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

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!

CodePudding user response：

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

CodePudding user response：

Try it:

DECLARE @Test TABLE (
  [AccountID] [bigint] NOT NULL,
  [Date] [date] NOT NULL
);

-- sample
--INSERT INTO @Test 
--SELECT 2376, CONVERT([datetime], '2022-01-01', 120) UNION ALL
--SELECT 2376, CONVERT([datetime], '2022-01-02', 120) UNION ALL
--SELECT 2376, CONVERT([datetime], '2022-01-03', 120) UNION ALL
--SELECT 2376, CONVERT([datetime], '2022-01-07', 120) UNION ALL
--SELECT 2376, CONVERT([datetime], '2022-01-08', 120) UNION ALL
--SELECT 2376, CONVERT([datetime], '2022-01-09', 120);

-- add month, year up
-- add mixing
-- add another id
INSERT INTO @Test 
SELECT 2376, CONVERT([datetime], '2022-01-01', 120) UNION ALL
SELECT 2532, CONVERT([datetime], '2022-12-31', 120) UNION ALL
SELECT 2532, CONVERT([datetime], '2022-01-31', 120) UNION ALL
SELECT 2532, CONVERT([datetime], '2022-12-30', 120) UNION ALL
SELECT 2376, CONVERT([datetime], '2022-01-08', 120) UNION ALL
SELECT 2376, CONVERT([datetime], '2022-01-03', 120) UNION ALL
SELECT 2532, CONVERT([datetime], '2022-02-02', 120) UNION ALL
SELECT 2532, CONVERT([datetime], '2022-02-01', 120) UNION ALL
SELECT 2532, CONVERT([datetime], '2023-01-01', 120) UNION ALL
SELECT 2376, CONVERT([datetime], '2022-01-09', 120) UNION ALL
SELECT 2376, CONVERT([datetime], '2022-01-07', 120) UNION ALL
SELECT 2376, CONVERT([datetime], '2022-01-02', 120);

WITH [CheckDiffs] AS (
  SELECT 
    t1.[AccountID],
    t1.[Date],
    CASE WHEN ISNULL(DATEDIFF(dd, x.[Date], t1.[Date]), 0) = 1 THEN 1 ELSE 0 END AS [IsContinious]
  FROM
    @Test t1
    OUTER APPLY (
      SELECT TOP 1
        t2.[Date]
      FROM @Test t2 
      WHERE
        t2.[AccountID] = t1.[AccountID]
        AND t2.[Date] < t1.[Date]
      ORDER BY
        t2.[Date] DESC
    ) x
),

[DateSeparate] AS (
  SELECT
    t1.[AccountID],
    MIN(t1.[Date]) OVER (PARTITION BY x.[Date]) AS [Begin],
    MAX(t1.[Date]) OVER (PARTITION BY x.[Date]) AS [End]
  FROM
    [CheckDiffs] t1
    OUTER APPLY (
      SELECT TOP 1
        t2.[Date]
      FROM [CheckDiffs] t2 
      WHERE
        t2.[AccountID] = t1.[AccountID]
        AND t2.[Date] <= t1.[Date]
        AND t2.[IsContinious] = 0
      ORDER BY
        t2.[Date] DESC
    ) x
)

-- and group it
SELECT
  t.[AccountID],
  t.[Begin],
  t.[End]
FROM
  [DateSeparate] t
GROUP BY
  t.[AccountID],
  t.[Begin],
  t.[End]