June 23, 2014 at 12:53 pm
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.
Given the data below, I would want to return
time_sched_start_dt = 2014-06-23 10:45:00.000
time_sched_end_dt = 2014-06-23 11:45:00.000
create table #return_schedule
(col_val char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('1', '6/23/2014 08:30:00', '6/23/2014 09:15:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('', '6/23/2014 09:15:00', '6/23/2014 10:00:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('2', '6/23/2014 10:00:00', '6/23/2014 10:45:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('', '6/23/2014 10:45:00', '6/23/2014 11:30:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('', '6/23/2014 11:30:00', '6/23/2014 12:15:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('3', '6/23/2014 12:15:00', '6/23/2014 13:00:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('', '6/23/2014 13:00:00', '6/23/2014 13:45:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('', '6/23/2014 13:45:00', '6/23/2014 14:30:00')
select * from #return_schedule
drop table #return_schedule
June 23, 2014 at 1:12 pm
Something like this?
select TOP 1 t.* from #return_schedule t
where COALESCE(t.col_val,0)=0
AND DATEDIFF(minute, t.time_sched_end_dt,t.time_sched_start_dt)>= 60
ORDER BY t.time_sched_start_dt ASC, t.time_sched_end_dt ASC
June 23, 2014 at 1:18 pm
Not the most efficient way to do it, but I'm sure someone will come along with a better solution (or I may given more time):
with base as (
select
rs1.time_sched_start_dt,
rs2.time_sched_end_dt,
rn = row_number() over (order by datediff(minute,rs1.time_sched_start_dt,rs2.time_sched_end_dt) asc, rs1.time_sched_start_dt)
from
#return_schedule rs1
cross join #return_schedule rs2
where
(rs1.col_val = '' and rs2.col_val = '') and
datediff(minute,rs1.time_sched_start_dt,rs2.time_sched_end_dt) >= 60
)
select time_sched_start_dt, time_sched_end_dt from base where rn = 1;
June 23, 2014 at 2:23 pm
BOR15K (6/23/2014)
Something like this?select TOP 1 t.* from #return_schedule t
where COALESCE(t.col_val,0)=0
AND DATEDIFF(minute, t.time_sched_end_dt,t.time_sched_start_dt)>= 60
ORDER BY t.time_sched_start_dt ASC, t.time_sched_end_dt ASC
I will give this a try
June 23, 2014 at 2:28 pm
Lynn Pettis (6/23/2014)
Not the most efficient way to do it, but I'm sure someone will come along with a better solution (or I may given more time):
with base as (
select
rs1.time_sched_start_dt,
rs2.time_sched_end_dt,
rn = row_number() over (order by datediff(minute,rs1.time_sched_start_dt,rs2.time_sched_end_dt) asc, rs1.time_sched_start_dt)
from
#return_schedule rs1
cross join #return_schedule rs2
where
(rs1.col_val = '' and rs2.col_val = '') and
datediff(minute,rs1.time_sched_start_dt,rs2.time_sched_end_dt) >= 60
)
select time_sched_start_dt, time_sched_end_dt from base where rn = 1;
This is OK until I change the query to >=70. Then it returns 9:15, 11:00. I cannot use 9:15 because there is a value in the col_val column at 10:00.
I need the earliest start time and the earliest end time where there is no value in col_val. If I change the datediff to 70 I would expect a return of 12:30, 02:00.
June 23, 2014 at 2:38 pm
Rog Saber (6/23/2014)
Lynn Pettis (6/23/2014)
Not the most efficient way to do it, but I'm sure someone will come along with a better solution (or I may given more time):
with base as (
select
rs1.time_sched_start_dt,
rs2.time_sched_end_dt,
rn = row_number() over (order by datediff(minute,rs1.time_sched_start_dt,rs2.time_sched_end_dt) asc, rs1.time_sched_start_dt)
from
#return_schedule rs1
cross join #return_schedule rs2
where
(rs1.col_val = '' and rs2.col_val = '') and
datediff(minute,rs1.time_sched_start_dt,rs2.time_sched_end_dt) >= 60
)
select time_sched_start_dt, time_sched_end_dt from base where rn = 1;
This is OK until I change the query to >=70. Then it returns 9:15, 11:00. I cannot use 9:15 because there is a value in the col_val column at 10:00.
I need the earliest start time and the earliest end time where there is no value in col_val. If I change the datediff to 70 I would expect a return of 12:30, 02:00.
Well, you really need to be more specific in your requirements. From what I read I did not take into account values in col_val that separated values in start_time and end-time. Based on my reading I simply excluded those rows of data that col_val was not ''.
Guess you should see what mods are needed to make this code meet the change in specs.
June 23, 2014 at 2:41 pm
Lynn Pettis (6/23/2014)
Rog Saber (6/23/2014)
Lynn Pettis (6/23/2014)
Not the most efficient way to do it, but I'm sure someone will come along with a better solution (or I may given more time):
with base as (
select
rs1.time_sched_start_dt,
rs2.time_sched_end_dt,
rn = row_number() over (order by datediff(minute,rs1.time_sched_start_dt,rs2.time_sched_end_dt) asc, rs1.time_sched_start_dt)
from
#return_schedule rs1
cross join #return_schedule rs2
where
(rs1.col_val = '' and rs2.col_val = '') and
datediff(minute,rs1.time_sched_start_dt,rs2.time_sched_end_dt) >= 60
)
select time_sched_start_dt, time_sched_end_dt from base where rn = 1;
This is OK until I change the query to >=70. Then it returns 9:15, 11:00. I cannot use 9:15 because there is a value in the col_val column at 10:00.
I need the earliest start time and the earliest end time where there is no value in col_val. If I change the datediff to 70 I would expect a return of 12:30, 02:00.
Well, you really need to be more specific in your requirements. From what I read I did not take into account values in col_val that separated values in start_time and end-time. Based on my reading I simply excluded those rows of data that col_val was not ''.
Guess you should see what mods are needed to make this code meet the change in specs.
I will try this again. I think I screwed up in my example. Thank you.
What I am trying to do is fill in a time slot. For example, from 8:30 - 9:15 my time slot is taken with col_val = '1'. From 10:00 - 10:45 my time slot is taken with col_val = '2'. from 11:45 - 12:30 my time slot is taken with col_val = '3'. I should have given better tables values as in below.
If the duration of my appointment is 60 minutes, I would expect 10:45 - 11:45.
If the duration of my appointment is 70 minutes, I would expect 12:30 - 14:00.
create table #return_schedule
(col_val char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('1', '6/23/2014 08:30:00', '6/23/2014 09:15:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('', '6/23/2014 09:15:00', '6/23/2014 10:00:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('2', '6/23/2014 10:00:00', '6/23/2014 10:45:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('', '6/23/2014 10:45:00', '6/23/2014 11:00:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('', '6/23/2014 11:00:00', '6/23/2014 11:45:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('3', '6/23/2014 11:45:00', '6/23/2014 12:30:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('', '6/23/2014 12:30:00', '6/23/2014 13:15:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('', '6/23/2014 13:15:00', '6/23/2014 14:00:00')
June 24, 2014 at 1:20 am
The query is purely based on the sample data provided and all the assumptions stated in the earlier posts.
See if this helps.
DECLARE @duration INT=70
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
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 24, 2014 at 2:56 am
Sachin Nandanwar (6/24/2014)
The query is purely based on the sample data provided and all the assumptions stated in the earlier posts.See if this helps.
DECLARE @duration INT=70SELECT 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
With a duration of 20, this returns an incorrect timeslot (2014-06-23 08:30:00.000 - 2014-06-23 09: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 2:57 am
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.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
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 3:13 am
ChrisM@Work (6/24/2014)
With a duration of 20, this returns an incorrect timeslot (2014-06-23 08:30:00.000 - 2014-06-23 09:15:00.000)
No it returns
(2014-06-23 09:15:00.0002014-06-23 10:00:00.000)
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 24, 2014 at 4:08 am
Sachin Nandanwar (6/24/2014)
ChrisM@Work (6/24/2014)
With a duration of 20, this returns an incorrect timeslot (2014-06-23 08:30:00.000 - 2014-06-23 09:15:00.000)No it returns
(2014-06-23 09:15:00.0002014-06-23 10:00:00.000)
Sorry, my mistake, I extended the dataset to add an extra day.
I like your solution. For this type of problem it shows some promise compared to the method I posted, which is derived from an IBG method - and is far more complex, though only about 2x slower. There are some issues with your solution however. Using the parameter of 20, it returns 7 rows with the same value of diff - and TOP is only returning the correct row by accident, not by design. You can check this by commenting out TOP and including diff in the output set. I'd be interested to see if you can fix this.
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 4:34 am
Sachin Nandanwar (6/24/2014)
ChrisM@Work (6/24/2014)
With a duration of 20, this returns an incorrect timeslot (2014-06-23 08:30:00.000 - 2014-06-23 09:15:00.000)No it returns
(2014-06-23 09:15:00.0002014-06-23 10:00:00.000)
Try also DECLARE @duration INT = 120, which should return no rows.
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:39 am
Here is what I tried but I believe I am still having an issue. If I put the duration at 120 minutes it returns:
time_sched_start_dtenddt
2014-06-23 09:15:00.0002014-06-23 11:30:00.000
I don't want it to return this result because a timeslot ('2') is taken at from 10:00 to 10:45.
create table #return_schedule
(col_val char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('1', '6/23/2014 08:30:00', '6/23/2014 09:15:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('', '6/23/2014 09:15:00', '6/23/2014 10:00:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('2', '6/23/2014 10:00:00', '6/23/2014 10:45:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('', '6/23/2014 10:45:00', '6/23/2014 11:30:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('', '6/23/2014 11:30:00', '6/23/2014 12:15:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('3', '6/23/2014 12:15:00', '6/23/2014 13:00:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('', '6/23/2014 13:00:00', '6/23/2014 13:45:00')
Insert Into #return_schedule
(col_val , time_sched_start_dt, time_sched_end_dt)
Values
('', '6/23/2014 13:45:00', '6/23/2014 14:30:00')
select * from #return_schedule
DECLARE @duration INT=120
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
drop table #return_schedule
June 24, 2014 at 7:45 am
Rog Saber (6/24/2014)
Here is what I tried but I believe I am still having an issue. If I put the duration at 120 minutes it returns:time_sched_start_dtenddt
2014-06-23 09:15:00.0002014-06-23 11:30:00.000
I don't want it to return this result because a timeslot ('2') is taken at from 10:00 to 10:45..
.
.
There are some issues with Sachin's query, as pointed out earlier.
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
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply