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

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!! ��

counterpointaud

You're welcome