SQL 2008 Query Help

Question

SQL 2008 Query Help

Douglasjbell

SSC Veteran

Points: 289
More actions
November 8, 2012 at 8:57 am

#266832

Hi All
Hope you can help. I have been asked to created a report based on an SQL 2008 Table. The table basically is just a list of options with a start and finish time.
For example
Option Start Finish
Option1 07:30:00 07:35:00
Option2 07:35:00 07:50:00
Option3 08:10:00 08:20:00
Option1 08:30:00 08:35:00
Option3 08:35:00 09:00:00
My report will run from an SQL Query but if you look at the example above you will notice gaps in the times, so for the report they want I have to get my list to look like the following.
Option Start Finish
Option1 07:30:00 07:35:00
Option2 07:35:00 07:50:00
Unspec 07:50:00 08:10:00
Option3 08:10:00 08:20:00
Unspec 08:20:00 08:30:00
Option1 08:30:00 08:35:00
Option3 08:35:00 09:00:00
I am new to SQL quiries so not sure how about getting the above so looking for a little help.
Cheers
DJ

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

The problem you are facing is known as "gaps and islands". In your case you are specifically looking for gaps. If you search this site you will find several articles discussing this challenge. If you find that you need more detailed assistance please read the first article in my signature about best practices when posting questions and we can help you the code.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Mark Cowne One Orange Chip Points: 26953 More actions · Answer 2

Try this

DECLARE @myTable TABLE([Option] VARCHAR(10), Start TIME, Finish TIME)

INSERT INTO @myTable([Option], Start, Finish)

VALUES('Option1', '07:30:00' ,'07:35:00'),

('Option2', '07:35:00' ,'07:50:00'),

('Option3', '08:10:00' ,'08:20:00'),

('Option1', '08:30:00' ,'08:35:00'),

('Option3', '08:35:00' ,'09:00:00');

WITH CTE AS (

SELECT [Option], Start, Finish,

ROW_NUMBER() OVER(ORDER BY Start) AS rn

FROM @myTable)

SELECT [Option], Start, Finish

FROM @myTable

UNION ALL

SELECT 'Unspec',t1.Finish,t2.Start

FROM CTE t1

INNER JOIN CTE t2 ON t2.rn = t1.rn+1

AND t2.Start <>t1.Finish

ORDER BY Start;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Douglasjbell SSC Veteran Points: 289 More actions · Answer 3

@mark-3

All I can say is AWESOME. Worked the code in with my query and it works without a hitch.

Thankyou

DJ