nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Child Item

Tsql question

c07

Ok, I've never posted in the tech forum before but I have an issue I'm trying to solve and it's basically me trying to find logic that will work in tsql that will give me a particular output.
Hopefully, someone in here can help.
So, here is the type of data I would have:

Code/number Status. Date
1111 A 01/01/2013
1111 A 02/02/2013
1111 B 03/03/2013
1111 A 04/04/2013
1111 A 05/05/2013
1111 B 06/06/2013
1111 A 07/06/2013

What I'm trying to get in my output is, the code number and status and the original date of the 'A' status that the code /number first occurs, or if status is 'B' it should take the original date of A status preceding it. If the A status is preceded by a B, then it should use it's own date.
So, using data above, I'll add the expected output date beside it in the hopes it makes my query clear.

Code/number status. Date. Output
1111 A 01/01/2013 01/01/2013
1111 A 02/02/2013 01/01/2013
1111 B 03/03/2013 01/01/2013
1111 A 04/04/2013 04/04/2013
1111 A 05/05/2013 04/04/2013
1111 B 06/06/2013 04/04/2013
1111 A 07/07/2013 07/07/2013

Hope that makes sense. And if anyone can solve my problem, I owe ye big time as it's been a thorn in my side for weeks now!

Cheers

Find more posts tagged with

logic

tsql

Quick Links

Comments

counterpointaud

Try this (probably not suitable for large data sets or where performance is important):

DECLARE @CurrentCode as NVARCHAR(50);
DECLARE @CurrentStatus as NVARCHAR(50);
DECLARE @CurrentDate as DATETIME2;
DECLARE @PreviousCode as NVARCHAR(50);
DECLARE @PreviousStatus as NVARCHAR(50);
DECLARE @PreviousDate as DATETIME2;
DECLARE @DateOutput as DATETIME2;
DECLARE @Cursor as CURSOR;
 
SET @Cursor = CURSOR FOR
select code, status, date from [table] order by date 
 
OPEN @Cursor;
FETCH NEXT FROM @Cursor INTO @CurrentCode, @CurrentStatus, @CurrentDate;
SET @PreviousDate = @CurrentDate
WHILE @@FETCH_STATUS = 0
BEGIN
 IF @CurrentStatus = @PreviousStatus
 BEGIN
   SET @DateOutput = @PreviousDate   
END  
 ELSE SET @DateOutput = @CurrentDate
 PRINT @CurrentCode  + ' ' + @CurrentStatus + ' ' + cast(@CurrentDate as VARCHAR (50)) + ' ' + cast(@DateOutput as VARCHAR (50));
 IF @CurrentStatus != @PreviousStatus
 BEGIN
   SET @PreviousDate = @CurrentDate
 END
 SET @PreviousStatus = @CurrentStatus
 FETCH NEXT FROM @Cursor INTO @CurrentCode, @CurrentStatus, @CurrentDate;
END
 
CLOSE @Cursor;

NOTE: Change datetime2 to whatever date format you are using

c07

Oh my god... I could kiss you...
Thanks so much. With only the slightest of tweaking ( to account for a scenario not outlined here).. It worked PERFECTLY!! &#55357;&#56397;

counterpointaud

You're welcome