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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Calculating days from one record to next

Author  Topic 

bi89405
Starting Member

35 Posts

Posted - 2013-04-30 : 23:14:28
Hello,

I have the following data set.

ID AppId AppName CreateDate
77597 1 Unsubmitted 2012-05-14 00:31:14.130
77597 2 Submitted 2012-05-14 00:30:35.587
77597 3 InitialApproval 2012-05-24 00:01:46.193
77597 4 ProductApproval 2012-05-24 00:17:40.550
77597 5 FinalApproval 2012-06-20 05:39:38.113

I 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) DaysBetween
FROM @t1 a
LEFT JOIN @t1 b ON a.ID = b.ID AND a.AppID = b.AppID+1

-Chad
Go to Top of Page

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]
Go to Top of Page

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
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-01 : 11:24:25
Sample data expected output?

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

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.130
77597 2 Submitted 2012-05-14 00:30:35.587
77597 3 InitialApproval 2012-05-24 00:01:46.193
77597 4 ProductApproval 2012-05-24 00:17:40.550
77597 5 FinalApproval 2012-06-20 05:39:38.113
12345 1 Unsubmitted 2012-07-14 00:31:14.130
12345 2 Submitted 2012-07-21 00:30:35.587
12345 3 InitialApproval 2012-07-24 00:01:46.193
12345 4 ProductApproval 2012-07-24 00:17:40.550
12345 5 FinalApproval 2012-07-30 05:39:38.113

The fifth column should read:

0
10
0
27
0
7
3
0
6
0

Goal: counting the number of days from one row to the next but resetting on start of new ID.

Thanks,
Zack

Go to Top of Page

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.
Go to Top of Page

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]
Go to Top of Page

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
Go to Top of Page

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:

0
10
0
27
0
7
3
0
6
0

This 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:

0
0
10
0
27
0
7
3
0
6

How can this be fixed?

Hope this makes sense.

Thanks again to both of you!
Zack
Go to Top of Page

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]
Go to Top of Page

bi89405
Starting Member

35 Posts

Posted - 2013-05-01 : 14:31:20
MuMu88, you are a superstar! Thank you. It worked like a charm!

Go to Top of Page

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) DaysBetween
FROM @t1 a
LEFT JOIN @t1 b ON a.ID = b.ID AND a.AppID = b.AppID-1

[/CODE]
Go to Top of Page

bi89405
Starting Member

35 Posts

Posted - 2013-05-01 : 15:15:58
Works just as well. Thanks agani MuMu88.
Go to Top of Page

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.130
77597 2 Submitted 2012-05-14 00:30:35.587
77597 4 InitialApproval 2012-05-24 00:01:46.193
77597 6 ProductApproval 2012-05-24 00:17:40.550
77597 8 FinalApproval 2012-06-20 05:39:38.113
12345 1 Unsubmitted 2012-07-14 00:31:14.130
12345 2 Submitted 2012-07-21 00:30:35.587
12345 4 InitialApproval 2012-07-24 00:01:46.193
12345 6 ProductApproval 2012-07-24 00:17:40.550
12345 9 FinalApproval 2012-07-30 05:39:38.113

Thanks in advance!
Zack
Go to Top of Page

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.130
77597 2 Submitted 2012-05-14 00:30:35.587
77597 4 InitialApproval 2012-05-24 00:01:46.193
77597 6 ProductApproval 2012-05-24 00:17:40.550
77597 8 FinalApproval 2012-06-20 05:39:38.113
12345 1 Unsubmitted 2012-07-14 00:31:14.130
12345 2 Submitted 2012-07-21 00:30:35.587
12345 4 InitialApproval 2012-07-24 00:01:46.193
12345 6 ProductApproval 2012-07-24 00:17:40.550
12345 9 FinalApproval 2012-07-30 05:39:38.113

Thanks 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]
Go to Top of Page

bi89405
Starting Member

35 Posts

Posted - 2013-05-02 : 09:09:24
Great, thank you again, MuMu88!
Go to Top of Page
   

- Advertisement -