Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Tsql question

  • 21-07-2014 9:53pm
    #1
    Registered Users 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

  • Registered Users 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


  • Registered Users 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!! ��


  • Registered Users Posts: 403 ✭✭counterpointaud


    You're welcome :)


Advertisement