Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Counting num ber of days using tsql

Posted on 2014-12-12
Medium Priority
Last Modified: 2014-12-18
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


Thank you for your help.
Question by:patd1
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 49

Expert Comment

ID: 40497304
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.?
LVL 32

Accepted Solution

Brendt Hess earned 2000 total points
ID: 40497331
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:

    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')

Open in new window

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

;WITH tf AS (
        MAX(seq) AS seq,
                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, 

    SUM(DATEDIFF(DAY, tf.transdate, tf.nextDt)) AS durationInDays
    ON tf.CID = t.CID
    AND tf.seq = t.seq
WHERE t.status IN ('o', 'r')

Open in new window


Author Comment

ID: 40500406
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.

Author Closing Comment

ID: 40507298
Thank You.
LVL 32

Expert Comment

by:Brendt Hess
ID: 40507543
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.

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question