Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

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

0
FamousMortimer
Asked:
FamousMortimer
3 Solutions
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
 
FamousMortimerAuthor Commented:
Well done, thank you all
0
 
Scott PletcherSenior DBACommented:
Welcome.  Good luck on future qs as well.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now