Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Tsql question

  • 21-07-2014 10:53PM
    #1
    c07
    Registered Users, Registered Users 2 Posts: 226 ✭✭


    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. :confused:

    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
    Tagged:


Comments

  • #2
    counterpointaud
    Registered Users, Registered Users 2 Posts: 403 ✭✭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


  • #3
    c07
    Registered Users, Registered Users 2 Posts: 226 ✭✭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!! ��


  • #4
    counterpointaud
    Registered Users, Registered Users 2 Posts: 403 ✭✭counterpointaud


    You're welcome :)


Advertisement