June 24, 2014 at 7:48 am
If I run the following, it returns:
time_sched_start_dttime_sched_end_dt
2014-06-23 10:45:00.0002014-06-23 12:15:00.000
I would expect it to return the first 45 minute available timeslot:
time_sched_start_dttime_sched_end_dt
2014-06-23 09:15:00.0002014-06-23 10:00:00.000
ChrisM@Work (6/24/2014)
DECLARE @duration INT=45
SELECT TOP 1
time_sched_start_dt = MAX(CASE WHEN dir = 'S' THEN dt END),
time_sched_end_dt = MAX(CASE WHEN dir = 'E' THEN dt END)
FROM ( -- f
SELECT dir, dt, Grouper = (ROW_NUMBER() OVER(ORDER BY dt, dir)-1)/2
FROM ( -- e
SELECT dir, dt, ct = COUNT(*) OVER(PARTITION BY dt)
FROM ( -- d
SELECT dir = 'S', dt = time_sched_start_dt
FROM #return_schedule sr
WHERE sr.col_val = ''
UNION ALL
SELECT dir = 'E', dt = time_sched_end_dt
FROM #return_schedule sr
WHERE sr.col_val = ''
) d
) e
WHERE ct = 1
) f
GROUP BY Grouper
HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) > @duration
ORDER BY time_sched_start_dt
June 24, 2014 at 7:50 am
Change
HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) > @duration
to
HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 24, 2014 at 7:55 am
ChrisM@Work (6/24/2014)
ChangeHAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) > @duration
to
HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration
This seems to have worked. I want to thank you for taking the time to look into this and help me out as it was a real challenge for me. I really appreciate it.
June 24, 2014 at 8:17 am
Hello guys,
As rightly pointed out by Chris the query I posted earlier were having some issues.Please see refined one.
DECLARE @duration INT=20;
WITH cte
AS (SELECT TOP 1 time_sched_start_dt,
enddt
FROM (SELECT T.*,
T1.time_sched_start_dt
strtdt,
T1.time_sched_end_dt
enddt,
Datediff(minute, T1.time_sched_start_dt,
T.time_sched_end_dt)
- @duration diff
FROM #return_schedule T1
CROSS apply(SELECT *
FROM #return_schedule T2
WHERE T1.col_val = T2.col_val)T
WHERE Datediff(minute, T.time_sched_start_dt,
T1.time_sched_end_dt)
>=
@duration)T
ORDER BY diff DESC)
SELECT Min(time_sched_start_dt)time_sched_start_dt,
Max(time_sched_end_dt) time_sched_end_dt,
Sum(CONVERT(INT, col_val))
FROM (SELECT *
FROM #return_schedule
WHERE time_sched_start_dt >= (SELECT time_sched_start_dt
FROM cte)
AND time_sched_end_dt <= (SELECT enddt
FROM cte))t
HAVING SUM(CONVERT(INT, col_val)) = 0
Hopefully this one meets all the requirements.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 24, 2014 at 8:27 am
Removed as wrong solution
Far away is close at hand in the images of elsewhere.
Anon.
June 24, 2014 at 8:35 am
ChrisM@Work (6/24/2014)
ChangeHAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) > @duration
to
HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration
Here's more of my actual data. Results don't appear correct unless I am doing something wrong.
create table #return_schedule
(slot_col1 char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('3108337', '2014-06-23 00:00:00.000', '2014-06-23 00:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 00:45:00.000', '2014-06-23 01:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 01:30:00.000', '2014-06-23 02:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 02:15:00.000', '2014-06-23 03:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 03:00:00.000', '2014-06-23 03:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 03:45:00.000', '2014-06-23 04:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 04:30:00.000', '2014-06-23 05:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 05:15:00.000', '2014-06-23 06:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 06:00:00.000', '2014-06-23 06:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 06:45:00.000', '2014-06-23 07:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 07:30:00.000', '2014-06-23 08:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('CLOSED', '2014-06-23 08:15:00.000', '2014-06-23 09:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('CLOSED', '2014-06-23 09:00:00.000', '2014-06-23 09:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 09:45:00.000', '2014-06-23 10:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 10:30:00.000', '2014-06-23 11:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 11:15:00.000', '2014-06-23 12:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 12:00:00.000', '2014-06-23 12:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 12:45:00.000', '2014-06-23 13:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 13:30:00.000', '2014-06-23 14:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 14:15:00.000', '2014-06-23 15:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 15:00:00.000', '2014-06-23 15:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 15:45:00.000', '2014-06-23 16:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 16:30:00.000', '2014-06-23 17:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 17:15:00.000', '2014-06-23 18:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 18:00:00.000', '2014-06-23 18:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 18:45:00.000', '2014-06-23 19:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 19:30:00.000', '2014-06-23 20:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 20:15:00.000', '2014-06-23 21:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 21:00:00.000', '2014-06-23 21:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 21:45:00.000', '2014-06-23 22:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 22:30:00.000', '2014-06-23 23:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 23:15:00.000', '2014-06-24 00:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-24 00:00:00.000', '2014-06-24 00:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-24 00:45:00.000', '2014-06-24 01:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-24 01:30:00.000', '2014-06-24 02:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-24 02:15:00.000', '2014-06-24 03:00:00.000')
select * from #return_schedule
DECLARE @duration INT=20
SELECT TOP 1
time_sched_start_dt = MAX(CASE WHEN dir = 'S' THEN dt END),
time_sched_end_dt = MAX(CASE WHEN dir = 'E' THEN dt END)
FROM ( -- f
SELECT dir, dt, Grouper = (ROW_NUMBER() OVER(ORDER BY dt, dir)-1)/2
FROM ( -- e
SELECT dir, dt, ct = COUNT(*) OVER(PARTITION BY dt)
FROM ( -- d
SELECT dir = 'S', dt = time_sched_start_dt
FROM #return_schedule sr
WHERE sr.slot_col1 = ''
UNION ALL
SELECT dir = 'E', dt = time_sched_end_dt
FROM #return_schedule sr
WHERE sr.slot_col1 = ''
) d
) e
WHERE ct = 1
) f
GROUP BY Grouper
HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration
ORDER BY time_sched_start_dt
drop table #return_schedule
June 24, 2014 at 8:46 am
Sachin Nandanwar (6/24/2014)
Hello guys,As rightly pointed out by Chris the query I posted earlier were having some issues.Please see refined one.
DECLARE @duration INT=20;
WITH cte
AS (SELECT TOP 1 time_sched_start_dt,
enddt
FROM (SELECT T.*,
T1.time_sched_start_dt
strtdt,
T1.time_sched_end_dt
enddt,
Datediff(minute, T1.time_sched_start_dt,
T.time_sched_end_dt)
- @duration diff
FROM #return_schedule T1
CROSS apply(SELECT *
FROM #return_schedule T2
WHERE T1.col_val = T2.col_val)T
WHERE Datediff(minute, T.time_sched_start_dt,
T1.time_sched_end_dt)
>=
@duration)T
ORDER BY diff DESC)
SELECT Min(time_sched_start_dt)time_sched_start_dt,
Max(time_sched_end_dt) time_sched_end_dt,
Sum(CONVERT(INT, col_val))
FROM (SELECT *
FROM #return_schedule
WHERE time_sched_start_dt >= (SELECT time_sched_start_dt
FROM cte)
AND time_sched_end_dt <= (SELECT enddt
FROM cte))t
HAVING SUM(CONVERT(INT, col_val)) = 0
Hopefully this one meets all the requirements.
This returns no results and I was expecting 2014-06-23 02:15:00.000, 2014-06-23 04:30:00.000
create table #return_schedule
(slot_col1 char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('3108337', '2014-06-23 00:00:00.000', '2014-06-23 00:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 00:45:00.000', '2014-06-23 01:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('123', '2014-06-23 01:30:00.000', '2014-06-23 02:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 02:15:00.000', '2014-06-23 03:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 03:00:00.000', '2014-06-23 03:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 03:45:00.000', '2014-06-23 04:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 04:30:00.000', '2014-06-23 05:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 05:15:00.000', '2014-06-23 06:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 06:00:00.000', '2014-06-23 06:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 06:45:00.000', '2014-06-23 07:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 07:30:00.000', '2014-06-23 08:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('CLOSED', '2014-06-23 08:15:00.000', '2014-06-23 09:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('CLOSED', '2014-06-23 09:00:00.000', '2014-06-23 09:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 09:45:00.000', '2014-06-23 10:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 10:30:00.000', '2014-06-23 11:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 11:15:00.000', '2014-06-23 12:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 12:00:00.000', '2014-06-23 12:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 12:45:00.000', '2014-06-23 13:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 13:30:00.000', '2014-06-23 14:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 14:15:00.000', '2014-06-23 15:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 15:00:00.000', '2014-06-23 15:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 15:45:00.000', '2014-06-23 16:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 16:30:00.000', '2014-06-23 17:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 17:15:00.000', '2014-06-23 18:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 18:00:00.000', '2014-06-23 18:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 18:45:00.000', '2014-06-23 19:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 19:30:00.000', '2014-06-23 20:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 20:15:00.000', '2014-06-23 21:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 21:00:00.000', '2014-06-23 21:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 21:45:00.000', '2014-06-23 22:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 22:30:00.000', '2014-06-23 23:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 23:15:00.000', '2014-06-24 00:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-24 00:00:00.000', '2014-06-24 00:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-24 00:45:00.000', '2014-06-24 01:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-24 01:30:00.000', '2014-06-24 02:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-24 02:15:00.000', '2014-06-24 03:00:00.000')
select * from #return_schedule
DECLARE @duration INT=120;
WITH cte
AS (SELECT TOP 1 time_sched_start_dt,
enddt
FROM (SELECT T.*,
T1.time_sched_start_dt
strtdt,
T1.time_sched_end_dt
enddt,
Datediff(minute, T1.time_sched_start_dt,
T.time_sched_end_dt)
- @duration diff
FROM #return_schedule T1
CROSS apply(SELECT *
FROM #return_schedule T2
WHERE T1.slot_col1 = T2.slot_col1)T
WHERE Datediff(minute, T.time_sched_start_dt,
T1.time_sched_end_dt)
>=
@duration)T
ORDER BY diff DESC)
SELECT Min(time_sched_start_dt)time_sched_start_dt,
Max(time_sched_end_dt) time_sched_end_dt,
Sum(CONVERT(INT, slot_col1))
FROM (SELECT *
FROM #return_schedule
WHERE time_sched_start_dt >= (SELECT time_sched_start_dt
FROM cte)
AND time_sched_end_dt <= (SELECT enddt
FROM cte))t
HAVING SUM(CONVERT(INT, slot_col1)) = 0
drop table #return_schedule
June 24, 2014 at 8:54 am
David Burrows (6/24/2014)
My solution
;WITH cte (RowID, col_val, time_sched_start_dt, time_sched_end_dt)
AS (
SELECT ROW_NUMBER() OVER (ORDER BY a.time_sched_start_dt),
col_val, time_sched_start_dt, time_sched_end_dt
FROM #return_schedule a
),
cte2 (GroupID, time_sched_start_dt, time_sched_end_dt)
AS (
SELECT RowID-ROW_NUMBER() OVER (ORDER BY time_sched_start_dt),
time_sched_start_dt, time_sched_end_dt
FROM cte
WHERE col_val = ''
)
SELECT TOP 1 GroupID,MIN(time_sched_start_dt), MAX(time_sched_end_dt)
FROM cte2
GROUP BY GroupID
HAVING DATEDIFF(minute,MIN(time_sched_start_dt), MAX(time_sched_end_dt)) >= @Duration
ORDER BY GroupID ASC
Returns this:
GroupID(No column name)(No column name)
22014-06-23 02:15:00.0002014-06-23 08:15:00.000
Would expect
GroupID(No column name)(No column name)
22014-06-23 02:15:00.0002014-06-23 04:30:00.000
create table #return_schedule
(slot_col1 char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('3108337', '2014-06-23 00:00:00.000', '2014-06-23 00:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 00:45:00.000', '2014-06-23 01:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('123', '2014-06-23 01:30:00.000', '2014-06-23 02:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 02:15:00.000', '2014-06-23 03:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 03:00:00.000', '2014-06-23 03:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 03:45:00.000', '2014-06-23 04:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 04:30:00.000', '2014-06-23 05:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 05:15:00.000', '2014-06-23 06:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 06:00:00.000', '2014-06-23 06:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 06:45:00.000', '2014-06-23 07:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 07:30:00.000', '2014-06-23 08:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('CLOSED', '2014-06-23 08:15:00.000', '2014-06-23 09:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('CLOSED', '2014-06-23 09:00:00.000', '2014-06-23 09:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 09:45:00.000', '2014-06-23 10:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 10:30:00.000', '2014-06-23 11:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 11:15:00.000', '2014-06-23 12:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 12:00:00.000', '2014-06-23 12:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 12:45:00.000', '2014-06-23 13:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 13:30:00.000', '2014-06-23 14:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 14:15:00.000', '2014-06-23 15:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 15:00:00.000', '2014-06-23 15:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 15:45:00.000', '2014-06-23 16:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 16:30:00.000', '2014-06-23 17:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 17:15:00.000', '2014-06-23 18:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 18:00:00.000', '2014-06-23 18:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 18:45:00.000', '2014-06-23 19:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 19:30:00.000', '2014-06-23 20:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 20:15:00.000', '2014-06-23 21:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 21:00:00.000', '2014-06-23 21:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 21:45:00.000', '2014-06-23 22:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 22:30:00.000', '2014-06-23 23:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-23 23:15:00.000', '2014-06-24 00:00:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-24 00:00:00.000', '2014-06-24 00:45:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-24 00:45:00.000', '2014-06-24 01:30:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-24 01:30:00.000', '2014-06-24 02:15:00.000')
Insert Into #return_schedule
(slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('', '2014-06-24 02:15:00.000', '2014-06-24 03:00:00.000')
select * from #return_schedule
DECLARE @duration INT=120;
;WITH cte (RowID, slot_col1, time_sched_start_dt, time_sched_end_dt)
AS (
SELECT ROW_NUMBER() OVER (ORDER BY a.time_sched_start_dt),
slot_col1, time_sched_start_dt, time_sched_end_dt
FROM #return_schedule a
),
cte2 (GroupID, time_sched_start_dt, time_sched_end_dt)
AS (
SELECT RowID-ROW_NUMBER() OVER (ORDER BY time_sched_start_dt),
time_sched_start_dt, time_sched_end_dt
FROM cte
WHERE slot_col1 = ''
)
SELECT TOP 1 GroupID,MIN(time_sched_start_dt), MAX(time_sched_end_dt)
FROM cte2
GROUP BY GroupID
HAVING DATEDIFF(minute,MIN(time_sched_start_dt), MAX(time_sched_end_dt)) >= @duration
ORDER BY GroupID ASC
drop table #return_schedule
June 24, 2014 at 8:54 am
Rog Saber (6/23/2014)
I'm having trouble writing a query and could use some help please.I want to find the earliest time_sched_start_dt, time_sched_end_dt where col_val is null
and the datediff between time_sched_start_dt and time_sched_end_dt is at least 60 minutes.
Looks good to me:
create table #return_schedule
(slot_col1 char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)
Insert Into #return_schedule (slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('3108337', '2014-06-23 00:00:00.000', '2014-06-23 00:45:00.000'),
('', '2014-06-23 00:45:00.000', '2014-06-23 01:30:00.000'),
('', '2014-06-23 01:30:00.000', '2014-06-23 02:15:00.000'),
('', '2014-06-23 02:15:00.000', '2014-06-23 03:00:00.000'),
('', '2014-06-23 03:00:00.000', '2014-06-23 03:45:00.000'),
('', '2014-06-23 03:45:00.000', '2014-06-23 04:30:00.000'),
('', '2014-06-23 04:30:00.000', '2014-06-23 05:15:00.000'),
('', '2014-06-23 05:15:00.000', '2014-06-23 06:00:00.000'),
('', '2014-06-23 06:00:00.000', '2014-06-23 06:45:00.000'),
('', '2014-06-23 06:45:00.000', '2014-06-23 07:30:00.000'),
('', '2014-06-23 07:30:00.000', '2014-06-23 08:15:00.000'),
('CLOSED', '2014-06-23 08:15:00.000', '2014-06-23 09:00:00.000'),
('CLOSED', '2014-06-23 09:00:00.000', '2014-06-23 09:45:00.000'),
('', '2014-06-23 09:45:00.000', '2014-06-23 10:30:00.000'),
('', '2014-06-23 10:30:00.000', '2014-06-23 11:15:00.000'),
('', '2014-06-23 11:15:00.000', '2014-06-23 12:00:00.000'),
('', '2014-06-23 12:00:00.000', '2014-06-23 12:45:00.000'),
('', '2014-06-23 12:45:00.000', '2014-06-23 13:30:00.000'),
('', '2014-06-23 13:30:00.000', '2014-06-23 14:15:00.000'),
('', '2014-06-23 14:15:00.000', '2014-06-23 15:00:00.000'),
('', '2014-06-23 15:00:00.000', '2014-06-23 15:45:00.000'),
('', '2014-06-23 15:45:00.000', '2014-06-23 16:30:00.000'),
('', '2014-06-23 16:30:00.000', '2014-06-23 17:15:00.000'),
('', '2014-06-23 17:15:00.000', '2014-06-23 18:00:00.000'),
('', '2014-06-23 18:00:00.000', '2014-06-23 18:45:00.000'),
('', '2014-06-23 18:45:00.000', '2014-06-23 19:30:00.000'),
('', '2014-06-23 19:30:00.000', '2014-06-23 20:15:00.000'),
('', '2014-06-23 20:15:00.000', '2014-06-23 21:00:00.000'),
('', '2014-06-23 21:00:00.000', '2014-06-23 21:45:00.000'),
('', '2014-06-23 21:45:00.000', '2014-06-23 22:30:00.000'),
('', '2014-06-23 22:30:00.000', '2014-06-23 23:15:00.000'),
('', '2014-06-23 23:15:00.000', '2014-06-24 00:00:00.000'),
('', '2014-06-24 00:00:00.000', '2014-06-24 00:45:00.000'),
('', '2014-06-24 00:45:00.000', '2014-06-24 01:30:00.000'),
('', '2014-06-24 01:30:00.000', '2014-06-24 02:15:00.000'),
('', '2014-06-24 02:15:00.000', '2014-06-24 03:00:00.000')
select * from #return_schedule
DECLARE @duration INT=20
SELECT TOP 1
time_sched_start_dt = MAX(CASE WHEN dir = 'S' THEN dt END),
time_sched_end_dt = MAX(CASE WHEN dir = 'E' THEN dt END)
FROM ( -- f
SELECT dir, dt, Grouper = (ROW_NUMBER() OVER(ORDER BY dt, dir)-1)/2
FROM ( -- e
SELECT dir, dt, ct = COUNT(*) OVER(PARTITION BY dt)
FROM ( -- d
SELECT dir = 'S', dt = time_sched_start_dt
FROM #return_schedule sr
WHERE sr.slot_col1 = ''
UNION ALL
SELECT dir = 'E', dt = time_sched_end_dt
FROM #return_schedule sr
WHERE sr.slot_col1 = ''
) d
) e
WHERE ct = 1
) f
GROUP BY Grouper
HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration
ORDER BY time_sched_start_dt
-- Result
-- 2014-06-23 00:45:00.000- 2014-06-23 08:15:00.000
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 24, 2014 at 9:02 am
ChrisM@Work (6/24/2014)
Rog Saber (6/23/2014)
I'm having trouble writing a query and could use some help please.I want to find the earliest time_sched_start_dt, time_sched_end_dt where col_val is null
and the datediff between time_sched_start_dt and time_sched_end_dt is at least 60 minutes.
Looks good to me:
create table #return_schedule
(slot_col1 char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)
Insert Into #return_schedule (slot_col1 , time_sched_start_dt, time_sched_end_dt)
Values
('3108337', '2014-06-23 00:00:00.000', '2014-06-23 00:45:00.000'),
('', '2014-06-23 00:45:00.000', '2014-06-23 01:30:00.000'),
('', '2014-06-23 01:30:00.000', '2014-06-23 02:15:00.000'),
('', '2014-06-23 02:15:00.000', '2014-06-23 03:00:00.000'),
('', '2014-06-23 03:00:00.000', '2014-06-23 03:45:00.000'),
('', '2014-06-23 03:45:00.000', '2014-06-23 04:30:00.000'),
('', '2014-06-23 04:30:00.000', '2014-06-23 05:15:00.000'),
('', '2014-06-23 05:15:00.000', '2014-06-23 06:00:00.000'),
('', '2014-06-23 06:00:00.000', '2014-06-23 06:45:00.000'),
('', '2014-06-23 06:45:00.000', '2014-06-23 07:30:00.000'),
('', '2014-06-23 07:30:00.000', '2014-06-23 08:15:00.000'),
('CLOSED', '2014-06-23 08:15:00.000', '2014-06-23 09:00:00.000'),
('CLOSED', '2014-06-23 09:00:00.000', '2014-06-23 09:45:00.000'),
('', '2014-06-23 09:45:00.000', '2014-06-23 10:30:00.000'),
('', '2014-06-23 10:30:00.000', '2014-06-23 11:15:00.000'),
('', '2014-06-23 11:15:00.000', '2014-06-23 12:00:00.000'),
('', '2014-06-23 12:00:00.000', '2014-06-23 12:45:00.000'),
('', '2014-06-23 12:45:00.000', '2014-06-23 13:30:00.000'),
('', '2014-06-23 13:30:00.000', '2014-06-23 14:15:00.000'),
('', '2014-06-23 14:15:00.000', '2014-06-23 15:00:00.000'),
('', '2014-06-23 15:00:00.000', '2014-06-23 15:45:00.000'),
('', '2014-06-23 15:45:00.000', '2014-06-23 16:30:00.000'),
('', '2014-06-23 16:30:00.000', '2014-06-23 17:15:00.000'),
('', '2014-06-23 17:15:00.000', '2014-06-23 18:00:00.000'),
('', '2014-06-23 18:00:00.000', '2014-06-23 18:45:00.000'),
('', '2014-06-23 18:45:00.000', '2014-06-23 19:30:00.000'),
('', '2014-06-23 19:30:00.000', '2014-06-23 20:15:00.000'),
('', '2014-06-23 20:15:00.000', '2014-06-23 21:00:00.000'),
('', '2014-06-23 21:00:00.000', '2014-06-23 21:45:00.000'),
('', '2014-06-23 21:45:00.000', '2014-06-23 22:30:00.000'),
('', '2014-06-23 22:30:00.000', '2014-06-23 23:15:00.000'),
('', '2014-06-23 23:15:00.000', '2014-06-24 00:00:00.000'),
('', '2014-06-24 00:00:00.000', '2014-06-24 00:45:00.000'),
('', '2014-06-24 00:45:00.000', '2014-06-24 01:30:00.000'),
('', '2014-06-24 01:30:00.000', '2014-06-24 02:15:00.000'),
('', '2014-06-24 02:15:00.000', '2014-06-24 03:00:00.000')
select * from #return_schedule
DECLARE @duration INT=20
SELECT TOP 1
time_sched_start_dt = MAX(CASE WHEN dir = 'S' THEN dt END),
time_sched_end_dt = MAX(CASE WHEN dir = 'E' THEN dt END)
FROM ( -- f
SELECT dir, dt, Grouper = (ROW_NUMBER() OVER(ORDER BY dt, dir)-1)/2
FROM ( -- e
SELECT dir, dt, ct = COUNT(*) OVER(PARTITION BY dt)
FROM ( -- d
SELECT dir = 'S', dt = time_sched_start_dt
FROM #return_schedule sr
WHERE sr.slot_col1 = ''
UNION ALL
SELECT dir = 'E', dt = time_sched_end_dt
FROM #return_schedule sr
WHERE sr.slot_col1 = ''
) d
) e
WHERE ct = 1
) f
GROUP BY Grouper
HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration
ORDER BY time_sched_start_dt
-- Result
-- 2014-06-23 00:45:00.000- 2014-06-23 08:15:00.000
Here is your result:
-- Result
-- 2014-06-23 00:45:00.000- 2014-06-23 08:15:00.000
I would expect:
-- Result
-- 2014-06-23 00:45:00.000- 2014-06-23 01:30:00.000
June 24, 2014 at 9:11 am
;WITH cte (RowID, slot_col1, time_sched_start_dt, time_sched_end_dt)
AS (
SELECT ROW_NUMBER() OVER (ORDER BY a.time_sched_start_dt),
slot_col1, time_sched_start_dt, time_sched_end_dt
FROM #return_schedule a
),
cte2 (GroupID, time_sched_start_dt, time_sched_end_dt)
AS (
SELECT RowID-ROW_NUMBER() OVER (ORDER BY time_sched_start_dt),
time_sched_start_dt, time_sched_end_dt
FROM cte
WHERE slot_col1 = ''
)
SELECT TOP 1 MIN(b.time_sched_start_dt),a.time_sched_end_dt
FROM cte2 a
LEFT JOIN cte2 b ON b.GroupID = a.GroupID AND b.time_sched_start_dt < a.time_sched_end_dt
GROUP BY a.GroupID,a.time_sched_end_dt
HAVING DATEDIFF(minute,MIN(b.time_sched_start_dt),a.time_sched_end_dt) >= @Duration
ORDER BY a.GroupID ASC,a.time_sched_end_dt ASC
Far away is close at hand in the images of elsewhere.
Anon.
June 24, 2014 at 9:14 am
David Burrows (6/24/2014)
;WITH cte (RowID, slot_col1, time_sched_start_dt, time_sched_end_dt)
AS (
SELECT ROW_NUMBER() OVER (ORDER BY a.time_sched_start_dt),
slot_col1, time_sched_start_dt, time_sched_end_dt
FROM #return_schedule a
),
cte2 (GroupID, time_sched_start_dt, time_sched_end_dt)
AS (
SELECT RowID-ROW_NUMBER() OVER (ORDER BY time_sched_start_dt),
time_sched_start_dt, time_sched_end_dt
FROM cte
WHERE slot_col1 = ''
)
SELECT TOP 1 MIN(b.time_sched_start_dt),a.time_sched_end_dt
FROM cte2 a
LEFT JOIN cte2 b ON b.GroupID = a.GroupID AND b.time_sched_start_dt < a.time_sched_end_dt
GROUP BY a.GroupID,a.time_sched_end_dt
HAVING DATEDIFF(minute,MIN(b.time_sched_start_dt),a.time_sched_end_dt) >= @Duration
ORDER BY a.GroupID ASC,a.time_sched_end_dt ASC
I have run this with different criteria and it appears to work great - thank you very much.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply