Solved

Need elegant T-SQL solution

Posted on 2014-07-28
7
345 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

861 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