CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Sheri G
Sheri G

Reputation: 47

Count previous row only if > 2 days later

In SQL Server 2012 using Studio: I need results displayed count of distinct clientnumbers (CN) for re-entry, grouped by Type like this: 

Type    CountOfCN    
5        1  
10       3

Only a RE-entry counts (ENTRY_NO 1 never counts) and it has to be more than 2 days after the end of the previous entry for that clientnumber. So basically ENTRY_NO 1 doesn't count. ENTRY_NO 2 counts if it's startdate is more than 2 days after the enddate of ENTRY_NO 1, and so on with ENTRY_NO 3, 4, 5.

I got ENTRY_NO by doing a ROW_NUMBER function when I created the table. I have no idea how to go about creating a datediff or dateadd function (?) to look at the previous row's enddate and calculate it with my startdate for each CN?

Here is my table:

CN    STARTDATE   ENDDATE    TYPE  ENTRY_NO  
1     1/1/2018    1/20/2018  10    1
1     1/21/2018   1/30/2018   5    2
1     2/3/2018    NULL       10    3
2     1/1/2018    1/20/2018  10    1
2     1/27/2018   1/30/2018  10    2
3     1/1/2018    1/20/2018   5    1
3     1/27/2018   1/30/2018  10    2
3     2/10/2018   2/20/2018   5    3
4     1/7/2018    1/30/2018   5    1
5     1/27/2018   1/30/2018   5    1
5     1/31/2018   NULL        5    2

So the rows that should be in the results are ENTRY_NO 2 for CN 1, ENTRY_NO 2 for CN 2, ENTRY_NO 2 & 3 for CN 3.

Only the last Entry may/may not have a NULL enddate

Upvotes: 0

Views: 85

Answers (3)

critical_error
critical_error

Reputation: 6706

So this ended up being more involved than I first thought, but here it goes...

You can run this example in SSMS.

Create a table variable matching your definition above:

DECLARE @data TABLE ( CN INT, STARTDATE DATETIME, ENDDATE DATETIME, [TYPE] INT, ENTRY_NO INT );

Insert data given:

INSERT INTO @data ( CN, STARTDATE, ENDDATE, [TYPE], ENTRY_NO ) VALUES   
( 1, '1/1/2018', '1/20/2018', 10, 1 )
, ( 1, '1/21/2018', '1/30/2018', 5, 2 )
, ( 1, '2/3/2018', NULL, 10, 3 )
, ( 2, '1/1/2018', '1/20/2018', 10, 1 )
, ( 2, '1/27/2018', '1/30/2018', 10, 2 )
, ( 3, '1/1/2018', '1/20/2018', 5, 1 )
, ( 3, '1/27/2018', '1/30/2018', 10, 2 )
, ( 3, '2/10/2018', '2/20/2018', 5, 3 )
, ( 4, '1/7/2018', '1/30/2018', 5, 1 )
, ( 5, '1/27/2018', '1/30/2018', 5, 1 )
, ( 5, '1/31/2018', NULL, 5, 2 );

Confirm inserted data:

+----+-------------------------+-------------------------+------+----------+
| CN |        STARTDATE        |         ENDDATE         | TYPE | ENTRY_NO |
+----+-------------------------+-------------------------+------+----------+
|  1 | 2018-01-01 00:00:00.000 | 2018-01-20 00:00:00.000 |   10 |        1 |
|  1 | 2018-01-21 00:00:00.000 | 2018-01-30 00:00:00.000 |    5 |        2 |
|  1 | 2018-02-03 00:00:00.000 | NULL                    |   10 |        3 |
|  2 | 2018-01-01 00:00:00.000 | 2018-01-20 00:00:00.000 |   10 |        1 |
|  2 | 2018-01-27 00:00:00.000 | 2018-01-30 00:00:00.000 |   10 |        2 |
|  3 | 2018-01-01 00:00:00.000 | 2018-01-20 00:00:00.000 |    5 |        1 |
|  3 | 2018-01-27 00:00:00.000 | 2018-01-30 00:00:00.000 |   10 |        2 |
|  3 | 2018-02-10 00:00:00.000 | 2018-02-20 00:00:00.000 |    5 |        3 |
|  4 | 2018-01-07 00:00:00.000 | 2018-01-30 00:00:00.000 |    5 |        1 |
|  5 | 2018-01-27 00:00:00.000 | 2018-01-30 00:00:00.000 |    5 |        1 |
|  5 | 2018-01-31 00:00:00.000 | NULL                    |    5 |        2 |
+----+-------------------------+-------------------------+------+----------+

Run SQL to get type count given your business rules:

  • ENTRY_NO must be greater than 1
  • Current CN ENDDATE must be greater than 2 days from previous ENDDATE

T-SQL:

SELECT
    [TYPE], COUNT( DISTINCT CN ) AS ClientCount
FROM @data
WHERE
    CN IN (

        SELECT DISTINCT CN FROM (

            SELECT
                dat.CN
                , dat.ENTRY_NO
                , dat.[TYPE]
                , DATEDIFF( DD
                    , LAG( ENDDATE, 1, NULL ) OVER ( PARTITION BY CN ORDER BY CN, ENDDATE ) -- gets enddate for previous CN entry
                    , ENDDATE
                ) AS DayDiff
            FROM @data dat

        ) AS Clients
        WHERE
            Clients.ENTRY_NO >= 2
            AND Clients.DayDiff > 2
    )
GROUP BY
    [TYPE]
ORDER BY
    [TYPE];

Returns:

+------+-------------+
| TYPE | ClientCount |
+------+-------------+
|    5 |           2 |
|   10 |           3 |
+------+-------------+

A quick look at the IN subquery shows us that CNs 1, 2, and 3 will be included during the "TYPE" count.

SELECT
    dat.CN
    , dat.ENTRY_NO
    , dat.[TYPE]
    , DATEDIFF( DD
        , LAG( ENDDATE, 1, NULL ) OVER ( PARTITION BY CN ORDER BY CN, ENDDATE ) -- gets enddate for previous CN entry
        , ENDDATE
    ) AS DayDiff
FROM @data dat
ORDER BY
    dat.CN, dat.ENTRY_NO;

+----+----------+------+---------+
| CN | ENTRY_NO | TYPE | DayDiff |
+----+----------+------+---------+
|  1 |        1 |   10 | NULL    |
|  1 |        2 |    5 | 10      |
|  1 |        3 |   10 | NULL    |
|  2 |        1 |   10 | NULL    |
|  2 |        2 |   10 | 10      |
|  3 |        1 |    5 | NULL    |
|  3 |        2 |   10 | 10      |
|  3 |        3 |    5 | 21      |
|  4 |        1 |    5 | NULL    |
|  5 |        1 |    5 | NULL    |
|  5 |        2 |    5 | NULL    |
+----+----------+------+---------+

Upvotes: 0

LukStorms
LukStorms

Reputation: 29647

Using the LAG window function you can get the previous enddate.

SELECT * 
FROM
(
  SELECT * , 
   LAG(ENDDATE) OVER (PARTITION BY CN ORDER BY STARTDATE) AS prevEndDate
  FROM yourtable
) q
WHERE DATEDIFF(d, prevEndDate, STARTDATE) > 2
AND ENDDATE IS NOT NULL

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

Inner join the table to itself on the conditions you want to enforce:

  • Can't be Entry_No 1
  • The Entry_No on one side is one greater than on the other side
  • Previous Entry must be more than 2 days earlier
  • Both sides of the join have the same CN

Use that join to create a CTE or derived table, and then SELECT from it, grouping by Type and getting the COUNT(*)

Upvotes: 0

Related Questions