For loop in Microsoft SQL Server

Question

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;

Please do not upload images of code/data/errors when asking a question. — Thom A
– Thom A ♦, Commented Jun 6, 2022 at 16:31
The language/dialect you are writing here is T-SQL, and the RDBMS is SQL Server not SQL Server Management Studio (SSMS). SSMS is just an IDE like application specifically for SQL Server; SSMS actually has nothing to do with the problem other than it's the application you are writing the code in (like Visual Studio) In truth, however, looping is one of the last things you want to do in T-SQL; SQL is a set based language and so you want to use set based methods. Iterative methods, like a loop, perform terribly in SQL. — Thom A
– Thom A ♦, Commented Jun 6, 2022 at 16:33
use the lead / lag functions to get the previous date learn.microsoft.com/en-us/sql/t-sql/functions/… — Bryan Dellinger
– Bryan Dellinger, Commented Jun 6, 2022 at 16:35
Help us understand the desired output. I can't find any logical explanation from the data or your description about what values are returned for new_end_date. — Sean Lange
– Sean Lange, Commented Jun 6, 2022 at 16:40
@SeanLange If I understand the OP's question correctly, they are looking to truncate overlapping contract periods so that the prior contracts for an equipment ID always ends at least one day before the new contract begins. — RBarryYoung
– RBarryYoung, Commented Jun 6, 2022 at 17:00

Sean Lange · Accepted Answer · 2022-06-06 20:01:50Z

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

I can't find words to thank you.. This is a clear explanation. That's true, the day before 4/1 is 3/31, it was just an error from me. Thank you so much again , one more function learned today :)

Collectives™ on Stack Overflow

For loop in Microsoft SQL Server

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related