Counting num ber of days using tsql
I have a transaction table that holds CID, status and transaction date. Status can be o, c , r.
A CID can go from o to c to r to c.
For each CID I want to find the duration it has been in o or r, and store it in a target table.
Please help me on how I can accomplish this using tsql, possibly without a cursor. I want to track number of days from o to c and r to c. If a CID is not in c status after being in o or r then it is considered to be in the same status till current date.
Sample data copied below and also attached as a worksheet.
Transaction table data:
1 o 12/1/2014
2 o 12/2/2014
3 o 12/3/2014 --3 o no c: 9 days till today
1 c 12/3/2014 --1 o to c: 12/1/2014 to 12/3/2014 = 1 day
1 r 12/4/2014
2 c 12/4/2014 --2 o to c: 12/2/2014 to 12/4/2014 = 2 days
1 c 12/5/2014 --1 r to c: 12/4/2014 to 12/5/2014 = 1 day
1 r 12/10/2014 --1 r no c: 12/10/2014 to today = 2 days
Target table: has one row per CID+status
CID status DurationInDays
1 o 2
1 r 3 (1+2)
2 o 2
3 o 9
Calculate-duration.xls
Thank you for your help.
A CID can go from o to c to r to c.
For each CID I want to find the duration it has been in o or r, and store it in a target table.
Please help me on how I can accomplish this using tsql, possibly without a cursor. I want to track number of days from o to c and r to c. If a CID is not in c status after being in o or r then it is considered to be in the same status till current date.
Sample data copied below and also attached as a worksheet.
Transaction table data:
1 o 12/1/2014
2 o 12/2/2014
3 o 12/3/2014 --3 o no c: 9 days till today
1 c 12/3/2014 --1 o to c: 12/1/2014 to 12/3/2014 = 1 day
1 r 12/4/2014
2 c 12/4/2014 --2 o to c: 12/2/2014 to 12/4/2014 = 2 days
1 c 12/5/2014 --1 r to c: 12/4/2014 to 12/5/2014 = 1 day
1 r 12/10/2014 --1 r no c: 12/10/2014 to today = 2 days
Target table: has one row per CID+status
CID status DurationInDays
1 o 2
1 r 3 (1+2)
2 o 2
3 o 9
Calculate-duration.xls
Thank you for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank You bhess1. That seems to be working. Can you please explain the logic in simple words so I can understand.
Thanks a lot again.
Thanks a lot again.
ASKER
Thank You.
The WITH statement creates a virtual table consisting of (1) The CID, Transaction date, the latest transaction ID for that CID/Transaction date, and the next date a transaction with that CID occurs on, or the current date if there is no other record with that CID on a later date.
The output of the WITH statement is essentially another table. So we join that output back to the original source table by CID and Seq, and use the pair of dates in the WITH table to calculate the duration of the activity.
The output of the WITH statement is essentially another table. So we join that output back to the original source table by CID and Seq, and use the pair of dates in the WITH table to calculate the duration of the activity.
in particular do you use SQL 2012 or later?
Is there s unique ID for each row in this table?
What is the table's name.?