Solved

Need elegant T-SQL solution

Posted on 2014-07-28
7
351 Views
Last Modified: 2014-07-31
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
Comment
Question by:FamousMortimer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 1

Expert Comment

by:Pooja Katiyar Verma
ID: 40224990
Hello

Can you provide the input data you have. and exact output you want.
0
 
LVL 10

Author Comment

by:FamousMortimer
ID: 40225001
The input data is the second table above, and the desired output is the first.
0
 
LVL 14

Assisted Solution

by:Thandava Vallepalli
Thandava Vallepalli earned 125 total points
ID: 40225156
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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 125 total points
ID: 40227292
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
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 250 total points
ID: 40228699
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
 
LVL 10

Author Comment

by:FamousMortimer
ID: 40229009
Well done, thank you all
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40231677
Welcome.  Good luck on future qs as well.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question