SQL loop while if then break-CodePudding

Below is the sample data:

Create table and insert statements:

 create table customer_shipping(Customer_ID int, Ship_date date, Supply_date date);
 insert into customer_shipping values(76868773  ,'10/15/2018 0:00','12/10/2018 0:00');
 insert into customer_shipping values(76868773  ,'12/06/2018 0:00','01/31/2019 0:00');
 insert into customer_shipping values(76868773  ,'02/21/2019 0:00','04/18/2019 0:00');
 insert into customer_shipping values(76868773  ,'04/25/2019 0:00','06/20/2019 0:00');
 insert into customer_shipping values(76868773  ,'06/27/2019 0:00','08/22/2019 0:00');
 insert into customer_shipping values(76868773  ,'08/29/2019 0:00','4/24/2019 0:00');
 insert into customer_shipping values(76868773  ,'11/26/2019 0:00','01/21/2020 0:00');
 insert into customer_shipping values(76868773  ,'01/30/2020 0:00','03/26/2020 0:00');
 insert into customer_shipping values(76868773  ,'04/06/2020 0:00','06/01/2020 0:00');
 insert into customer_shipping values(76868773  ,'06/01/2020 0:00','07/27/2020 0:00');
 insert into customer_shipping values(76868773  ,'07/29/2020 0:00','09/23/2020 0:00');

We have different Customer IDs, for which we have different ship date and supply date.. we have used the Lead function to calculate the Gap where we are subtracting the previous value of the supplydate from the next value of the shipdate.

Now are requirement is to get the details of the above customerid till when the gap is not greater than 180 days.. in the above sample example we have the Gap as 216 days (row 6), so the required data is till row 5.. and after that no row should be considered even if the gap is less than 180 days..

Expected result should be:

I am using SQL server.

Need help from all of you to get the idea how we can get the required data.

CodePudding user response：

Add RowNumber column (RN), find the first RN where gap is greater then 180, and just select those where RN is smaller.

WITH CTE_Gaps AS 
(
  SELECT *
  , DATEDIFF(DAY,supply_date, LEAD(ship_date) OVER (PARTITION BY Customer_id ORDER BY ship_date)) AS Gap 
  , ROW_NUMBER() OVER (PARTITION BY Customer_id ORDER BY ship_date) AS Rn
  FROM customer_shipping
)
SELECT *
FROM CTE_Gaps
WHERE Rn < (SELECT Min(Rn) FROM CTE_Gaps WHERE Gap>180)

EDIT: If you want to select all when there are no large gaps, add COALESCE to protect yourself where min(RN) is NULL

WHERE Rn < COALESCE((SELECT Min(Rn) FROM CTE_Gaps WHERE Gap>500), Rn 1)

CodePudding user response：

You can use a CTE or derived table for your current query and use a correlated sub-query to filter based on the dates prior to the earliest that exceeds the gap:

with gaps as (
  select *, 
    DateDiff(day,supply_date,lead(ship_date)
      over(partition by customer_id order by ship_date)) Gap
  from customer_shipping
)
select * 
from gaps g where ship_date < IsNull((
  select Min(ship_date) from gaps g2
  where g2.gap >=180 and g2.Customer_ID = g.Customer_ID
), '20500101'); /* failsafe if there's no gap */