How To Increment Date By One Year, Based on Last Result (DateTime Banding)

0
votes

Hopefully I'll be able to explain this better than the title.

I have an activity table that looks like this:

|ID|    |LicenseNumber|   |DateTime|
|1 |    |123          |   |2017-11-17 11:19:04.420|
|2 |    |123          |   |2017-11-26 10:16:52.790|  
|3 |    |123          |   |2018-02-06 11:13:21.480|  
|4 |    |123          |   |2018-02-19 10:12:32.493|  
|5 |    |123          |   |2018-05-16 09:33:05.440|  
|6 |    |123          |   |2019-01-02 10:05:25.193|

What I need is a count of rows per License Number, grouped in essentially 12 month intervals. But, the year needs to start from when the previous entry ended.

For example, I need a count of all records for 12 months from 2017-11-17 11:19:04.420, and then I need a count of all records starting from (2017-11-17 11:19:04.420 + 12 months) for another 12 months, and so on.

I've considered using recursive CTEs, the LAG function etc. but can't quite figure it out. I could probably do something with a CASE statement and static values, but that would require updating the report code every year.

Any help pointing me in the right direction would be much appreciated!

2
sqlsql-servertsql
Could you post your expected output for the data you've provided and the your attempt so far please? – Larnu

2 Answers

0
votes

I think the following code using CTE can help you but I am not totally sure what you want to achieve:

WITH CTE AS 
( 
SELECT TOP 1 DateTime
FROM YourTable
ORDER BY ID
UNION ALL
SELECT DATEADD(YEAR, 1, DateTime)
FROM CTE 
WHERE DateTime<= DATEADD(YEAR, 1, GETDATE())
)
SELECT  LicenseNumber, DateTime, Count(*) AS Rows
FROM CTE
INNER JOIN YourTable
ON YourTable.DateTime BETWEEN CTE.DateTime AND DATEADD(YEAR, 1, CTE.DateTime)
GROUP BY LicenseNumber, DateTime;
0
votes

Hmmm. Do you just need the number of records in 12-month intervals after the first record?

If so:

select dateadd(year, yr - 1, min_datetime),
       dateadd(year, yr, min_datetime),
       count(t.id)
from (values (1), (2), (3)) v(yr) left join
     (select t.*,
             min(datetime) over () as min_datetime
      from t
     ) t     
     on t.datetime >= dateadd(year, yr - 1, min_datetime) and
        t.datetime < dateadd(year, yr, min_datetime)
group by dateadd(year, yr - 1, min_datetime),
         dateadd(year, yr, min_datetime)
order by yr;

This can easily be extended to more years, if it is what you want.