# 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

###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

freelancerCommented:
I see you have chosen SQL 2008 as the topic but is that the version you actually use?
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.?
0
Senior DBACommented:
Your data source must have uniquely identifiable rows for any such calculation to work.  For my work, I created and populated a simple temp table:

``````CREATE TABLE #tt (
CID int NOT NULL,
status char NULL,
transdate datetime,
seq int IDENTITY(1, 1)
)

INSERT INTO #tt (cid, status, transdate) VALUES (1, 'o', '12/1/2014')
INSERT INTO #tt (cid, status, transdate) VALUES (2, 'o', '12/2/2014')
INSERT INTO #tt (cid, status, transdate) VALUES (3, 'o', '12/3/2014')
INSERT INTO #tt (cid, status, transdate) VALUES (1, 'c', '12/3/2014')
INSERT INTO #tt (cid, status, transdate) VALUES (1, 'r', '12/4/2014')
INSERT INTO #tt (cid, status, transdate) VALUES (2, 'c', '12/4/2014')
INSERT INTO #tt (cid, status, transdate) VALUES (1, 'c', '12/5/2014')
INSERT INTO #tt (cid, status, transdate) VALUES (1, 'r', '12/10/2014')
``````

This method worked well to get your data out in the form you needed.

``````;WITH tf AS (
SELECT
CID,
transdate,
MAX(seq) AS seq,
ISNULL(
(
SELECT MIN(transdate)
FROM #tt AS tX
WHERE tX.CID = #tt.CID
AND tX.transdate > #tt.transdate
),
CONVERT(varchar(10), CURRENT_TIMESTAMP, 101)
) AS nextDt
FROM #tt
GROUP BY cid,
transdate
)

SELECT
tf.CID,
status,
SUM(DATEDIFF(DAY, tf.transdate, tf.nextDt)) AS durationInDays
FROM tf
INNER JOIN #tt AS t
ON tf.CID = t.CID
AND tf.seq = t.seq
WHERE t.status IN ('o', 'r')
GROUP BY tf.CID,
status
ORDER BY
tf.CID,
status
``````
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
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.
0
Author Commented:
Thank You.
0
Senior DBACommented:
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.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.