June 7, 2015 at 1:00 am
Hi Guys,
I'm working with a schedule report where the data given is as follows however the required results are very tricky.
A day based schedule is given with a STARTDATE, STARTTIME, ENDDATE, ENDTIME and a DURATION, when it's REQCODE 11 alone for a given day then all is good however when there is also a REQCODE 13 for the same day then that's where the problem comes in.
So each record would have a REQCODE of 11 or 13, if it's 11 then it's straight forward schedule however when it's 13 then I need to look at record 11 of that same day as 13 and then use the STARTIME from REQCODE 13 for the ENDTIME in REQCODE 11, then for REQCODE 13 use it's STARTIME as the STARTTIME however use the ENDTIME from REQCODE 11 as the ENDTIME for REQCODE 13.
Is this possible?
Thank you all in advance..
SAMPLE DATA
WITH SampleData (REQCODE, PERSON, STARTDATE, ENDDATE, STARTTIME, ENDTIME, DURATION) AS
(
SELECT '11','1234','07/19/2015','07/19/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','1234','07/22/2015','07/22/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','1234','07/25/2015','07/25/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','1234','07/29/2015','07/29/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','5678','07/22/2015','07/22/2015', '07:00', '19:00', '12:00'
UNION ALL SELECT '13','5678','07/22/2015','07/22/2015', '17:30', '00:00', '00:00'
UNION ALL SELECT '11','91011','07/26/2015','07/26/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','91011','07/27/2015','07/27/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','91011','07/28/2015','07/28/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '13','91011','07/28/2015','07/28/2015', '14:30', '00:00', '00:00'
)
SELECT *
FROM SampleData;
CURRENT RESULTS
REQCODE PERSON STARTDATE ENDDATE STARTTIME ENDTIME DURATION
11123407/19/2015 07/19/201507:00 15:30 08:30
11123407/22/201507/22/201507:00 15:30 08:30
11123407/25/201507/25/201507:00 15:30 08:30
11123407/29/201507/29/201507:00 15:30 08:30
11567807/22/201507/22/201507:00 19:00 12:00
13567807/22/201507/22/201517:30 00:00 00:00
119101107/26/201507/26/201507:00 15:30 08:30
119101107/27/201507/27/201507:00 15:30 08:30
119101107/28/201507/28/201507:00 15:30 08:30
139101107/28/201507/28/201514:30 00:00 00:00
DESIRED RESULTS
REQCODE PERSON STARTDATE ENDDATE STARTTIME ENDTIME DURATION
11123407/19/2015 07/19/201507:00 15:30 08:30
11123407/22/201507/22/201507:00 15:30 08:30
11123407/25/201507/25/201507:00 15:30 08:30
11123407/29/201507/29/201507:00 15:30 08:30
11567807/22/201507/22/201507:00 17:30 10:30
13567807/22/201507/22/201517:30 19:00 01:30
119101107/26/201507/26/201507:00 15:30 08:30
119101107/27/201507/27/201507:00 15:30 08:30
119101107/28/201507/28/201507:00 14:30 07:30
139101107/28/201507/28/201514:30 15:30 01:00
June 7, 2015 at 4:16 am
Quick solution, works with the sample data
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH SampleData (REQCODE, PERSON, STARTDATE, ENDDATE, STARTTIME, ENDTIME, DURATION) AS
(
SELECT '11','1234','07/19/2015','07/19/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','1234','07/22/2015','07/22/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','1234','07/25/2015','07/25/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','1234','07/29/2015','07/29/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','5678','07/22/2015','07/22/2015', '07:00', '19:00', '12:00'
UNION ALL SELECT '13','5678','07/22/2015','07/22/2015', '17:30', '00:00', '00:00'
UNION ALL SELECT '11','91011','07/26/2015','07/26/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','91011','07/27/2015','07/27/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','91011','07/28/2015','07/28/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '13','91011','07/28/2015','07/28/2015', '14:30', '00:00', '00:00'
)
,TIME_TWISTED AS
(
SELECT
SD.REQCODE
,SD.PERSON
,SD.STARTDATE
,SD.ENDDATE
,SD.STARTTIME
,CASE WHEN REQCODE = 11 AND COUNT(*) OVER
(
PARTITION BY SD.PERSON
,SD.STARTDATE
) = 2 THEN MAX(SD.STARTTIME) OVER
(
PARTITION BY SD.PERSON
,SD.STARTDATE
)
WHEN REQCODE = 11 THEN SD.ENDTIME
ELSE MAX(SD.ENDTIME) OVER
(
PARTITION BY SD.PERSON
,SD.STARTDATE
)
END AS ENDTIME
FROM SampleData SD
)
SELECT
TT.REQCODE
,TT.PERSON
,TT.STARTDATE
,TT.ENDDATE
,TT.STARTTIME
,TT.ENDTIME
,DATEADD(MINUTE, DATEDIFF(MINUTE,TT.STARTTIME,TT.ENDTIME), CONVERT(TIME(0),'00:00:00',0)) AS DURATION
FROM TIME_TWISTED TT
ORDER BY TT.PERSON
,TT.STARTDATE
,TT.REQCODE;
Results
REQCODE PERSON STARTDATE ENDDATE STARTTIME ENDTIME DURATION
------- ------ ---------- ---------- --------- ------- ----------------
11 1234 07/19/2015 07/19/2015 07:00 15:30 08:30:00
11 1234 07/22/2015 07/22/2015 07:00 15:30 08:30:00
11 1234 07/25/2015 07/25/2015 07:00 15:30 08:30:00
11 1234 07/29/2015 07/29/2015 07:00 15:30 08:30:00
11 5678 07/22/2015 07/22/2015 07:00 17:30 10:30:00
13 5678 07/22/2015 07/22/2015 17:30 19:00 01:30:00
11 91011 07/26/2015 07/26/2015 07:00 15:30 08:30:00
11 91011 07/27/2015 07/27/2015 07:00 15:30 08:30:00
11 91011 07/28/2015 07/28/2015 07:00 14:30 07:30:00
13 91011 07/28/2015 07/28/2015 14:30 15:30 01:00:00
June 7, 2015 at 6:32 pm
Yes, this is it.
Thank you so much sir !!
You are a life saver.. God bless..
June 9, 2015 at 9:32 am
DiabloZA (6/7/2015)
Yes, this is it.Thank you so much sir !!
You are a life saver.. God bless..
You may want to look at performance. With small amounts of data, the solution presented will be fine. However, with truly large data, there could be performance issues. Also, and potentially troublesome, is that there's no accommodation of an end date that differs from the start date. This may not be an issue, but the performance on large record quantities could be. Take a look at the following query:
DECLARE @END_DT AS datetime2;
DECLARE @START_DT AS datetime2;
SET @START_DT = GETDATE();
WITH SampleData (REQCODE, PERSON, STARTDATE, ENDDATE, STARTTIME, ENDTIME, DURATION) AS (
SELECT '11','1234','07/19/2015','07/19/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','1234','07/22/2015','07/22/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','1234','07/25/2015','07/25/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','1234','07/29/2015','07/29/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','5678','07/22/2015','07/22/2015', '07:00', '19:00', '12:00'
UNION ALL SELECT '13','5678','07/22/2015','07/22/2015', '17:30', '00:00', '00:00'
UNION ALL SELECT '11','91011','07/26/2015','07/26/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','91011','07/27/2015','07/27/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','91011','07/28/2015','07/28/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '13','91011','07/28/2015','07/28/2015', '14:30', '00:00', '00:00'
),
ADJUSTED_TIMES AS (
SELECT SD.REQCODE, SD.PERSON, SD.STARTDATE,
CASE
WHEN SD.REQCODE = '11' AND SD13.PERSON IS NULL THEN SD.ENDDATE
WHEN SD.REQCODE = '11' AND SD13.PERSON IS NOT NULL THEN SD13.STARTDATE
WHEN SD.REQCODE = '13' AND SD11.PERSON IS NOT NULL THEN SD11.ENDDATE
WHEN SD.REQCODE = '13' AND SD11.PERSON IS NULL THEN NULL
END AS ENDDATE,
SD.STARTTIME,
CASE
WHEN SD.REQCODE = '11' AND SD13.PERSON IS NULL THEN SD.ENDTIME
WHEN SD.REQCODE = '11' AND SD13.PERSON IS NOT NULL THEN SD13.STARTTIME
WHEN SD.REQCODE = '13' AND SD11.PERSON IS NOT NULL THEN SD11.ENDTIME
WHEN SD.REQCODE = '13' AND SD11.PERSON IS NULL THEN NULL
END AS ENDTIME
FROM SampleData AS SD
LEFT OUTER JOIN SampleData AS SD13
ON SD.PERSON = SD13.PERSON
AND SD.STARTDATE = SD13.STARTDATE
AND SD.REQCODE = '11'
AND SD13.REQCODE = '13'
LEFT OUTER JOIN SampleData AS SD11
ON SD.PERSON = SD11.PERSON
AND SD.STARTDATE = SD11.STARTDATE
AND SD.REQCODE = '13'
AND SD11.REQCODE = '11'
)
SELECT AT.*,
DATEADD(mi, DATEDIFF(mi, CAST(AT.STARTDATE + ' ' + AT.STARTTIME + ':00' AS datetime), CAST(AT.ENDDATE + ' ' + AT.ENDTIME + ':00' AS datetime)), CONVERT(TIME(0),'00:00:00',0)) AS DURATION
FROM ADJUSTED_TIMES AS AT;
SET @END_DT = GETDATE();
SELECT DATEDIFF(ns, @START_DT, @END_DT) AS NANO_DURATION;
SET @START_DT = GETDATE();
;WITH SampleData (REQCODE, PERSON, STARTDATE, ENDDATE, STARTTIME, ENDTIME, DURATION) AS
(
SELECT '11','1234','07/19/2015','07/19/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','1234','07/22/2015','07/22/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','1234','07/25/2015','07/25/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','1234','07/29/2015','07/29/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','5678','07/22/2015','07/22/2015', '07:00', '19:00', '12:00'
UNION ALL SELECT '13','5678','07/22/2015','07/22/2015', '17:30', '00:00', '00:00'
UNION ALL SELECT '11','91011','07/26/2015','07/26/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','91011','07/27/2015','07/27/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '11','91011','07/28/2015','07/28/2015', '07:00', '15:30', '08:30'
UNION ALL SELECT '13','91011','07/28/2015','07/28/2015', '14:30', '00:00', '00:00'
)
,TIME_TWISTED AS
(
SELECT
SD.REQCODE
,SD.PERSON
,SD.STARTDATE
,SD.ENDDATE
,SD.STARTTIME
,CASE WHEN REQCODE = 11 AND COUNT(*) OVER
(
PARTITION BY SD.PERSON
,SD.STARTDATE
) = 2 THEN MAX(SD.STARTTIME) OVER
(
PARTITION BY SD.PERSON
,SD.STARTDATE
)
WHEN REQCODE = 11 THEN SD.ENDTIME
ELSE MAX(SD.ENDTIME) OVER
(
PARTITION BY SD.PERSON
,SD.STARTDATE
)
END AS ENDTIME
FROM SampleData SD
)
SELECT
TT.REQCODE
,TT.PERSON
,TT.STARTDATE
,TT.ENDDATE
,TT.STARTTIME
,TT.ENDTIME
,DATEADD(MINUTE, DATEDIFF(MINUTE,TT.STARTTIME,TT.ENDTIME), CONVERT(TIME(0),'00:00:00',0)) AS DURATION
FROM TIME_TWISTED TT
ORDER BY TT.PERSON
,TT.STARTDATE
,TT.REQCODE;
SET @END_DT = GETDATE();
SELECT DATEDIFF(ns, @START_DT, @END_DT) AS NANO_DURATION;
Now look at the execution plan comparison between the two, as shown in the attached JPG file.
There's a lot going on in the second query that could add up big time with larger data sets.
The result set from the first query is identical to the one in the 2nd, and the 2nd is the one posted
by Erikur. FYI...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 9, 2015 at 10:48 am
sgmunson (6/9/2015)
DiabloZA (6/7/2015)
Yes, this is it.Thank you so much sir !!
You are a life saver.. God bless..
You may want to look at performance. With small amounts of data, the solution presented will be fine. However, with truly large data, there could be performance issues. Also, and potentially troublesome, is that there's no accommodation of an end date that differs from the start date. This may not be an issue, but the performance on large record quantities could be.
Did think of that but decided to leave it as the majority of the cost is mitigatable with a single index with the right sort order, the most expensive operation is the sort of the initial table scan.
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply