Post reply

Rearrange Result of Data with TSQL Script

  • November 20, 2013 at 6:20 am

    #1667767

    Output should be like that as I mentioned ...

    So that we know between what dates , the bus was used ..

  • November 20, 2013 at 6:43 am

    #1667780

    sunil.mvs (11/20/2013)

    Output should be like that as I mentioned ...

    So that we know between what dates , the bus was used ..

    Looks like a typical gaps and islands problems.

    You put all of the occurences of one bus in a sequence and you search for the gaps (the dates the bus was not used).

    If you google for this, you might find a few solutions.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • November 20, 2013 at 6:58 am

    #1667788

    Here's a simple islands'n'gaps solution as suggested by Koen:

    DROP table #temp

    CREATE table #temp (SysKey int ,BusName varchar(9) ,BusType varchar(6) ,StartDateKey int ,EndDatekey int )

    Insert into #temp (SysKey ,BusName,BusType,StartDateKey,EndDatekey)

    Select 1 , 'A' ,'AC',20130101,20130112 union ALL

    Select 2 , 'A' ,'AC', 20130113 ,20130115 union ALL

    Select 3 , 'A' ,'AC', 20130116 ,20130118 Union ALL

    Select 4 , 'B' , 'NON-AC', 20130119 ,20130121 union ALL

    Select 5 , 'B' , 'NON-AC', 20130122 ,20130124 Union ALL

    Select 6 , 'A' ,'AC', 20130125, NULL

    ;WITH SequencedData AS (

    SELECT

    SysKey, BusName, BusType, StartDateKey, EndDatekey,

    seq = SysKey - ROW_NUMBER() OVER(PARTITION BY BusName ORDER BY StartDateKey)

    FROM #temp

    )

    SELECT

    SysKey = MAX(SysKey),

    BusName, BusType,

    StartDate = MIN(StartDateKey),

    EndDate = MAX(EndDatekey)

    FROM SequencedData

    GROUP BY BusName, BusType, seq

    ORDER BY MAX(SysKey)

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • November 20, 2013 at 7:25 am

    #1667808

    Thanks Chris ...

    The solution is as expected.. but need to check performance .

    Thanks

    Surya Sunil

  • November 20, 2013 at 7:27 am

    #1667811

    sunil.mvs (11/20/2013)

    Thanks Chris ...

    The solution is as expected.. but need to check performance .

    Thanks

    Surya Sunil

    You should also make sure you understand how it is working before you put it in production. Remember, you are the one who has to support it if something goes sideways.

  • November 21, 2013 at 6:27 am

    #1668092

    Lynn Pettis (11/20/2013)

    sunil.mvs (11/20/2013)

    Thanks Chris ...

    The solution is as expected.. but need to check performance .

    Thanks

    Surya Sunil

    You should also make sure you understand how it is working before you put it in production. Remember, you are the one who has to support it if something goes sideways.

    Important point. Google "islands and gaps" and pick from the reputable sites, or try these:

    https://www.simple-talk.com/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/[/url]

    http://qa.sqlservercentral.com/articles/T-SQL/71550/[/url]

    http://technet.microsoft.com/en-us/library/aa175780(v=sql.80).aspx

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply