Limit the records based on ranges column in sql server

Question

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.

-

  - **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

Thanks In Advance

Answer 1

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:

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.

Answer 2

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?