none
Get the Min And Max date range with in specific date range RRS feed

  • Question

  • Can someone help me on the below requirement in SQL 

    A table contains the records like 

    DECLARE @T TABLE (ID INT,SDATE DATE,EDATE DATE)
    INSERT INTO @T
    SELECT 1,'2019-03-01','2019-03-07' UNION ALL
    SELECT 1,'2019-03-02','2019-03-08' UNION ALL
    SELECT 1,'2019-03-04','2019-03-09' UNION ALL
    SELECT 1,'2019-03-06','2019-03-11' UNION ALL
    SELECT 1,'2019-04-12','2019-04-23' UNION ALL
    SELECT 2,'2019-03-01','2019-03-06' UNION ALL
    SELECT 3,'2019-05-20','2019-07-01'

    And the Derived output for me is as:

    id SDATE          EDATE
    1 2019-03-01 2019-03-11
    1 2019-04-12 2019-04-23
    2 2019-03-01 2019-03-06
    3 2019-05-20 2019-07-01

    And the logic is I need to derive the Min and Max with in the ranges for each id.

    Can you please help me on this?

    Wednesday, April 3, 2019 5:06 AM

All replies

  • Hi, kartheek Vasantha

    DECLARE @T TABLE (ID INT,SDATE DATE,EDATE DATE)
    INSERT INTO @T
    SELECT 1,'2019-03-01','2019-03-07' UNION ALL
    SELECT 1,'2019-03-02','2019-03-08' UNION ALL
    SELECT 1,'2019-03-04','2019-03-09' UNION ALL
    SELECT 1,'2019-03-06','2019-03-11' UNION ALL
    SELECT 1,'2019-04-12','2019-04-23' UNION ALL
    SELECT 2,'2019-03-01','2019-03-06' UNION ALL
    SELECT 3,'2019-05-20','2019-07-01'
    
    
    
    
    select distinct id,min(sdate) sdate,max(edate) edate from 
    
    (select id,format(sdate,'yyyy-MM') dt,SDATE,EDATE from @t
    
    union all 
    select id,format(edate,'yyyy-MM'), SDATE,EDATE from @t)  as t group by id,dt
    order by id

    Hope it can help you.

     

    Best Regards,

    Natig


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Wednesday, April 3, 2019 5:16 AM
  • Hi natig,

    Thanks for your early response but here my scenario is not based on month and year.consider the smae exampe with one one new record

    DECLARE @T TABLE (ID INT,SDATE DATE,EDATE DATE)
    INSERT INTO @T
    SELECT 1,'2019-03-01','2019-03-07' UNION ALL
    SELECT 1,'2019-03-02','2019-03-08' UNION ALL
    SELECT 1,'2019-03-04','2019-03-09' UNION ALL
    SELECT 1,'2019-03-06','2019-03-11' UNION ALL
    SELECT 1,'2019-03-17','2019-03-21' UNION ALL
    SELECT 1,'2019-04-12','2019-04-23' UNION ALL
    SELECT 2,'2019-03-01','2019-03-06' UNION ALL
    SELECT 3,'2019-05-20','2019-07-01'

    And the expected is:

    id sdate          edate
    1 2019-03-01  2019-03-11
    1      2019-03-17        2019-03-21
    1 2019-04-12  2019-04-23
    2 2019-03-01  2019-03-06
    3 2019-05-20  2019-07-01

    Wednesday, April 3, 2019 5:25 AM
  • Hi kartheek Vasantha,

     

    Please try following script.

     
    DECLARE @T TABLE (ID INT,SDATE DATE,EDATE DATE)
    INSERT INTO @T
    SELECT 1,'2019-03-01','2019-03-07' UNION ALL
    SELECT 1,'2019-03-02','2019-03-08' UNION ALL
    SELECT 1,'2019-03-04','2019-03-09' UNION ALL
    SELECT 1,'2019-03-06','2019-03-11' UNION ALL
    SELECT 1,'2019-04-12','2019-04-23' UNION ALL
    SELECT 2,'2019-03-01','2019-03-06' UNION ALL
    SELECT 3,'2019-05-20','2019-07-01'
    
    SELECT d.ID,d.SDATE,MIN(d.EDATE) as EDATE from 
    (SELECT a.ID,a.SDATE,b.EDATE FROM @T a,@T b ,@T c 
    WHERE a.EDATE<=b.EDATE GROUP BY a.ID,a.SDATE,b.EDATE 
    HAVING MAX(CASE WHEN (a.SDATE>c.SDATE and a.SDATE<=c.EDATE and a.ID=c.ID ) OR (b.EDATE>=c.SDATE and b.EDATE<c.EDATE and b.ID=c.ID ) then 1 ELSE 0 END) =0) d 
    GROUP BY  d.ID,d.SDATE
    order by d.ID,d.SDATE
    /*
    ID          SDATE      EDATE
    ----------- ---------- ----------
    1           2019-03-01 2019-03-11
    1           2019-04-12 2019-04-23
    2           2019-03-01 2019-03-06
    3           2019-05-20 2019-07-01
    */

     

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 3, 2019 6:26 AM
  • Try again following script

    DECLARE @T TABLE (ID INT,SDATE DATE,EDATE DATE)
    
    INSERT INTO @T
    SELECT 1,'2019-03-01','2019-03-07' UNION ALL
    SELECT 1,'2019-03-02','2019-03-08' UNION ALL
    SELECT 1,'2019-03-04','2019-03-09' UNION ALL
    SELECT 1,'2019-03-06','2019-03-11' UNION ALL
    SELECT 1,'2019-03-17','2019-03-21' UNION ALL
    SELECT 1,'2019-04-12','2019-04-23' UNION ALL
    SELECT 2,'2019-03-01','2019-03-06' UNION ALL
    SELECT 3,'2019-05-20','2019-07-01'
    
    ;
    with cte
    as
    (
    select * from (
    
    select id,row_number() over (partition by id order by sdate) rm ,SDATE ,EDATE from @t
    ) as t
    
    )
    
    ,
    cte1
    as
    (
    
    select *
    ,iif((select max(edate) from cte where rm<a1.rm and id=a1.ID) is null,sdate,iif(SDATE>(select max(edate) from cte where rm<a1.rm and id=a1.ID),sdate,NULL)) mn
    from cTE a1 )
    -- where SDATE>(select max(edate) from cte where rm<a1.rm and id=a1.ID) 
    
    select id, mn ,isnull(lead((select max(edate) from cte where cte1.SDATE>cte.EDATE),1) over ( partition by id order by rm),EDATE) from cte1 where mn is not null
    
    

    Hope it can help you.

     

    Best Regards,

    Natig


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, April 3, 2019 6:32 AM
  • DECLARE @T TABLE (ID INT,SDATE DATE,EDATE DATE)
    INSERT INTO @T
    SELECT 1,'2019-03-01','2019-03-07' UNION ALL
    SELECT 1,'2019-03-02','2019-03-08' UNION ALL
    SELECT 1,'2019-03-04','2019-03-09' UNION ALL
    SELECT 1,'2019-03-06','2019-03-11' UNION ALL
    SELECT 1,'2019-03-17','2019-03-21' UNION ALL
    SELECT 1,'2019-04-12','2019-04-23' UNION ALL
    SELECT 2,'2019-03-01','2019-03-06' UNION ALL
    SELECT 3,'2019-05-20','2019-07-01'
    
    ;WITH mycte AS
    (
        SELECT *
            , CASE
                WHEN DATEDIFF(day, LAG(EDATE) OVER (PARTITION BY ID ORDER BY SDATE), SDATE) <= 1 THEN 0
                ELSE 1
            END AS [flag]
        FROM @T
    ),
    mycte1 AS
    (
        SELECT *, SUM([flag]) OVER (PARTITION BY ID ORDER BY SDATE) AS grp
        FROM mycte
    )
    SELECT ID, MIN(SDATE) AS start_dt, MAX(EDATE) AS end_dt
    FROM mycte1
    GROUP BY ID, grp
    Order by ID, start_dt
    
    

    Wednesday, April 3, 2019 5:33 PM
    Moderator
  • Try this:

    select id, min(sdate) as sdate, max(edate) as edate
    from @T
    group by id, month(sdate), month(edate)

    Wednesday, April 3, 2019 8:06 PM
  • SELECT ID, MIN(SDATE) AS SDATE, MAX(EDATE) AS EDATE
    FROM @T
    GROUP BY ID;

    A Fan of SSIS, SSRS and SSAS

    Wednesday, April 3, 2019 9:31 PM