July 7, 2016 at 2:22 am
Hi I have this sample data
INSERT INTO #dates VALUES ('DA6557','2016-07-01 09:00:00.000','2016-07-03 12:00:00.000')
and I need to create an entry between the two dates if the start and end date is greater then 0
I also have time parameters for the shifts:
DECLARE @MorningShiftStart TIME;
SET @MorningShiftStart = '07:00';
DECLARE @MorningShiftEnd TIME;
SET @MorningShiftEnd = '12:30';
DECLARE @AfterNonShiftStart TIME;
SET @AfterNonShiftStart = '19:00';
DECLARE @AfterNonShiftEnd TIME;
SET @AfterNonShiftEnd = '00:30';
according to my sample data the machine was down from '2016-07-01 09:00:00' to '2016-07-03 12:00:00.000'
and my results should be
Code START END
---- --------------------
DA6557 '2016-07-01 09:00:00' '2016-07-01 12:30:00'
DA6557 '2016-07-01 19:00:00' '2016-07-02 00:30:00'
DA6557 '2016-07-02 07:00:00' '2016-07-02 12:30:00'
DA6557 '2016-07-02 19:00:00' '2016-07-03 00:30:00'
DA6557 '2016-07-03 07:00:00' '2016-07-03 12:00:00'
July 7, 2016 at 4:13 am
This ended up a little complicated, but it works, at least for the date range you provided. I had to mangle some of the keywords to allow me to post them from my company's computer.
CRE ATE TABLE #dates (Code char(6), PeriodStart datetime, PeriodEnd datetime)
INSERT INTO #dates VALUES ('DA6557','2016-07-01 09:00:00.000','2016-07-03 12:00:00.000')
DEC LARE @MorningShiftStart TIME;
SET @MorningShiftStart = '07:00';
DEC LARE @MorningShiftEnd TIME;
SET @MorningShiftEnd = '12:30';
DEC LARE @AfterNonShiftStart TIME;
SET @AfterNonShiftStart = '19:00';
DEC LARE @AfterNonShiftEnd TIME;
SET @AfterNonShiftEnd = '00:30';
WITH Numbers AS ( -- add more numbers here to increase your tolerance for down time
SELECT n FROM (VALUES (0),(1),(2),(3),(4)) AS V(n)
)
, Shifts AS (
SELECT
@MorningShiftStart AS ShiftStart
,@MorningShiftEnd AS ShiftEnd
UNI ON ALL SELECT
@AfterNonShiftStart
,@AfterNonShiftEnd
)
, DayShifted AS (
SELECT
ShiftStart
,ShiftEnd
,CASE
WHEN ShiftStart >= ShiftEnd THEN 1
ELSE 0 -- we will add 1 if the shift ends the next day
END AS DayShifted
FROM Shifts
)
SELECT
d.Code
,CASE -- takes the later of period start time and shift start time
WHEN d.PeriodStart > CAST(CAST(d.PeriodStart AS date) as datetime) + v.n + CAST(f.ShiftStart AS datetime) THEN d.PeriodStart
ELSE CAST(CAST(d.PeriodStart AS date) as datetime) + v.n + CAST(f.ShiftStart AS datetime)
END AS MyStart
,CASE -- takes the earlier of period end time and shift end time
WHEN d.PeriodEnd < CAST(CAST(d.PeriodStart AS date) as datetime) + v.n + CAST(f.ShiftEnd AS datetime) + f.DayShifted THEN d.PeriodEnd
ELSE CAST(CAST(d.PeriodStart AS date) as datetime) + v.n + CAST(f.ShiftEnd AS datetime) + f.DayShifted
END AS MyEnd
FROM #dates d
JOIN Numbers v ON d.PeriodEnd >= d.PeriodStart + v.n
JOIN DayShifted f
ON CASE
WHEN d.PeriodStart > CAST(CAST(d.PeriodStart AS date) as datetime) + v.n + CAST(f.ShiftStart AS datetime) THEN d.PeriodStart
ELSE CAST(CAST(d.PeriodStart AS date) as datetime) + v.n + CAST(f.ShiftStart AS datetime)
END >= d.PeriodStart
AND CASE -- also need to check we haven't gone past the period end
WHEN d.PeriodStart > CAST(CAST(d.PeriodStart AS date) as datetime) + v.n + CAST(f.ShiftStart AS datetime) THEN d.PeriodStart
ELSE CAST(CAST(d.PeriodStart AS date) as datetime) + v.n + CAST(f.ShiftStart AS datetime)
END <= d.PeriodEnd
AND CASE
WHEN d.PeriodEnd < CAST(CAST(d.PeriodStart AS date) as datetime) + v.n + CAST(f.ShiftEnd AS datetime) + f.DayShifted THEN d.PeriodEnd
ELSE CAST(CAST(d.PeriodStart AS date) as datetime) + v.n + CAST(f.ShiftEnd AS datetime) + f.DayShifted
END <= d.PeriodEnd
John
July 7, 2016 at 6:49 am
Hi John
I must say the script it's very complicated. I've managed to test the few and it works.
only if the dates is in the midnight i.e.
INSERT INTO #dates VALUES ('DA6557','2016-06-22 23:15:00.000','2016-06-23 00:30:00.000')
it's creates two entries:
the results should only be: DA65572016-06-22 23:15:00.0002016-06-23 00:30:00.000
otherwise it's working.
thank you so much in advance:
July 7, 2016 at 7:25 am
OK, take the penultimate join predicate and replace it with this:AND CASE -- make sure the end is after the start
WHEN d.PeriodStart > CAST(CAST(d.PeriodStart AS date) as datetime) + v.n + CAST(f.ShiftStart AS datetime) THEN d.PeriodStart
ELSE CAST(CAST(d.PeriodStart AS date) as datetime) + v.n + CAST(f.ShiftStart AS datetime)
END
<CASE
WHEN d.PeriodEnd < CAST(CAST(d.PeriodStart AS date) as datetime) + v.n + CAST(f.ShiftEnd AS datetime) + f.DayShifted THEN d.PeriodEnd
ELSE CAST(CAST(d.PeriodStart AS date) as datetime) + v.n + CAST(f.ShiftEnd AS datetime) + f.DayShifted
END
John
July 7, 2016 at 8:13 am
If you use a Calendar table, you can query the rows in that, join them to your time parameters, and simply do either a join to your date-range table (Cross Apply works really well for that), or do a query of "WHERE Calendar.Date >= @Start AND ..." and so on.
Doing date-ranges of any sort is very, very easy with a Calendar table.
Calendar table is just a table with one row per calendar day. You can add the columns you want, like "Holiday" or "DayOfWeek", pre-calculate these values into it, and get really fast lookups for things like "how many work-days between X date and Y date". Or querying "last Monday in November in 2014", without having to do any complex math - if the table has day-of-week, month, and year, as pre-calculated columns in it.
Put the PK and cluster on the main date column, then index whatever else you use a lot.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 8, 2016 at 5:42 am
you are a genuis
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply