Limit the records based on ranges column in sql server

Sahoo 46 Reputation points
2023-02-26T20:50:08.0833333+00:00

Hi @Erland Sommarskog ,

Problem Statement: If partially overlap then return till the next plan and incase of fully overlap then return maxplanid.

Input data is having all the required information just we have to limit no of records based on the condition. I tried myself but didn't get exected results.

Case 1: If plan is partially overlap then return the records between that overlap start and Overlapend date

For Example: Planid : 10 is partially overlap so we need to return Nov and dec because new planid :20 has released from jan-2023.

Case 2: If plan is Fully overlap then return the max plan id records between that overlap start and Overlapend date

For Example: Planid :20,30,40,50 are the fully overlap plans then need to return only max planid :50 records from jan,feb,mar,apr-2023.

image

-

  - **Input  Data**
     
   `Create table #target(`
  `SLID int,`
  `planid	int,`
  `monthstartdate date,`
  `StartDate date,`
  `EndDate Date,`
  `OverlapstartDate Date,`
  `OverlapEndDate Date,`
  `OLPlanID int,`
  `IsFullOverlap int`
  `)`
  `insert into #target values`
  `(1,10,'2022-11-01','2022-11-01','2023-04-30','2022-11-01','2022-12-31',10,0),`
  `(1,10,'2022-12-01','2022-11-01','2023-04-30','2022-11-01','2022-12-31',10,0),`
  `(1,10,'2023-01-01','2022-11-01','2023-04-30','2022-11-01','2022-12-31',10,0),`
  `(1,10,'2023-02-01','2022-11-01','2023-04-30','2022-11-01','2022-12-31',10,0),`
  `(1,10,'2023-03-01','2022-11-01','2023-04-30','2022-11-01','2022-12-31',10,0),`
  `(1,10,'2023-04-01','2022-11-01','2023-04-30','2022-11-01','2022-12-31',10,0),`
  `(1,20,'2022-11-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',20,1),`
  `(1,20,'2022-12-01','2023-01-01','2023-04-30','2023-01-01','2023-01-01',20,1),`
  `(1,20,'2023-01-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',20,1),`
  `(1,20,'2023-02-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',20,1),`
  `(1,20,'2023-03-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',20,1),`
  `(1,20,'2023-04-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',20,1),`
  `(1,30,'2022-11-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',30,1),`
  `(1,30,'2022-12-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',30,1),`
  `(1,30,'2023-01-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',30,1),`
  `(1,30,'2023-02-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',30,1),`
  `(1,30,'2023-03-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',30,1),`
  `(1,30,'2023-04-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',30,1),`
  `(1,40,'2022-11-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',40,1),`
  `(1,40,'2022-12-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',40,1),`
  `(1,40,'2023-01-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',40,1),`
  `(1,40,'2023-02-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',40,1),`
  `(1,40,'2023-03-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',40,1),`
  `(1,40,'2023-04-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',40,1),`
  `(1,50,'2022-11-01','2023-01-01','2023-04-30',NULL,NULL,NULL,NULL),`
  `(1,50,'2022-12-01','2023-01-01','2023-04-30',NULL,NULL,NULL,NULL),`
  `(1,50,'2023-01-01','2023-01-01','2023-04-30',NULL,NULL,NULL,NULL),`
  `(1,50,'2023-02-01','2023-01-01','2023-04-30',NULL,NULL,NULL,NULL),`
  `(1,50,'2023-03-01','2023-01-01','2023-04-30',NULL,NULL,NULL,NULL),`
  `(1,50,'2023-04-01','2023-01-01','2023-04-30',NULL,NULL,NULL,NULL);`
  
  • Output

User's image

Thanks In Advance

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,644 questions
{count} votes

Accepted answer
  1. CosmogHong-MSFT 22,621 Reputation points Microsoft Vendor
    2023-02-27T02:41:55.7433333+00:00

    Hi @Sahoo

    Planid :20,30,40,50 are the fully overlap plans then need to return only max planid :50

    One thing I am confused is that you say '50' is the fully overlap plan, however, the OverlapStartdate and OverlapEnddate of '50' is NULL in your table.

    Please check this query:

    ;WITH CTE AS
    (
     SELECT SLID,planid,monthstartdate,StartDate,EndDate,OverlapstartDate,OverlapEndDate,OLPlanID
           ,DENSE_RANK()OVER(ORDER BY planid DESC) AS RNum
     FROM #target
     WHERE (monthstartdate BETWEEN StartDate AND EndDate)
       AND ((StartDate = OverlapstartDate AND EndDate = OverlapEndDate) OR OverlapEndDate IS NULL) 
    )
    SELECT SLID,planid,monthstartdate,StartDate,EndDate,OverlapstartDate,OverlapEndDate,OLPlanID 
    FROM CTE WHERE RNum = 1
    UNION
    SELECT SLID,planid,monthstartdate,StartDate,EndDate,OverlapstartDate,OverlapEndDate,OLPlanID
    FROM #target
    WHERE (monthstartdate BETWEEN OverlapstartDate AND OverlapEndDate) AND EndDate <> OverlapEndDate
    

    Output:

    User's image

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP
    2023-02-26T22:25:31.4666667+00:00

    I will have to admit that there is much here I don't understand. Specifically, I don't know anything about the actual business, so for me these SLID, planid, OverlapStart etc is just mumbo-jumbo. But it could certainly help if you explain.
    It also unclear to me that is the expected output is given the sample in #target. Is that the grid with five rows in the image?

    That result can be produced with this query:

      SELECT SLID, planid, MIN(StartDate), MAX(EndDate)
      FROM   #target
      GROUP  BY SLID, planid
    

    But maybe that is overly simple-minded?

    0 comments No comments