1

I want to return data in such way that the data which have if code has continuous active dates and end dates it should give Min as active date and Max end date, but if code was discontinued it should show two dates

E.g Table

+----+------+---------------+------------+
|Code| Decri|   Active Date |End Date    | 
+----+------+---------------+------------+
|1000| ABC  | 1/1/2011      | 30/6/2011  |
|1000| ABC  | 1/7/2011      | 30/6/2012  |
|1000| ABC  | 1/7/2012      | 30/6/2013  |
|1001| ABC  | 1/7/2013      | 30/6/2014  |
|1001| ABC  |12/20/2015     |12/20/2017  |
|1003| ABC  | 1/1/2011      |30/6/2011   |
|1003| ABC  | 1/7/2012      |30/6/2013   |
|1003| ABC  | 1/7/2014      |30/6/2015   |
|1003| ABC  | 1/7/2015      |12/20/2017  |
+----+------+---------------+------------+

Desire Output

+----+------+---------------+------------+
|Code| Decri|   Active Date |End Date    | 
+----+------+---------------+------------+
|1000| ABC  | 1/1/2011      | 30/6/2013  |
|1001| ABC  | 1/7/2013      | 30/6/2014  |
|1001| ABC  |12/20/2015     |12/20/2017  |
|1003| ABC  | 1/1/2011      |30/6/2013   |
|1003| ABC  | 1/7/2014      |12/20/2017  |
+----+------+---------------+------------+
3

On SQL Server 2012, you can use LAG and SUM with an ORDER BY clause to get what you're after without any self-joins. LAG is used to figure out if the [End Date] value from the previous row for a partition is exactly one day away from the [Start Date] value of the current row. The running total is used to tie the groups together and you get your desired results with a simple aggregate at the end.

SELECT
  Code
, Decri
, MIN([Active Date]) [Active Date]
, MAX([End Date]) [End Date]
FROM
(
    SELECT
      Code
    , Decri
    , [Active Date]
    , [End Date]
    , SUM(start_new_group) OVER (PARTITION BY Code, Decri ORDER BY [Active Date], [End Date]) group_id
    FROM
    (
        SELECT 
          Code
        , Decri
        , [Active Date]
        , [End Date]
        , CASE WHEN DATEADD(DAY, -1, [Active Date]) = LAG([End Date]) OVER (PARTITION BY Code, Decri ORDER BY [Active Date], [End Date]) THEN 0 ELSE 1 END start_new_group
        FROM #x
    ) t
) t2
GROUP BY Code, Decri, group_id;

Results:

╔══════╦═══════╦═════════════╦════════════╗
║ Code ║ Decri ║ Active Date ║  End Date  ║
╠══════╬═══════╬═════════════╬════════════╣
║ 1000 ║ ABC   ║ 2011-01-01  ║ 2013-06-30 ║
║ 1001 ║ ABC   ║ 2013-07-01  ║ 2014-06-30 ║
║ 1001 ║ ABC   ║ 2015-12-20  ║ 2017-12-20 ║
║ 1003 ║ ABC   ║ 2011-01-01  ║ 2013-06-30 ║
║ 1003 ║ ABC   ║ 2014-07-01  ║ 2017-12-20 ║
╚══════╩═══════╩═════════════╩════════════╝
2

I am sure there is a more efficient way to solve this if you can think like Itzik Ben-Gan, but for now, here's an exasperated try. Given:

CREATE TABLE #x
(
  Code int, 
  Decri char(3), 
  [Active Date] date, 
  [End Date] date
);

CREATE UNIQUE CLUSTERED INDEX x ON #x(Code, Decri, [Active Date], [End Date]);

INSERT #x(Code, Decri, [Active Date], [End Date]) VALUES
-- try to avoid regional date formats like d/m/y 
(1000,'ABC', '20110101','20110630'),
(1000,'ABC', '20110701','20120630'),
(1000,'ABC', '20120701','20130630'),
(1001,'ABC', '20130701','20140630'),
(1001,'ABC', '20151220','20171220'),
(1003,'ABC', '20110101','20110630'),
(1003,'ABC', '20110701','20130630'),
(1003,'ABC', '20140701','20150630'),
(1003,'ABC', '20150701','20171220');

This long-winded query gets the answer you want (including a few edge cases I tested):

;WITH NewGroup AS
(
  SELECT *, SeqNo = ROW_NUMBER() OVER (PARTITION BY Code, Decri 
      ORDER BY [Active Date]),
    NewGroup = CASE WHEN COALESCE(DATEDIFF(DAY, LAG([End Date],1) 
      OVER (PARTITION BY Code, Decri 
      ORDER BY [Active Date]), [Active Date]),0) <> 1 THEN 'y' END
    FROM #x
), 
EndOfGroup AS 
(
  SELECT *, EndOfGroup = CASE WHEN LEAD(NewGroup,1) 
       OVER (PARTITION BY Code, Decri ORDER BY SeqNo) = 'y'
       OR SeqNo = MAX(SeqNo) OVER (PARTITION BY Code, Decri) 
       THEN 'y' END
   FROM NewGroup 
),
Groups AS
(
  SELECT *, GroupNo = (SELECT COUNT(*) FROM EndOfGroup AS eog
     WHERE eog.Code = EndOfGroup.Code 
       AND eog.Decri = EndOfGroup.Decri 
       AND eog.EndOfGroup ='y' 
       AND eog.SeqNo < EndOfGroup.SeqNo) 
  FROM EndOfGroup
)
SELECT 
  Code, 
  Decri, 
  [Active Date] = MIN([Active Date]), 
  [End Date] = MAX([End Date])
FROM Groups
GROUP BY Code, Decri, GroupNo
ORDER BY Code, Decri, [Active Date];

I hate the correlated subquery and I am sure there is a fancier way to do this with window functions, but an hour is my limit. :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.