Stored procedure to output date differences I have a table of records which, in very simplified form, comprise a pair of dates, well actually let's say three dates:


ID
creation_date
process_date
done_date


and I am required to output records of counts as follows:


ID  P=C  P=C+1   P=C+2+   D=C   D=C+1   D=C+2+


where "P=C+1" for example is the number of records with a specific ID for which process_date equals creation_date + 1, and similarly for the other columns (and C+2+ indicates two or more days after creation_date).

So being a bit shaky with stored procedure syntax, and even the SQL statement required for this, using temp tables (presumably), I would welcome any help.

Typical output might be as follows, grouping by ID and using vaguely plausible values off the top of my head:


ID   P=C  P=C+1   P=C+2+   D=C   D=C+1   D=C+2+
XY   130   29       4      140    13
AB    16    3               27    19       2
   :::::::::::::::::::::::::::::::::::
Using a CASE expression, I think you could do something like this:

Declare @T table (ID int,creation_date date,process_date date,done_date date)

insert into @T(id,creation_date,process_date,done_date) values
(1,'2018-01-01','2018-01-01','2018-01-01')
,(1,'2018-01-01','2018-01-01','2018-01-01')
,(1,'2018-01-01','2018-01-03','2018-01-01')
,(1,'2018-01-01','2018-01-03','2018-01-02')
,(1,'2018-01-01','2018-01-03','2018-01-03')

SELECT ID
    ,sum(CASE 
            WHEN process_date = creation_date
                THEN 1
            ELSE 0
            END) AS 'P=C'
    ,sum(CASE 
            WHEN process_date = (dateadd(day, 1, creation_date))
                THEN 1
            ELSE 0
            END) AS 'P=C+1'
    ,sum(CASE 
            WHEN process_date &gt;= (dateadd(day, 2, creation_date))
                THEN 1
            ELSE 0
            END) AS 'P=C+2+'
    ,sum(CASE 
            WHEN done_date = creation_date
                THEN 1
            ELSE 0
            END) AS 'D=C'
    ,sum(CASE 
            WHEN done_date = (dateadd(day, 1, creation_date))
                THEN 1
            ELSE 0
            END) AS 'D=C+1'
    ,sum(CASE 
            WHEN done_date &gt;= (dateadd(day, 2, creation_date))
                THEN 1
            ELSE 0
            END) AS 'D=C+2+'
FROM @T
GROUP BY ID




| ID | P=C | P=C+1 | P=C+2+ | D=C | D=C+1 | D=C+2+ |
|----|-----|-------|--------|-----|-------|--------|
| 1  | 2   | 0     | 3      | 3   | 1     | 1      |