SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Next Date query
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/03/2013 :  23:40:11  Show Profile  Reply with Quote
quote:
Originally posted by tyekhan

The query works now but there is no information its all blank.


Just think for a second from out perspective
we dont know your system, neither have access to your tables. Then how do you think we can answer this.
So atleast post some sample data from tables and help us to understand what you're trying to get out of it

see below post for guidelines on how to post data

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

tyekhan
Starting Member

29 Posts

Posted - 06/04/2013 :  07:18:39  Show Profile  Reply with Quote
This is the table layout,

[dbo].[History](
[HistoryID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SystemID] [tinyint] NOT NULL,
[PID] [int] NOT NULL,
[Name] [varchar](35) NOT NULL,
[Source] [varchar](32) NOT NULL,
[SourceField] [varchar](32) NOT NULL,
[SourceID] [varchar](32) NOT NULL,
[CallDateTime] [datetime] NOT NULL,
[Num] [varchar](15) NOT NULL,
[CallData] [varchar](240) NOT NULL,
[ANI] [varchar](15) NOT NULL,
[RC] [varchar](5) NOT NULL,
[ID] [int] NOT NULL,

SELECT ID, Name, CallDateTime, Num, RC
FROM History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called')


Current out put

ID Name CallDateTime Num RC
3936803 Name1 01/05/2013 11:16:27 84 Called
5211387 Name2 01/05/2013 12:14:21 604 Called
5185689 Name3 01/05/2013 12:15:28 298 Called
4811923 Name4 01/05/2013 12:29:36 170 Called

Would like to see the below output

ID Name CallDateTime Num RC Next CallDateTime Total Number Of Days
3936803 Name1 01/05/2013 11:16 84 Called 04/05/2013 11:16 3
5211387 Name2 01/05/2013 12:14 604 Called 04/05/2013 12:14 3
5185689 Name3 01/05/2013 12:15 298 Called 04/05/2013 12:15 3
4811923 Name4 01/05/2013 12:29 170 Called 04/05/2013 12:29 3

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable


--===== Create the test table with
CREATE TABLE #mytable

(
[HistoryID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SystemID] [tinyint] NOT NULL,
[PID] [int] NOT NULL,
[Name] [varchar](35) NOT NULL,
[Source] [varchar](32) NOT NULL,
[SourceField] [varchar](32) NOT NULL,
[SourceID] [varchar](32) NOT NULL,
[CallDateTime] [datetime] NOT NULL,
[Num] [varchar](15) NOT NULL,
[CallData] [varchar](240) NOT NULL,
[ANI] [varchar](15) NOT NULL,
[RC] [varchar](5) NOT NULL,
[ID] [int] NOT NULL,

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table
INsert INTO #mytable
(ID, Name, CallDateTime, RC, Num)
SELECT '26241','Carr_CHS_20120227','Feb 27 2012 2:07PM','Called','01912' UNION ALL
SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443' UNION ALL
SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513' UNION ALL
SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519' UNION ALL
SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270' UNION ALL
SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938' UNION ALL
SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924' UNION ALL
SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875' UNION ALL
SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849' UNION ALL
SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476' UNION ALL
SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228' UNION ALL
SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048' UNION ALL
SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410' UNION ALL
SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379' UNION ALL
SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612' UNION ALL
SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227' UNION ALL
SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209' UNION ALL
SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240' UNION ALL
SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198' UNION ALL
SELECT '41393','Carr_CMM_20120227','Mar 05 2012 8:07PM','Done','01642198' UNION ALL
SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478'


--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF

I'm using Server 2008 Management Studio.

Let me know if you need anything else

Thanks

[quote]Originally posted by MuMu88

Show us your input data.

Edited by - tyekhan on 06/04/2013 08:37:31
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/04/2013 :  11:26:12  Show Profile  Reply with Quote
1. The table definition you provided above is incomplete and is incompatible with the data.
2. The query we have been working on has a filter to return the rows that satisfy the following condition:
CallDateTime > CONVERT(DATETIME, '2012-02-01 00:00:00', 102), none of the data you provided satisfy this condition, so your output will be blank.


Here is a modified working version of the information you provided along with a working query, you can modify the @FilterDate field to change the filtering criteria:

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable


--===== Create the test table with 
CREATE TABLE #mytable 

(
[HistoryID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
--[SystemID] [tinyint] NOT NULL,
--[PID] [int] NOT NULL,
[Name] [varchar](35) NOT NULL,
--[Source] [varchar](32) NOT NULL,
--[SourceField] [varchar](32) NOT NULL,
--[SourceID] [varchar](32) NOT NULL,
[CallDateTime] [datetime] NOT NULL,
[Num] [varchar](15) NOT NULL,
--[CallData] [varchar](240) NOT NULL,
--[ANI] [varchar](15) NOT NULL,
[RC] [varchar](8) NOT NULL,
[ID] [int] NOT NULL
);

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable OFF

--===== Insert the test data into the test table
INsert INTO #mytable 
(ID, Name, CallDateTime, RC, Num)
SELECT '26241','Carr_CHS_20120227','Feb 27 2012 2:07PM','Called','01912' UNION ALL
SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443' UNION ALL
SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513' UNION ALL
SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519' UNION ALL
SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270' UNION ALL
SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938' UNION ALL
SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924' UNION ALL
SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875' UNION ALL
SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849' UNION ALL
SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476' UNION ALL
SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228' UNION ALL
SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048' UNION ALL
SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410' UNION ALL
SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379' UNION ALL
SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612' UNION ALL
SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227' UNION ALL
SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209' UNION ALL
SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240' UNION ALL
SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198' UNION ALL
SELECT '41393','Carr_CMM_20120227','Mar 05 2012 8:07PM','Done','01642198' UNION ALL
SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478' 


--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF

DECLARE @FilterDate DATETIME = '2012-02-21 00:00:00' -- CHOOSE A DIFFERENT DATE HERE
;WITH CTE2 AS
(SELECT ID, Name, CallDateTime, Num, RC, 
	ROW_NUMBER() OVER(PARTITION BY Name Order by CallDateTime) as RowNum from #mytable
	WHERE (CallDateTime > CONVERT(DATETIME, @FilterDate, 102)) AND (RC = 'Called'))
SELECT T1.ID, T1.Name, T1.RC, T1.CallDateTime,  
	T2.CallDateTime as NextCallDate,
	DATEDIFF(DAY, T1.CallDateTime, T2.CallDateTime) as DaysPassed 
		  FROM CTE2 T1 LEFT JOIN CTE2 T2 ON T1.Name = T2.Name 
			 and (T1.RowNum+1 = T2.RowNum) 


--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable


Edited by - MuMu88 on 06/04/2013 11:28:30
Go to Top of Page

tyekhan
Starting Member

29 Posts

Posted - 06/05/2013 :  06:31:00  Show Profile  Reply with Quote
Thanks, but the below query is not pulling the nextcalldate,

I have added PID to the data below The PID is unique ID per customer so on the below i want to see
PID = 22

calldatetime 25/02/2012
NextCallDateTime = 28/02/2013
numberofdays = 3



--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..HistoryTEST','U') IS NOT NULL
DROP TABLE HistoryTEST

--===== Create the test table with 
CREATE TABLE HistoryTEST 
(
[HistoryTESTID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SystemID] [tinyint] NULL,
[PID] [int] NULL,
[Name] [varchar](35) NOT NULL,
[Source] [varchar](32)  NULL,
[SourceField] [varchar](32)  NULL,
[SourceID] [varchar](32)  NULL,
[CallDateTime] [datetime]  NULL,
[Num] [varchar](15)  NULL,
[CallData] [varchar](240)  NULL,
[ANI] [varchar](15)  NULL,
[RC] [varchar](10)  NULL,
[ID] [int]  NULL,
)
--===== Insert the test data into the test table
INSERT INTO HistoryTEST 
(ID, Name, CallDateTime, RC, Num, PID)
SELECT '26241','Carr_CHS_20120227','Feb 25 2012 3:07PM','Called','01912','22' UNION ALL
SELECT '28252','Carr_CHS_20120227','Feb 28 2012 4:07PM','PCO','01912','22' UNION ALL
SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443','23' UNION ALL
SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513','24' UNION ALL
SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519','25' UNION ALL
SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270','26' UNION ALL
SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938','27' UNION ALL
SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924','28' UNION ALL
SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875','29' UNION ALL
SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849','30' UNION ALL
SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476','31' UNION ALL
SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228','32' UNION ALL
SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048','33' UNION ALL
SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410','34' UNION ALL
SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379','35' UNION ALL
SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612','36' UNION ALL
SELECT '28265','Carr_CHS_20120227','Mar 2 2012 6:17PM','Other','01612' ,'36'UNION ALL
SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227','37' UNION ALL
SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209','38' UNION ALL
SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240','39' UNION ALL
SELECT '28654','Carr_CHS_20120227','Feb 28 2012 7:00PM','KKT','014240','40' UNION ALL
SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198','41' UNION ALL
SELECT '41345','Carr_CMM_20120227','Mar 4 2012 7:07PM','NIG','01642198' ,'41'UNION ALL
SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478','42' 

SELECT   HistoryTESTID, SystemID, PID, Name, Source, SourceField, SourceID, CallDateTime, Num, CallData, ANI, RC, ID
FROM      HistoryTEST
ORDER BY Name, CallDateTime





quote:
Originally posted by MuMu88

1. The table definition you provided above is incomplete and is incompatible with the data.
2. The query we have been working on has a filter to return the rows that satisfy the following condition:
CallDateTime > CONVERT(DATETIME, '2012-02-01 00:00:00', 102), none of the data you provided satisfy this condition, so your output will be blank.


Here is a modified working version of the information you provided along with a working query, you can modify the @FilterDate field to change the filtering criteria:

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable


--===== Create the test table with 
CREATE TABLE #mytable 

(
[HistoryID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
--[SystemID] [tinyint] NOT NULL,
--[PID] [int] NOT NULL,
[Name] [varchar](35) NOT NULL,
--[Source] [varchar](32) NOT NULL,
--[SourceField] [varchar](32) NOT NULL,
--[SourceID] [varchar](32) NOT NULL,
[CallDateTime] [datetime] NOT NULL,
[Num] [varchar](15) NOT NULL,
--[CallData] [varchar](240) NOT NULL,
--[ANI] [varchar](15) NOT NULL,
[RC] [varchar](8) NOT NULL,
[ID] [int] NOT NULL
);

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable OFF

--===== Insert the test data into the test table
INsert INTO #mytable 
(ID, Name, CallDateTime, RC, Num)
SELECT '26241','Carr_CHS_20120227','Feb 27 2012 2:07PM','Called','01912' UNION ALL
SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443' UNION ALL
SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513' UNION ALL
SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519' UNION ALL
SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270' UNION ALL
SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938' UNION ALL
SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924' UNION ALL
SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875' UNION ALL
SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849' UNION ALL
SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476' UNION ALL
SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228' UNION ALL
SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048' UNION ALL
SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410' UNION ALL
SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379' UNION ALL
SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612' UNION ALL
SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227' UNION ALL
SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209' UNION ALL
SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240' UNION ALL
SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198' UNION ALL
SELECT '41393','Carr_CMM_20120227','Mar 05 2012 8:07PM','Done','01642198' UNION ALL
SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478' 


--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF

DECLARE @FilterDate DATETIME = '2012-02-21 00:00:00' -- CHOOSE A DIFFERENT DATE HERE
;WITH CTE2 AS
(SELECT ID, Name, CallDateTime, Num, RC, 
	ROW_NUMBER() OVER(PARTITION BY Name Order by CallDateTime) as RowNum from #mytable
	WHERE (CallDateTime > CONVERT(DATETIME, @FilterDate, 102)) AND (RC = 'Called'))
SELECT T1.ID, T1.Name, T1.RC, T1.CallDateTime,  
	T2.CallDateTime as NextCallDate,
	DATEDIFF(DAY, T1.CallDateTime, T2.CallDateTime) as DaysPassed 
		  FROM CTE2 T1 LEFT JOIN CTE2 T2 ON T1.Name = T2.Name 
			 and (T1.RowNum+1 = T2.RowNum) 


--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable



Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/05/2013 :  11:27:58  Show Profile  Reply with Quote
Excellent, we are getting closer to the solution.
In the test data you provided, only one of the rows with PID = 22 satisfy the condition RC = 'Called', that is the reason why you are not seeing the second row in your output.


SELECT '26241','Carr_CHS_20120227','Feb 25 2012 3:07PM','Called','01912','22' UNION ALL
SELECT '28252','Carr_CHS_20120227','Feb 28 2012 4:07PM','PCO','01912','22' UNION ALL


If you want to include the data with RC = 'PCO' then use the following modified query:



DECLARE @FilterDate DATETIME = '2012-02-21 00:00:00' -- CHOOSE A DIFFERENT DATE HERE
;WITH CTE2 AS
(SELECT PID, ID, Name, CallDateTime, Num, RC, 
	ROW_NUMBER() OVER(PARTITION BY Name Order by CallDateTime) as RowNum from HistoryTEST
	WHERE (CallDateTime > CONVERT(DATETIME, @FilterDate, 102)) AND (RC = 'Called' OR RC = 'PCO'))
SELECT T1.PID, T1.ID, T1.Name, T1.RC, T1.CallDateTime,  
	T2.CallDateTime as NextCallDate,
	DATEDIFF(DAY, T1.CallDateTime, T2.CallDateTime) as DaysPassed 
		  FROM CTE2 T1 LEFT JOIN CTE2 T2 ON T1.Name = T2.Name 
			 and (T1.RowNum+1 = T2.RowNum) 


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 06/08/2013 :  03:46:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Which version of SQL Server are you using?


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

tyekhan
Starting Member

29 Posts

Posted - 06/08/2013 :  05:08:59  Show Profile  Reply with Quote
I'm using Server 2008 Management Studio.

quote:
Originally posted by SwePeso

Which version of SQL Server are you using?


N 56°04'39.26"
E 12°55'05.63"


Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000