Need elegant T-SQL solution

I am looking for a somewhat elegant solution to my problem if possible.  Looking at the example table below, I need the records whose ShiftStartTime > ShiftEndTime to be split in two records to the time will account for a fill 24 hours.

Take StartDayOfWeek = 2 for example.  I would like the result to return 4 records for that DoW...
StartDayOfWeek	ShiftStartTime	ShiftEndTime	ShiftLengthInHours
2		00:00:00	06:00:00	6
2		06:00:00	14:00:00	8
2		14:00:00	22:00:00	8
2		22:00:00	24:00:00	2

Open in new window

Actually, I don't even need shift length in the result...
StartDayOfWeek	ShiftStartTime	ShiftEndTime	ShiftLengthInHours
2		06:00:00	14:00:00	8.0000
2		14:00:00	22:00:00	8.0000
2		22:00:00	06:00:00	8.0000
3		06:00:00	14:00:00	8.0000
3		14:00:00	22:00:00	8.0000
3		22:00:00	06:00:00	8.0000
4		06:00:00	14:00:00	8.0000
4		14:00:00	22:00:00	8.0000
4		22:00:00	06:00:00	8.0000
5		06:00:00	14:00:00	8.0000
5		14:00:00	22:00:00	8.0000
5		22:00:00	06:00:00	8.0000
6		06:00:00	14:00:00	8.0000
6		14:00:00	22:00:00	8.0000
6		22:00:00	06:00:00	8.0000
1		22:00:00	06:00:00	8.0000
7		06:00:00	14:00:00	8.0000
7		14:00:00	22:00:00	8.0000

Open in new window

LVL 10
FamousMortimerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pooja Katiyar VermaCommented:
Hello

Can you provide the input data you have. and exact output you want.
0
FamousMortimerAuthor Commented:
The input data is the second table above, and the desired output is the first.
0
Thandava VallepalliCommented:
try this one....

================

select StartDayOfWeek,      ShiftStartTime,      ShiftEndTime
from timeslot
where ShiftStartTime < ShiftEndTime
union all
select StartDayOfWeek,      ShiftStartTime,      cast('23:59:59' as time)
from timeslot
where ShiftStartTime > ShiftEndTime
union all
select StartDayOfWeek,      cast('00:00:00' as time),      ShiftEndTime
from timeslot
where ShiftStartTime > ShiftEndTime
order by 1, 2, 3

====================

Here is the output of above query

=============================

StartDayOfWeek ShiftStartTime   ShiftEndTime
-------------- ---------------- ----------------
1              00:00:00.0000000 06:00:00.0000000
1              22:00:00.0000000 23:59:59.0000000
2              00:00:00.0000000 06:00:00.0000000
2              06:00:00.0000000 14:00:00.0000000
2              14:00:00.0000000 22:00:00.0000000
2              22:00:00.0000000 23:59:59.0000000
3              00:00:00.0000000 06:00:00.0000000
3              06:00:00.0000000 14:00:00.0000000
3              14:00:00.0000000 22:00:00.0000000
3              22:00:00.0000000 23:59:59.0000000
4              00:00:00.0000000 06:00:00.0000000
4              06:00:00.0000000 14:00:00.0000000
4              14:00:00.0000000 22:00:00.0000000
4              22:00:00.0000000 23:59:59.0000000
5              00:00:00.0000000 06:00:00.0000000
5              06:00:00.0000000 14:00:00.0000000
5              14:00:00.0000000 22:00:00.0000000
5              22:00:00.0000000 23:59:59.0000000
6              00:00:00.0000000 06:00:00.0000000
6              06:00:00.0000000 14:00:00.0000000
6              14:00:00.0000000 22:00:00.0000000
6              22:00:00.0000000 23:59:59.0000000
7              06:00:00.0000000 14:00:00.0000000
7              14:00:00.0000000 22:00:00.0000000

=============================


--itsvtk
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
Here's the main code; full test is below.
Btw, it took me much longer to code the create table and INSERTs for the test data below than it did to write the final query.  It would be so much easier if you presented data as INSERTs and not just raw text.


SELECT
    s.StartDayOfWeek,
    CASE WHEN s.ShiftStartTime > s.ShiftEndTime AND cj1.WhichSplit = 'Split1' THEN '00:00:00' ELSE s.ShiftStartTime END
        AS ShiftStartTime,
    CASE WHEN s.ShiftStartTime > s.ShiftEndTime AND cj1.WhichSplit = 'Split2' THEN '23:59:59' ELSE s.ShiftEndTime END
        AS ShiftEndTime
FROM #shifts s
INNER JOIN (
    SELECT 'Split1' AS WhichSplit UNION ALL
    SELECT 'Split2'
) AS cj1 ON
    cj1.WhichSplit = 'Split1' OR
    s.ShiftStartTime > s.ShiftEndTime
ORDER BY
    StartDayOfWeek, ShiftStartTime


USE tempdb

IF OBJECT_ID('tempdb..#shifts') IS NOT NULL
    DROP TABLE #shifts
CREATE TABLE #shifts (
    StartDayOfWeek tinyint,
    ShiftStartTime time,
    ShiftEndTime time
    )
SET NOCOUNT ON
INSERT INTO #shifts
SELECT 1,            '22:00:00',      '06:00:00' UNION ALL      --8.0000
SELECT 2,            '06:00:00',      '14:00:00' UNION ALL      --8.0000
SELECT 2,            '14:00:00',      '22:00:00' UNION ALL      --8.0000
SELECT 2,            '22:00:00',      '06:00:00' UNION ALL      --8.0000
SELECT 3,            '06:00:00',      '14:00:00' UNION ALL      --8.0000
SELECT 3,            '14:00:00',      '22:00:00' UNION ALL      --8.0000
SELECT 3,            '22:00:00',      '06:00:00' UNION ALL      --8.0000
SELECT 4,            '06:00:00',      '14:00:00' UNION ALL      --8.0000
SELECT 4,            '14:00:00',      '22:00:00' UNION ALL      --8.0000
SELECT 4,            '22:00:00',      '06:00:00' UNION ALL      --8.0000
SELECT 5,            '06:00:00',      '14:00:00' UNION ALL      --8.0000
SELECT 5,            '14:00:00',      '22:00:00' UNION ALL      --8.0000
SELECT 5,            '22:00:00',      '06:00:00' UNION ALL      --8.0000
SELECT 6,            '06:00:00',      '14:00:00' UNION ALL      --8.0000
SELECT 6,            '14:00:00',      '22:00:00' UNION ALL      --8.0000
SELECT 6,            '22:00:00',      '06:00:00' UNION ALL      --8.0000
SELECT 7,            '06:00:00',      '14:00:00' UNION ALL      --8.0000
SELECT 7,            '14:00:00',      '22:00:00'      --8.0000
SET NOCOUNT OFF

SELECT
    s.StartDayOfWeek,
    CASE WHEN s.ShiftStartTime > s.ShiftEndTime AND cj1.WhichSplit = 'Split1' THEN '00:00:00' ELSE s.ShiftStartTime END
        AS ShiftStartTime,
    CASE WHEN s.ShiftStartTime > s.ShiftEndTime AND cj1.WhichSplit = 'Split2' THEN '23:59:59' ELSE s.ShiftEndTime END
        AS ShiftEndTime
FROM #shifts s
INNER JOIN (
    SELECT 'Split1' AS WhichSplit UNION ALL
    SELECT 'Split2'
) AS cj1 ON
    cj1.WhichSplit = 'Split1' OR
    s.ShiftStartTime > s.ShiftEndTime
ORDER BY
    StartDayOfWeek, ShiftStartTime
0
Olaf DoschkeSoftware DeveloperCommented:
Scott,

almost, but your sample record for day 1 from 22:00 to 06:00 of the follwing day 2 is split into two day 1 records, that's wrong. It should be split into one record for day 1 with 22:00 to 24:00 (or 23:59:59) and 1 record from 0:00 to 06:00 of day 2, not day 1.

The whole thing would be much easier to do, if it were datetimes. But aside of that you can use Scotts elegant split method in this modified way:

Declare @shifts as Table (
    StartDayOfWeek tinyint,
    ShiftStartTime time,
    ShiftEndTime time
    )
SET NOCOUNT ON
INSERT INTO @shifts values
   (1,            '22:00:00',      '06:00:00'),
   (2,            '06:00:00',      '14:00:00'),
   (2,            '14:00:00',      '22:00:00'),
   (2,            '22:00:00',      '06:00:00'),
   (3,            '06:00:00',      '14:00:00'),
   (3,            '14:00:00',      '22:00:00'),
   (3,            '22:00:00',      '06:00:00'),
   (4,            '06:00:00',      '14:00:00'),
   (4,            '14:00:00',      '22:00:00'),
   (4,            '22:00:00',      '06:00:00'),
   (5,            '06:00:00',      '14:00:00'), 
   (5,            '14:00:00',      '22:00:00'),
   (5,            '22:00:00',      '06:00:00'),
   (6,            '06:00:00',      '14:00:00'),
   (6,            '14:00:00',      '22:00:00'),
   (6,            '22:00:00',      '06:00:00'),
   (7,            '06:00:00',      '14:00:00'),
   (7,            '14:00:00',      '22:00:00')
SET NOCOUNT OFF

SELECT 
    CASE cj1.WhichSplit WHEN 'Split2' THEN s.StartDayOfWeek+1 ELSE s.StartDayOfWeek END AS StartDayOfWeek,
    CASE cj1.WhichSplit WHEN 'Split2' THEN '00:00:00'         ELSE s.ShiftStartTime END AS ShiftStartTime,
    CASE cj1.WhichSplit WHEN 'Split1' THEN '23:59:59.9999999' ELSE s.ShiftEndTime   END AS ShiftEndTime
FROM @shifts s
LEFT JOIN (
    SELECT 'Split1' AS WhichSplit UNION ALL
    SELECT 'Split2'
) AS cj1 ON  s.ShiftStartTime > s.ShiftEndTime
ORDER BY 
    StartDayOfWeek, ShiftStartTim

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FamousMortimerAuthor Commented:
Well done, thank you all
0
Scott PletcherSenior DBACommented:
Welcome.  Good luck on future qs as well.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.