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...

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...

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,...

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...

ChrisM@Work (6/24/2014)

---

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...

ChrisM@Work (6/24/2014)

---

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...

If I run the following, it returns:

time_sched_start_dt time_sched_end_dt

2014-06-23 10:45:00.000 2014-06-23 12:15:00.000

I would expect it to return the first 45 minute available timeslot:

time_sched_start_dt time_sched_end_dt

2014-06-23 09:15:00.000 2014-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...

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_dt enddt

2014-06-23 09:15:00.000 2014-06-23 11:30:00.000

I don't want it to...

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...

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

...

I will give this a try

I will give it a try thank you.

These work great. Thank you all.

OK I think I figured it out now.

OK thank you all for the suggestions.