Rearrange Result of Data with TSQL Script

Question

Post reply

Rearrange Result of Data with TSQL Script

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

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

sunil.mvs Ten Centuries Points: 1031 More actions · Answer 1

Output should be like that as I mentioned ...

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

sunil.mvs Ten Centuries Points: 1031 More actions · Answer 4

Thanks Chris ...

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

Thanks

Surya Sunil

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

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://www.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