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.
Author |
Topic |
bi89405
Starting Member
35 Posts |
Posted - 2013-04-30 : 23:14:28
|
Hello,I have the following data set.ID AppId AppName CreateDate77597 1 Unsubmitted 2012-05-14 00:31:14.13077597 2 Submitted 2012-05-14 00:30:35.58777597 3 InitialApproval 2012-05-24 00:01:46.19377597 4 ProductApproval 2012-05-24 00:17:40.55077597 5 FinalApproval 2012-06-20 05:39:38.113I am trying to create a fifth column that calculates the number of days from one row to the next. So, for this example, the fifth column should contain 0, 0, 10, 0, 27.Can someone assist me with this, please?Thanks in advance,Zack |
|
chadmat
The Chadinator
1974 Posts |
Posted - 2013-04-30 : 23:48:27
|
declare @t1 table (ID int, AppID Int, AppName varchar(20), CreateDate Datetime)insert into @t1 values (77597, 1, 'Unsubmitted', '5/14/2012')insert into @t1 values (77597, 2, 'Submitted', '5/14/2012')insert into @t1 values (77597, 3, 'InitialApproval', '5/24/2012')insert into @t1 values (77597, 4, 'ProductApproval', '5/24/2012')insert into @t1 values (77597, 5, 'Final Approval', '6/20/2012')SELECT a.*, ISNULL(DATEDIFF(dd, b.CreateDate, a.CreateDate),0) DaysBetweenFROM @t1 aLEFT JOIN @t1 b ON a.ID = b.ID AND a.AppID = b.AppID+1-Chad |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-01 : 00:17:03
|
Here is another solution different from Chad's. Chad's solution is efficient if you can guarantee consecutive AppIds.The following solution works if you don't have consecutive AppIds or any other columns with consecutive numbers:[CODE]Select A.ID, A.AppId, A.AppName, datediff(dd, B.CreateDate, A.CreateDate)AS DaysBetween from (SELECT ID, AppId, AppName, CreateDate, (ROW_NUMBER() OVER(Order by CreateDate) -1) as R1 from YourTableName) as A LEFT JOIN (SELECT ID, AppId, AppName, CreateDate, ROW_NUMBER() OVER(Order by CreateDate) as R2 from YourTableName) as B ON R1 = R2;[/CODE] |
|
|
bi89405
Starting Member
35 Posts |
Posted - 2013-05-01 : 09:42:12
|
Hello,Thank you for both of your responses. Now, I'd like to throw a monkey wrench into the mix.Suppose I have multiple IDs in the data set and I want the process to repeat for each new ID. In other words, I have ID# 12345 where you count the number of days between each AppID. Then, you have ID# 67890 and you need to do the same process for this group until all the IDs are complete.Thanks in advance!Zack |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
bi89405
Starting Member
35 Posts |
Posted - 2013-05-01 : 12:14:30
|
Here is the data set:77597 1 Unsubmitted 2012-05-14 00:31:14.13077597 2 Submitted 2012-05-14 00:30:35.58777597 3 InitialApproval 2012-05-24 00:01:46.19377597 4 ProductApproval 2012-05-24 00:17:40.55077597 5 FinalApproval 2012-06-20 05:39:38.11312345 1 Unsubmitted 2012-07-14 00:31:14.13012345 2 Submitted 2012-07-21 00:30:35.58712345 3 InitialApproval 2012-07-24 00:01:46.19312345 4 ProductApproval 2012-07-24 00:17:40.55012345 5 FinalApproval 2012-07-30 05:39:38.113The fifth column should read:010027073060Goal: counting the number of days from one row to the next but resetting on start of new ID.Thanks,Zack |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-05-01 : 13:02:10
|
Please put yout data in a consumable format so we can run queries against it. See chadmat's post from 04/30/2013 23:48:27 to see how to do it. |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-01 : 13:28:38
|
If I understood your request correctly, you should be able to get what you need by modifying the original query as shown below:[CODE]Select A.ID, A.AppId, A.AppName, datediff(dd, B.CreateDate, A.CreateDate)AS DaysBetween from (SELECT ID, AppId, AppName, CreateDate, (ROW_NUMBER() OVER(PARTITION BY ID Order by CreateDate) -1) as R1 from YourTableName) as A LEFT JOIN (SELECT ID, AppId, AppName, CreateDate, ROW_NUMBER() OVER(PARTITION BY ID Order by CreateDate) as R2 from YourTableName) as B ON R1 = R2 and A.ID = B.ID;[/CODE] |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2013-05-01 : 13:37:16
|
I believe mine will work with no change, just add the data into the table, and the query should return the expected output.-Chad |
|
|
bi89405
Starting Member
35 Posts |
Posted - 2013-05-01 : 14:13:35
|
You guys rock! Both of your responses gave the same results. However, after reviewing the output, I realized that there is one flaw (apologies for not defining this better earlier).The data for the DaysBetween column should be read as follows:010027073060This should be interpreted as, for ID 77597, the item sat in Unsubmitted stage for 0 days. It sat in Submitted stage for 10 days. It sat in InitialApproval stage for 0 days etc.It seems you guys are booking the value on the next row as such:001002707306How can this be fixed?Hope this makes sense.Thanks again to both of you!Zack |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-01 : 14:28:48
|
Try this:[CODE]Select A.ID, A.AppId, A.AppName, datediff(dd, A.CreateDate, B.CreateDate)AS DaysBetween from (SELECT ID, AppId, AppName, CreateDate, (ROW_NUMBER() OVER(PARTITION BY ID Order by CreateDate) + 1) as R1 from YourTableName) as A LEFT JOIN (SELECT ID, AppId, AppName, CreateDate, ROW_NUMBER() OVER(PARTITION BY ID Order by CreateDate) as R2 from YourTableName) as B ON R1 = R2 and A.ID = B.ID;[/CODE] |
|
|
bi89405
Starting Member
35 Posts |
Posted - 2013-05-01 : 14:31:20
|
MuMu88, you are a superstar! Thank you. It worked like a charm! |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-01 : 14:39:49
|
Or here is modified Chad's query:[CODE]SELECT a.*, ISNULL(DATEDIFF(dd, a.CreateDate, b.CreateDate),0) DaysBetweenFROM @t1 aLEFT JOIN @t1 b ON a.ID = b.ID AND a.AppID = b.AppID-1[/CODE] |
|
|
bi89405
Starting Member
35 Posts |
Posted - 2013-05-01 : 15:15:58
|
Works just as well. Thanks agani MuMu88. |
|
|
bi89405
Starting Member
35 Posts |
Posted - 2013-05-01 : 16:04:24
|
Hello again,I came across a scenario where the AppID may not necessarily be sequential. I noticed that for these instances, the calculation to add the next day is not correct. Any idea how we can resolve this bug?77597 1 Unsubmitted 2012-05-14 00:31:14.13077597 2 Submitted 2012-05-14 00:30:35.58777597 4 InitialApproval 2012-05-24 00:01:46.19377597 6 ProductApproval 2012-05-24 00:17:40.55077597 8 FinalApproval 2012-06-20 05:39:38.11312345 1 Unsubmitted 2012-07-14 00:31:14.13012345 2 Submitted 2012-07-21 00:30:35.58712345 4 InitialApproval 2012-07-24 00:01:46.19312345 6 ProductApproval 2012-07-24 00:17:40.55012345 9 FinalApproval 2012-07-30 05:39:38.113Thanks in advance!Zack |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-01 : 18:55:13
|
quote: Originally posted by bi89405 Hello again,I came across a scenario where the AppID may not necessarily be sequential. I noticed that for these instances, the calculation to add the next day is not correct. Any idea how we can resolve this bug?77597 1 Unsubmitted 2012-05-14 00:31:14.13077597 2 Submitted 2012-05-14 00:30:35.58777597 4 InitialApproval 2012-05-24 00:01:46.19377597 6 ProductApproval 2012-05-24 00:17:40.55077597 8 FinalApproval 2012-06-20 05:39:38.11312345 1 Unsubmitted 2012-07-14 00:31:14.13012345 2 Submitted 2012-07-21 00:30:35.58712345 4 InitialApproval 2012-07-24 00:01:46.19312345 6 ProductApproval 2012-07-24 00:17:40.55012345 9 FinalApproval 2012-07-30 05:39:38.113Thanks in advance!Zack
the following query should work:[CODE]Select A.ID, A.AppId, A.AppName, ISNULL(datediff(dd, A.CreateDate, B.CreateDate), 0) AS DaysBetween from (SELECT ID, AppId, AppName, CreateDate, (ROW_NUMBER() OVER(PARTITION BY ID Order by CreateDate) + 1) as R1 from YourTableName) as A LEFT JOIN (SELECT ID, AppId, AppName, CreateDate, ROW_NUMBER() OVER(PARTITION BY ID Order by CreateDate) as R2 from YourTableName) as B ON R1 = R2 and A.ID = B.ID;[/CODE] |
|
|
bi89405
Starting Member
35 Posts |
Posted - 2013-05-02 : 09:09:24
|
Great, thank you again, MuMu88! |
|
|
|
|
|
|
|