CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
user2162331
user2162331

Reputation: 99

Running total of records based on date ranges in t-sql

Sample data:

groupID CustomerID  CustomerAddr    work_date   work_order
CA123   ABC12345    123 MAIN ST     2/1/2012    WORKNEW
CA123   ABC12345    123 MAIN ST     10/9/2012   ZZZ888
CA123   ABC12345    123 MAIN ST     3/9/2013    ZZZ131
WA999   ZZZ99909    451 EAST ST     1/13/2013   SY1234
WA999   ZZZ99909    451 EAST ST     4/15/2013   WORKOTHER
WA999   ZZZ99909    451 EAST ST     5/17/2013   SY1244
WA999   ZZZ99909    451 EAST ST     12/8/2013   SY1334
CA123   ABD54321    522 AVE A       4/21/2013   WW9999

For the given sample data, I need to count how many times records with the same groupID and CustomerID appear within 6, 12 and 18 month intervals. The count should be independent of other records with same groupID and customerID.

The output should look something like this:

groupID CustomerID  work_date   six_month_count twelve_month_count  twenty_four_month_count
CA123   ABC12345    2/1/2012    0               1                   2
CA123   ABC12345    10/9/2012   1               1                   1
CA123   ABC12345    3/9/2013    0               0                   0
WA999   ZZZ99909    1/13/2013   2               3                   3               
WA999   ZZZ99909    4/15/2013   1               2                   2
WA999   ZZZ99909    5/17/2013   0               1                   1
WA999   ZZZ99909    12/8/2013   0               0                   0
CA123   ABD54321    4/21/2013   0               0                   0

The only thing I can think to do is to grab the current record and max records for each row, but i don't know how to count the records for the given ranges.

Any help would be appreciated.

Upvotes: 1

Views: 395

Answers (1)

StuartLC
StuartLC

Reputation: 107247

You can join the table to itself, and then work out the 6,12 and 24 columns via an aggregate. I've used Work_Order as a key to avoid counting the record vs itself during the self join.

WITH cteCountInRange
AS
(
    SELECT c1.CustomerID, 
           c1.GroupID, 
           c1.Work_Date as WorkDate1, 
           c2.Work_Date as WorkDate2, 
           CASE WHEN c2.Work_Date > c1.Work_Date AND c1.Work_Order <> c2.Work_Order 
                THEN 1 
                ELSE 0 
           END AS Eligible,
           DATEDIFF(M, c1.Work_Date, c2.Work_Date ) AS MonthsDiff
    FROM CustomerData c1 INNER JOIN CustomerData c2
        ON c1.CustomerID = c2.CustomerID AND c1.GroupID = c2.GroupID
)
SELECT cir.CustomerID, cir.GroupID, cir.WorkDate1, 
    SUM(CASE WHEN Eligible = 1 AND MonthsDiff <= 6 THEN 1 ELSE 0 END) AS SixMonthCount,
    SUM(CASE WHEN Eligible = 1 AND MonthsDiff <= 12 THEN 1 ELSE 0 END) AS TwelveMonthCount,
    SUM(CASE WHEN Eligible = 1 AND MonthsDiff <= 24 THEN 1 ELSE 0 END) AS TwentyFourMonthCount
FROM cteCountInRange cir
GROUP BY cir.CustomerID, cir.GroupID, cir.WorkDate1;

Fiddle here

Edit
Additional comment - the reason we can't filter out "InEligible" rows in the CTE is because a line of ZEROES is needed if there are no other matched rows after the current one. If we filtered these, we would need another LEFT JOIN back to the original table to include the zero rows.

Upvotes: 1

Related Questions