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, RCFROM HistoryWHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called')Showing the below,ID Name CallDateTime Num RC3936803 Name1 01/05/2013 11:16:27 84 Called5211387 Name2 01/05/2013 12:14:21 604 Called5185689 Name3 01/05/2013 12:15:28 298 Called4811923 Name4 01/05/2013 12:29:36 170 CalledThis 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 Days3936803 Name1 01/05/2013 11:16 84 Called 04/05/2013 11:16 35211387 Name2 01/05/2013 12:14 604 Called 04/05/2013 12:14 35185689 Name3 01/05/2013 12:15 298 Called 04/05/2013 12:15 34811923 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 @DataInAConsumableFormatVALUES(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 TotalNumberOfDaysFROM ( SELECT *, LEAD(CallDateTime, 1, 0) OVER (ORDER BY CallDateTime) AS NextCallDateTime FROM @DataInAConsumableFormat ) AS Temp |
|
|
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, RCFROM HistoryWHERE (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 @DataInAConsumableFormatVALUES(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 TotalNumberOfDaysFROM ( SELECT *, LEAD(CallDateTime, 1, 0) OVER (ORDER BY CallDateTime) AS NextCallDateTime FROM @DataInAConsumableFormat ) AS Temp
|
|
|
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 @HistoryVALUES(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 |
|
|
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 14Column '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 @HistoryVALUES(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
|
|
|
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 14Column '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 @HistoryVALUES(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
|
|
|
Rasta Pickles
Posting Yak Master
174 Posts |
Posted - 2013-05-18 : 14:16:20
|
Query runs fine here. |
|
|
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 14Column '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 @HistoryVALUES(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
|
|
|
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/2013with 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. |
|
|
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 @HistoryVALUES (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) NumDaysFROM @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 ) H2ON H1.ID = H2.ID AND H1.Name = H2.Name WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called') --Chandu |
|
|
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 @HistoryVALUES (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) NumDaysFROM @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 ) H2ON H1.ID = H2.ID AND H1.Name = H2.Name WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called') --Chandu
|
|
|
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 |
|
|
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 DaysSELECT ID, Name, CallDateTime, Num, RCFROM HistoryWHERE (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 DaysID Name CallDateTime Num RC Next CallDateTime Total Number Of DaysFor 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- Called05/05/2013 Outcome – other10/05/2013 Outcome – thinking20/05/2013 Outcome – maybeEct… So the CallDateTime would be 05/05/2013ID, Name, CallDateTime, Num, RC, Next CallDateTime, TotalNOOfDays 1, Name1, 01/05/2013 11:16, 84, CALLED, 05/05/2013 11:16, 3The above is what i would like.quote: Originally posted by bandi Can you provide expected output for the above sample data--Chandu
|
|
|
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) |
|
|
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] |
|
|
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 2Incorrect 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]
|
|
|
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] |
|
|
tyekhan
Starting Member
29 Posts |
Posted - 2013-06-03 : 06:03:06
|
Thanks for the code i'm getting the below error1-Msg 1087, Level 15, State 2, Line 2Must declare the table variable "@History".Msg 1087, Level 15, State 2, Line 8Must declare the table variable "@History".2-Msg 1087, Level 15, State 2, Line 3Must 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]
|
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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]
|
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-06-03 : 14:18:31
|
Show us your input data. |
|
|
Previous Page&nsp;
Next Page
|
|
|