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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Calculating days from one record to next
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bi89405
Starting Member

USA
35 Posts

Posted - 04/30/2013 :  23:14:28  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 04/30/2013 :  23:48:27  Show Profile  Visit chadmat's Homepage  Reply with Quote
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

547 Posts

Posted - 05/01/2013 :  00:17:03  Show Profile  Reply with Quote
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:


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;


Edited by - MuMu88 on 05/01/2013 00:18:25
Go to Top of Page

bi89405
Starting Member

USA
35 Posts

Posted - 05/01/2013 :  09:42:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 05/01/2013 :  11:24:25  Show Profile  Reply with Quote
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

USA
35 Posts

Posted - 05/01/2013 :  12:14:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 05/01/2013 :  13:02:10  Show Profile  Reply with Quote
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

547 Posts

Posted - 05/01/2013 :  13:28:38  Show Profile  Reply with Quote
If I understood your request correctly, you should be able to get what you need by modifying the original query as shown below:


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;


Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 05/01/2013 :  13:37:16  Show Profile  Visit chadmat's Homepage  Reply with Quote
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

USA
35 Posts

Posted - 05/01/2013 :  14:13:35  Show Profile  Reply with Quote
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

547 Posts

Posted - 05/01/2013 :  14:28:48  Show Profile  Reply with Quote
Try this:

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;



Go to Top of Page

bi89405
Starting Member

USA
35 Posts

Posted - 05/01/2013 :  14:31:20  Show Profile  Reply with Quote
MuMu88, you are a superstar! Thank you. It worked like a charm!

Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/01/2013 :  14:39:49  Show Profile  Reply with Quote
Or here is modified Chad's query:


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

Go to Top of Page

bi89405
Starting Member

USA
35 Posts

Posted - 05/01/2013 :  15:15:58  Show Profile  Reply with Quote
Works just as well. Thanks agani MuMu88.
Go to Top of Page

bi89405
Starting Member

USA
35 Posts

Posted - 05/01/2013 :  16:04:24  Show Profile  Reply with Quote
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

547 Posts

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


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

bi89405
Starting Member

USA
35 Posts

Posted - 05/02/2013 :  09:09:24  Show Profile  Reply with Quote
Great, thank you again, MuMu88!
Go to Top of Page
  Previous Topic Topic Next Topic  
 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.12 seconds. Powered By: Snitz Forums 2000