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
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

tyekhan
Starting Member

29 Posts

Posted - 05/17/2013 :  13:18:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/17/2013 :  15:01:20  Show Profile  Reply with Quote
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 - 05/18/2013 :  05:26:24  Show Profile  Reply with Quote
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

547 Posts

Posted - 05/18/2013 :  09:09:34  Show Profile  Reply with Quote
Is this what you are looking for:


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;



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 - 05/18/2013 :  10:30:03  Show Profile  Reply with Quote
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:


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;



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

547 Posts

Posted - 05/18/2013 :  12:10:55  Show Profile  Reply with Quote
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:


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;



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

United Kingdom
171 Posts

Posted - 05/18/2013 :  14:16:20  Show Profile  Reply with Quote
Query runs fine here.
Go to Top of Page

tyekhan
Starting Member

29 Posts

Posted - 05/20/2013 :  02:57:12  Show Profile  Reply with Quote
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:


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;



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 - 05/20/2013 :  03:41:58  Show Profile  Reply with Quote
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.


Edited by - tyekhan on 05/20/2013 03:52:57
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 05/20/2013 :  06:31:34  Show Profile  Reply with Quote
--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 - 05/20/2013 :  06:49:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 05/20/2013 :  07:55:53  Show Profile  Reply with Quote
Can you provide expected output for the above sample data

--
Chandu
Go to Top of Page

tyekhan
Starting Member

29 Posts

Posted - 05/20/2013 :  08:31:22  Show Profile  Reply with Quote
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


Edited by - tyekhan on 05/20/2013 08:41:03
Go to Top of Page

tyekhan
Starting Member

29 Posts

Posted - 05/21/2013 :  06:54:13  Show Profile  Reply with Quote
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)

Edited by - tyekhan on 05/21/2013 09:19:14
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/02/2013 :  12:36:23  Show Profile  Reply with Quote
Try this:



;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)

Go to Top of Page

tyekhan
Starting Member

29 Posts

Posted - 06/02/2013 :  19:05:32  Show Profile  Reply with Quote

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:



;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)



Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/02/2013 :  19:46:40  Show Profile  Reply with Quote
May be you have an older version of SQL Server.
Try this:


;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) 



Or you can use this:


;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) 


Edited by - MuMu88 on 06/03/2013 00:25:08
Go to Top of Page

tyekhan
Starting Member

29 Posts

Posted - 06/03/2013 :  06:03:06  Show Profile  Reply with Quote
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:


;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) 



Or you can use this:


;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) 



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/03/2013 :  06:18:34  Show Profile  Reply with Quote
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 - 06/03/2013 :  06:57:35  Show Profile  Reply with Quote
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:


;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) 



Or you can use this:


;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) 



Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/03/2013 :  14:18:31  Show Profile  Reply with Quote
Show us your input data.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next 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.19 seconds. Powered By: Snitz Forums 2000