Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Next Date query

Author  Topic 

tyekhan
Starting Member

29 Posts
Posted - 2013-05-17 : 13:18:25
I need help with the below query,

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

Showing the below,
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


This is the query I have at the moment BUT not show how to add two more columns to it they are next called in date & time after the first called in. I want it to show the next time & date does not matter what the RC code is next visit.

So I want mine output to show the below, adding the next called time & date and how many days it take from the first date to the next date,

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2013-05-17 : 15:01:20
How do you determine the "next call date?"

I don't get the logic, so I can't write a query to do that, but maybe this will help foster some ideas on what to do??

DECLARE @DataInAConsumableFormat TABLE (ID INT, Name VARCHAR(10), CallDateTime DATETIME, Num INT, RC VARCHAR(10))


INSERT  @DataInAConsumableFormat
VALUES
(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')


SELECT 
	*,
	DATEDIFF(DAY, NextCallDateTime, CallDateTime) aS TotalNumberOfDays
FROM
	(
		SELECT 
			*,
			LEAD(CallDateTime, 1, 0) OVER (ORDER BY CallDateTime) AS NextCallDateTime
		FROM 
			@DataInAConsumableFormat
	) AS Temp
Go to Top of Page

tyekhan
Starting Member

29 Posts
Posted - 2013-05-18 : 05:26:24
Sorry to make it clear what I need is to be able to find out when a customer next called in from the date I have select with the next date & time on any Purchase, does don't need to be same. It’s like using MIN & MAX but that does not work on this query as it select the same date & time for both.

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

As you can see in the query above that all the data is in one overall table called History, this records all the purchases.

So I want to know that if a customer after the 1/05/2013 called in with the outcome of called what was he next purchases date, some customer might not have come so that can stay blank.



quote:
Originally posted by Lamprey

How do you determine the "next call date?"

I don't get the logic, so I can't write a query to do that, but maybe this will help foster some ideas on what to do??

DECLARE @DataInAConsumableFormat TABLE (ID INT, Name VARCHAR(10), CallDateTime DATETIME, Num INT, RC VARCHAR(10))


INSERT  @DataInAConsumableFormat
VALUES
(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')


SELECT 
	*,
	DATEDIFF(DAY, NextCallDateTime, CallDateTime) aS TotalNumberOfDays
FROM
	(
		SELECT 
			*,
			LEAD(CallDateTime, 1, 0) OVER (ORDER BY CallDateTime) AS NextCallDateTime
		FROM 
			@DataInAConsumableFormat
	) AS Temp
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts
Posted - 2013-05-18 : 09:09:34
Is this what you are looking for:
[CODE]

DECLARE @History TABLE (ID INT, Name VARCHAR(10), CallDateTime DATETIME, RC INT, Outcome VARCHAR(10))


INSERT @History
VALUES
(3936801, 'Name1', '01/05/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '01/05/2013 12:14:21', 604, 'Called'),
(3936803, 'Name3', '01/05/2013 12:15:28', 298, 'Called'),
(3936804, 'Name4', '01/05/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/05/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/05/2013 12:14:21', 604, 'Called'),
(3936803, 'Name3', '05/05/2013 12:15:28', 298, 'Called'),
(3936804, 'Name4', '05/05/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/06/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/08/2013 12:14:21', 604, 'Called'),
(3936804, 'Name4', '05/15/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/15/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/25/2013 12:14:21', 604, 'Called'),
(3936804, 'Name4', '05/29/2013 12:29:36', 170, 'Called');


SELECT DISTINCT ID, Name, MIN(CALLDateTIme) CallDateTime,
(CASE WHEN MAX(CALLDateTIme) <> MIN(CALLDateTIme) THEN MAX(CALLDateTIme) ELSE NULL END) AS NextCallDateTime,
(CASE WHEN MAX(CALLDateTIme) <> MIN(CALLDateTIme) THEN DATEDIFF(DAY, MIN(CALLDateTIme), MAX(CALLDateTIme)) ELSE NULL END) AS NumDays
FROM
(SELECT * FROM
(SELECT ID, Name, CallDateTime, outcome, RC, ROW_NUMBER() OVER(PARTITION BY Name Order by CallDateTime) as RowNum from @History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called')) B WHERE RowNum <= 2) A GROUP BY NAME, ID;


[/CODE]
Consider providing your DDLs, input and output data in a consumable format if you need more help as described here:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

tyekhan
Starting Member

29 Posts
Posted - 2013-05-18 : 10:30:03
i'm trying the below query but i'm getting an error message,

'Msg 8120, Level 16, State 1, Line 14
Column 'A.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'


quote:
Originally posted by MuMu88

Is this what you are looking for:
[CODE]

DECLARE @History TABLE (ID INT, Name VARCHAR(10), CallDateTime DATETIME, RC INT, Outcome VARCHAR(10))


INSERT @History
VALUES
(3936801, 'Name1', '01/05/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '01/05/2013 12:14:21', 604, 'Called'),
(3936803, 'Name3', '01/05/2013 12:15:28', 298, 'Called'),
(3936804, 'Name4', '01/05/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/05/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/05/2013 12:14:21', 604, 'Called'),
(3936803, 'Name3', '05/05/2013 12:15:28', 298, 'Called'),
(3936804, 'Name4', '05/05/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/06/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/08/2013 12:14:21', 604, 'Called'),
(3936804, 'Name4', '05/15/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/15/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/25/2013 12:14:21', 604, 'Called'),
(3936804, 'Name4', '05/29/2013 12:29:36', 170, 'Called');


SELECT DISTINCT ID, Name, MIN(CALLDateTIme) CallDateTime,
(CASE WHEN MAX(CALLDateTIme) <> MIN(CALLDateTIme) THEN MAX(CALLDateTIme) ELSE NULL END) AS NextCallDateTime,
(CASE WHEN MAX(CALLDateTIme) <> MIN(CALLDateTIme) THEN DATEDIFF(DAY, MIN(CALLDateTIme), MAX(CALLDateTIme)) ELSE NULL END) AS NumDays
FROM
(SELECT * FROM
(SELECT ID, Name, CallDateTime, outcome, RC, ROW_NUMBER() OVER(PARTITION BY Name Order by CallDateTime) as RowNum from @History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called')) B WHERE RowNum <= 2) A GROUP BY NAME, ID;


[/CODE]
Consider providing your DDLs, input and output data in a consumable format if you need more help as described here:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts
Posted - 2013-05-18 : 12:10:55
Did you include GROUP by clause highlighted in red below?

quote:
Originally posted by tyekhan

i'm trying the below query but i'm getting an error message,

'Msg 8120, Level 16, State 1, Line 14
Column 'A.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'


quote:
Originally posted by MuMu88

Is this what you are looking for:
[CODE]

DECLARE @History TABLE (ID INT, Name VARCHAR(10), CallDateTime DATETIME, RC INT, Outcome VARCHAR(10))


INSERT @History
VALUES
(3936801, 'Name1', '01/05/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '01/05/2013 12:14:21', 604, 'Called'),
(3936803, 'Name3', '01/05/2013 12:15:28', 298, 'Called'),
(3936804, 'Name4', '01/05/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/05/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/05/2013 12:14:21', 604, 'Called'),
(3936803, 'Name3', '05/05/2013 12:15:28', 298, 'Called'),
(3936804, 'Name4', '05/05/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/06/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/08/2013 12:14:21', 604, 'Called'),
(3936804, 'Name4', '05/15/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/15/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/25/2013 12:14:21', 604, 'Called'),
(3936804, 'Name4', '05/29/2013 12:29:36', 170, 'Called');


SELECT DISTINCT ID, Name, MIN(CALLDateTIme) CallDateTime,
(CASE WHEN MAX(CALLDateTIme) <> MIN(CALLDateTIme) THEN MAX(CALLDateTIme) ELSE NULL END) AS NextCallDateTime,
(CASE WHEN MAX(CALLDateTIme) <> MIN(CALLDateTIme) THEN DATEDIFF(DAY, MIN(CALLDateTIme), MAX(CALLDateTIme)) ELSE NULL END) AS NumDays
FROM
(SELECT * FROM
(SELECT ID, Name, CallDateTime, outcome, RC, ROW_NUMBER() OVER(PARTITION BY Name Order by CallDateTime) as RowNum from @History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called')) B WHERE RowNum <= 2) A GROUP BY NAME, ID;


[/CODE]
Consider providing your DDLs, input and output data in a consumable format if you need more help as described here:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Rasta Pickles
Posting Yak Master

174 Posts
Posted - 2013-05-18 : 14:16:20
Query runs fine here.
Go to Top of Page

tyekhan
Starting Member

29 Posts
Posted - 2013-05-20 : 02:57:12
Yes i did group by the red below.

quote:
Originally posted by tyekhan

i'm trying the below query but i'm getting an error message,

'Msg 8120, Level 16, State 1, Line 14
Column 'A.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'


quote:
Originally posted by MuMu88

Is this what you are looking for:
[CODE]

DECLARE @History TABLE (ID INT, Name VARCHAR(10), CallDateTime DATETIME, RC INT, Outcome VARCHAR(10))


INSERT @History
VALUES
(3936801, 'Name1', '01/05/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '01/05/2013 12:14:21', 604, 'Called'),
(3936803, 'Name3', '01/05/2013 12:15:28', 298, 'Called'),
(3936804, 'Name4', '01/05/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/05/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/05/2013 12:14:21', 604, 'Called'),
(3936803, 'Name3', '05/05/2013 12:15:28', 298, 'Called'),
(3936804, 'Name4', '05/05/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/06/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/08/2013 12:14:21', 604, 'Called'),
(3936804, 'Name4', '05/15/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/15/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/25/2013 12:14:21', 604, 'Called'),
(3936804, 'Name4', '05/29/2013 12:29:36', 170, 'Called');


SELECT DISTINCT ID, Name, MIN(CALLDateTIme) CallDateTime,
(CASE WHEN MAX(CALLDateTIme) <> MIN(CALLDateTIme) THEN MAX(CALLDateTIme) ELSE NULL END) AS NextCallDateTime,
(CASE WHEN MAX(CALLDateTIme) <> MIN(CALLDateTIme) THEN DATEDIFF(DAY, MIN(CALLDateTIme), MAX(CALLDateTIme)) ELSE NULL END) AS NumDays
FROM
(SELECT * FROM
(SELECT ID, Name, CallDateTime, outcome, RC, ROW_NUMBER() OVER(PARTITION BY Name Order by CallDateTime) as RowNum from @History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called')) B WHERE RowNum <= 2) A GROUP BY NAME, ID;


[/CODE]
Consider providing your DDLs, input and output data in a consumable format if you need more help as described here:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

tyekhan
Starting Member

29 Posts
Posted - 2013-05-20 : 03:41:58
got the query working BUT the nextcalldatetime fields are all null, also I need this query not only to show the records that are in the value section part,but all the records that are after specific date&date with an outcome that is selected on the query,

so if i have select the CallDateTime

After 01/05/2013
with all the outcomes in the query with the cr is called in the field.

i want it to return all the values for called made after 01/05/2013.

Then if the first CallDateTime was made on 01/05/2013 then the 2nd 05/05/2013 also 15/05/2013 etc.., i only want to next calldatetime to be 05/05/2013.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2013-05-20 : 06:31:34
--May be this?

DECLARE @History TABLE (ID INT, Name VARCHAR(10), CallDateTime DATETIME, RC INT, Outcome VARCHAR(10))
INSERT  @History
VALUES (3936801, 'Name1', '01/05/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '01/05/2013 12:14:21', 604, 'Called'),
(3936803, 'Name3', '01/05/2013 12:15:28', 298, 'Called'),
(3936804, 'Name4', '01/05/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/05/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/05/2013 12:14:21', 604, 'Called'),
(3936803, 'Name3', '05/05/2013 12:15:28', 298, 'Called'),
(3936804, 'Name4', '05/05/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/06/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/08/2013 12:14:21', 604, 'Called'),
(3936804, 'Name4', '05/15/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/15/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/25/2013 12:14:21', 604, 'Called'),
(3936804, 'Name4', '05/29/2013 12:29:36', 170, 'Called');

SELECT H1.ID, H1.Name, CallDateTime, NextCallDateTime, DATEDIFF( DD, NextCallDateTime, CallDateTime) NumDays
FROM @History h1 
JOIN (SELECT ID, Name, MIN(CallDateTime) NextCallDateTime
		FROM @History WHERE (CallDateTime >  CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called')
		GROUP BY ID, Name ) H2
ON H1.ID = H2.ID AND H1.Name = H2.Name 
WHERE (CallDateTime >  CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called')


--
Chandu
Go to Top of Page

tyekhan
Starting Member

29 Posts
Posted - 2013-05-20 : 06:49:37
the nextCallDateTime is the same as the first CallDateTime field, Also can I do this query without have the values as this only select the value figures for the query not the overall records for the date select for that I have over 2000 records.


quote:
Originally posted by bandi

--May be this?

DECLARE @History TABLE (ID INT, Name VARCHAR(10), CallDateTime DATETIME, RC INT, Outcome VARCHAR(10))
INSERT  @History
VALUES (3936801, 'Name1', '01/05/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '01/05/2013 12:14:21', 604, 'Called'),
(3936803, 'Name3', '01/05/2013 12:15:28', 298, 'Called'),
(3936804, 'Name4', '01/05/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/05/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/05/2013 12:14:21', 604, 'Called'),
(3936803, 'Name3', '05/05/2013 12:15:28', 298, 'Called'),
(3936804, 'Name4', '05/05/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/06/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/08/2013 12:14:21', 604, 'Called'),
(3936804, 'Name4', '05/15/2013 12:29:36', 170, 'Called'),
(3936801, 'Name1', '05/15/2013 11:16:27', 84 , 'Called'),
(3936802, 'Name2', '05/25/2013 12:14:21', 604, 'Called'),
(3936804, 'Name4', '05/29/2013 12:29:36', 170, 'Called');

SELECT H1.ID, H1.Name, CallDateTime, NextCallDateTime, DATEDIFF( DD, NextCallDateTime, CallDateTime) NumDays
FROM @History h1 
JOIN (SELECT ID, Name, MIN(CallDateTime) NextCallDateTime
		FROM @History WHERE (CallDateTime >  CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called')
		GROUP BY ID, Name ) H2
ON H1.ID = H2.ID AND H1.Name = H2.Name 
WHERE (CallDateTime >  CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called')


--
Chandu
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2013-05-20 : 07:55:53
Can you provide expected output for the above sample data

--
Chandu
Go to Top of Page

tyekhan
Starting Member

29 Posts
Posted - 2013-05-20 : 08:31:22
What I would like is to be able to select CallDateTime with the outcome as selected as below query will get the information I need but not the nextcalldatetime & Total Number Of Days

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

So what I would like is having the query above with the nextcalldatetime & Total Number Of Days

ID Name CallDateTime Num RC Next CallDateTime Total Number Of Days


For example, a record that has CallDateTime after 01/05/2013 and has an Outcome as Called, would come into the select query then I would like the next CallDateTime for the same records ONLY the next one,

ID- 1 the below CallDateTime,

01/05/2013, Outcome- Called
05/05/2013 Outcome – other
10/05/2013 Outcome – thinking
20/05/2013 Outcome – maybe

Ect…

So the CallDateTime would be 05/05/2013

ID, Name, CallDateTime, Num, RC, Next CallDateTime, TotalNOOfDays
1, Name1, 01/05/2013 11:16, 84, CALLED, 05/05/2013 11:16, 3

The above is what i would like.


quote:
Originally posted by bandi

Can you provide expected output for the above sample data

--
Chandu
Go to Top of Page

tyekhan
Starting Member

29 Posts
Posted - 2013-05-21 : 06:54:13
If i have two different rows for NAME1, one with calldatetime first of may and the second fifth of may, i want to show both in the result. If more rows are available i want to show always the first date and the following (Next date)
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts
Posted - 2013-06-02 : 12:36:23
Try this:

[CODE]

;WITH CTE AS
(SELECT ID, Name, CallDateTime, outcome, RC, COUNT(1) OVER(PARTITION BY Name Order by CallDateTime ROWS
BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) as CountNum,
ROW_NUMBER() OVER(PARTITION BY Name Order by CallDateTime) as RowNum from @History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called'))
SELECT T1.ID, T1.Name, T1.RC, T1.CallDateTime,
(CASE WHEN T1.CountNum =1 THEN NULL ELSE T2.CallDateTime END) as NextCallDate,
DATEDIFF(DAY, T1.CallDateTime, T2.CallDateTime) as DaysPassed
FROM CTE T1 OUTER APPLY CTE T2 WHERE T1.Name = T2.Name
and (T1.RowNum = T2.RowNum-1 or T1.CountNum = 1)

[/CODE]
Go to Top of Page

tyekhan
Starting Member

29 Posts
Posted - 2013-06-02 : 19:05:32

im getting the below error, with the below query,

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'ROWS'.

quote:
Originally posted by MuMu88

Try this:

[CODE]

;WITH CTE AS
(SELECT ID, Name, CallDateTime, outcome, RC, COUNT(1) OVER(PARTITION BY Name Order by CallDateTime ROWS
BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) as CountNum,
ROW_NUMBER() OVER(PARTITION BY Name Order by CallDateTime) as RowNum from @History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called'))
SELECT T1.ID, T1.Name, T1.RC, T1.CallDateTime,
(CASE WHEN T1.CountNum =1 THEN NULL ELSE T2.CallDateTime END) as NextCallDate,
DATEDIFF(DAY, T1.CallDateTime, T2.CallDateTime) as DaysPassed
FROM CTE T1 OUTER APPLY CTE T2 WHERE T1.Name = T2.Name
and (T1.RowNum = T2.RowNum-1 or T1.CountNum = 1)

[/CODE]
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts
Posted - 2013-06-02 : 19:46:40
May be you have an older version of SQL Server.
Try this:
[CODE]

;WITH CTE1 AS
(SELECT COUNT(1) as CountNum, Name, ID from @History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102))
AND (Outcome = 'Called')
GROUP by Name, ID),
CTE2 AS
(SELECT ID, Name, CallDateTime, outcome, RC,
ROW_NUMBER() OVER(PARTITION BY Name Order by CallDateTime) as RowNum from @History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called'))
SELECT T1.ID, T1.Name, T1.RC, T1.CallDateTime,
(CASE WHEN T3.CountNum =1 THEN NULL ELSE T2.CallDateTime END) as NextCallDate,
DATEDIFF(DAY, T1.CallDateTime, T2.CallDateTime) as DaysPassed
FROM CTE2 T1 OUTER APPLY CTE2 T2 OUTER APPLY CTE1 T3 WHERE T1.Name = T2.Name and
T1.Name = T3.Name and (T1.RowNum = T2.RowNum-1 OR T3.CountNum = 1)

[/CODE]

Or you can use this:

[CODE]
;WITH CTE2 AS
(SELECT ID, Name, CallDateTime, outcome, RC,
ROW_NUMBER() OVER(PARTITION BY Name Order by CallDateTime) as RowNum from @History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = '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)

[/CODE]
Go to Top of Page

tyekhan
Starting Member

29 Posts
Posted - 2013-06-03 : 06:03:06
Thanks for the code i'm getting the below error

1-
Msg 1087, Level 15, State 2, Line 2
Must declare the table variable "@History".
Msg 1087, Level 15, State 2, Line 8
Must declare the table variable "@History".

2-
Msg 1087, Level 15, State 2, Line 3
Must declare the table variable "@History".


quote:
Originally posted by MuMu88

May be you have an older version of SQL Server.
Try this:
[CODE]

;WITH CTE1 AS
(SELECT COUNT(1) as CountNum, Name, ID from @History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102))
AND (Outcome = 'Called')
GROUP by Name, ID),
CTE2 AS
(SELECT ID, Name, CallDateTime, outcome, RC,
ROW_NUMBER() OVER(PARTITION BY Name Order by CallDateTime) as RowNum from @History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called'))
SELECT T1.ID, T1.Name, T1.RC, T1.CallDateTime,
(CASE WHEN T3.CountNum =1 THEN NULL ELSE T2.CallDateTime END) as NextCallDate,
DATEDIFF(DAY, T1.CallDateTime, T2.CallDateTime) as DaysPassed
FROM CTE2 T1 OUTER APPLY CTE2 T2 OUTER APPLY CTE1 T3 WHERE T1.Name = T2.Name and
T1.Name = T3.Name and (T1.RowNum = T2.RowNum-1 OR T3.CountNum = 1)

[/CODE]

Or you can use this:

[CODE]
;WITH CTE2 AS
(SELECT ID, Name, CallDateTime, outcome, RC,
ROW_NUMBER() OVER(PARTITION BY Name Order by CallDateTime) as RowNum from @History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = '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)

[/CODE]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-06-03 : 06:18:34
You need to have declare part also in above query

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

tyekhan
Starting Member

29 Posts
Posted - 2013-06-03 : 06:57:35
The query works now but there is no information its all blank.

quote:
Originally posted by MuMu88

May be you have an older version of SQL Server.
Try this:
[CODE]

;WITH CTE1 AS
(SELECT COUNT(1) as CountNum, Name, ID from @History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102))
AND (Outcome = 'Called')
GROUP by Name, ID),
CTE2 AS
(SELECT ID, Name, CallDateTime, outcome, RC,
ROW_NUMBER() OVER(PARTITION BY Name Order by CallDateTime) as RowNum from @History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called'))
SELECT T1.ID, T1.Name, T1.RC, T1.CallDateTime,
(CASE WHEN T3.CountNum =1 THEN NULL ELSE T2.CallDateTime END) as NextCallDate,
DATEDIFF(DAY, T1.CallDateTime, T2.CallDateTime) as DaysPassed
FROM CTE2 T1 OUTER APPLY CTE2 T2 OUTER APPLY CTE1 T3 WHERE T1.Name = T2.Name and
T1.Name = T3.Name and (T1.RowNum = T2.RowNum-1 OR T3.CountNum = 1)

[/CODE]

Or you can use this:

[CODE]
;WITH CTE2 AS
(SELECT ID, Name, CallDateTime, outcome, RC,
ROW_NUMBER() OVER(PARTITION BY Name Order by CallDateTime) as RowNum from @History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = '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)

[/CODE]
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts
Posted - 2013-06-03 : 14:18:31
Show us your input data.
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site