Solved

Need elegant T-SQL solution

Posted on 2014-07-28
7
337 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
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher 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:ScottPletcher
ID: 40231677
Welcome.  Good luck on future qs as well.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now