For loop in Microsoft SQL Server-CodePudding

I have a data which has 3 different numbers of "Equipement" and each "Equipement" has different contract date ( start_date and end_date).

Screen Data:

I want to write a script which I can say that for every "Equipement" If the first line of "end_date" match the second line of "start_date" in days, so I should do ("start_date" - 1 day) in the second line AS a new_end_date for the first line.

I've made an attempt, but for just the two first lines ( not generalized):

    SELECT[Ref]
          ,[Equipement]
          ,[start_date]
          ,[end_date]
          ,CASE WHEN DATEDIFF(day, (SELECT [end_date] FROM [DWDiagnostics].[dbo].[Test1] WHERE [Ref] = 1290), (SELECT [start_date] FROM [DWDiagnostics].[dbo].[Test1] WHERE [Ref] = 1380)) < 0 THEN DATEADD(dd, -1, [start_date]) ELSE [end_date]
          END AS [new_end_date]  
          FROM [DWDiagnostics].[dbo].[Test1]

Here's a screen of the result I want

SQL code for the Data ==>

    DECLARE   @Test TABLE
              (
                   Ref VARCHAR(10),
                   Equipment   VARCHAR(10),
                   start_date  DATE,
                   end_date  DATE
                )
    INSERT INTO @Test VALUES ('1290','9999','2014-03-01','2016-04-16')
    INSERT INTO @Test VALUES ('1380','9999','2016-04-01','2018-05-17')
    INSERT INTO @Test VALUES ('2000','9999','2018-05-01','2020-06-27')
    INSERT INTO @Test VALUES ('2900','9999','2020-06-01','2021-06-29')
    INSERT INTO @Test VALUES ('1556','8888','2016-01-01','2017-02-27')
    INSERT INTO @Test VALUES ('1876','8888','2017-02-01','2018-04-26')
    INSERT INTO @Test VALUES ('2897','8888','2018-04-01','2020-03-30')
    INSERT INTO @Test VALUES ('2653','7777','2017-09-01','2018-10-14')
    INSERT INTO @Test VALUES ('4536','7777','2018-10-01','2019-11-13')
    INSERT INTO @Test VALUES ('2987','7777','2019-11-01','2020-12-27')
    INSERT INTO @Test VALUES ('2776','7777','2020-12-01','2021-11-30')
    SELECT * FROM   @Test;

CodePudding user response：

Thanks for posting sample data and tables structures. Makes this so much easier to work on the problem. This should work based on your explanation of the issue. However, some of the new_end_date values you posted as desired do not match up to your description. For example, with Equipment 9999 you have the second start_date as 4/1/2016 but in your desired output you show 3/30. The day before 4/1 is 3/31. There are some other examples with dates like that in your desired output that are slightly off the day before.

DECLARE   @Test TABLE
(
    Ref VARCHAR(10),
    Equipment   VARCHAR(10),
    start_date  DATE,
    end_date  DATE
)
INSERT INTO @Test VALUES ('1290','9999','2014-03-01','2016-04-16')
INSERT INTO @Test VALUES ('1380','9999','2016-04-01','2018-05-17')
INSERT INTO @Test VALUES ('2000','9999','2018-05-01','2020-06-27')
INSERT INTO @Test VALUES ('2900','9999','2020-06-01','2021-06-29')
INSERT INTO @Test VALUES ('1556','8888','2016-01-01','2017-02-27')
INSERT INTO @Test VALUES ('1876','8888','2017-02-01','2018-04-26')
INSERT INTO @Test VALUES ('2897','8888','2018-04-01','2020-03-30')
INSERT INTO @Test VALUES ('2653','7777','2017-09-01','2018-10-14')
INSERT INTO @Test VALUES ('4536','7777','2018-10-01','2019-11-13')
INSERT INTO @Test VALUES ('2987','7777','2019-11-01','2020-12-27')
INSERT INTO @Test VALUES ('2776','7777','2020-12-01','2021-11-30')
    
select *
    , new_end_date  = isnull(dateadd(day, -1, lead(start_date, 1)over(partition by Equipment order by start_date)), end_date)
from @Test
ORDER BY Equipment desc
    , start_date