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
Hi all! We have been experiencing an issue on site where threads have been missing the latest postings. The platform host Vanilla are working on this issue. A workaround that has been used by some is to navigate back from 1 to 10+ pages to re-sync the thread and this will then show the latest posts. Thanks, Mike.
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Tsql question

  • 21-07-2014 10:53pm
    #1
    c07
    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

  • #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 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