How to Get the Start Date and End Date according to a Date List with SQL?

December 6, 2022

I have a question and would be really appreciate if anyone could help.

I have a list of dates for many accounts like showed in the attached picture, and I want to create start date and end date base on this date list. At the same time, if for one account, the date list is not continuous, then the start date and end date should be re-calculated from the break point.

Take the above picture as an example, the date list for account 2376 breaks for 2022-01-04, 2022-01-05, and 2022-01-06, so the result I want would be the following table, which shows the account has dates from 2022-01-01 to 2022-01-03, then has dates from 2022-01-07 to 2022-01-09:

Thanks a lot for any suggestions, I would really appreciate it!

>Solution :

So you can use a query like below

WITH AugmentedData AS
  (
  SELECT *, 
       DATEDIFF(M,MIN(Date) OVER( PARTITION BY AccountID ORDER BY Date ASC), date) -
  ROW_NUMBER() OVER(PARTITION BY AccountID ORDER BY Date ASC) AS GroupingDate
  FROM TB
  )
SELECT AccountID, Min(Date) AS StartDate, MAX(Date) AS EndDate
FROM AugmentedData
GROUP BY AccountID, GroupingDate

Also adding a demo link