March 9, 2007 at 1:34 pm
Any quick ways to quickly write a query to get a date/time difference from more than one row.
EMPID START DATE TIME END DATE TIME
10 01/01/2006 01/04/2006
10 01/07/2006 01/08/2006
10 01/09/2006 01/10/2006
10 01/11/2006 01/12/2006
20 01/01/2006 01/04/2006
20 01/07/2006 01/08/2006
20 01/09/2006 01/10/2006
20 01/11/2006 01/12/2006
For each EMPID, I need to find the difference between each EndDate and the next StartDate....Any help would be greatly appreciated....thanks
March 9, 2007 at 2:08 pm
this should be a starting point to work with, i named the table table_a for your three fields:
select
orig.*, datediff(dd, orig.end_date_time, x.min_start_date) as day_diff
from
table_a orig
left join (
select orig.empid, orig.start_date_time, orig.end_date_time, min(nd.start_date_time) min_start_date
from
table_a orig
inner join table_a nd
on orig.empid = nd.empid
and orig.end_date_time < nd.start_date_time
group by orig.empid, orig.start_date_time, orig.end_date_time) x
on orig.empid = x.empid
and orig.start_date_time = x.start_date_time
and orig.end_date_time = x.end_date_time
March 12, 2007 at 1:32 pm
Beautiful...thanks, works nicely.
I need to take it a step further.
I have the following table
EMPID DIFF
1 0
1 20
10 0
10 1
10 16
10 18
10 24
30 0
30 16
40 0
40 3
40 7
40 8
OK..for each unique EMPID (for all the 1s, for all the 10s, for all the 30s, for all the 40s) I need to extract(group) the rows that have a diff that is 3 or less of the previous row.
For example, I wouldn't want to extract the 1s or the 30s, but for the 10s, I would want to group the first 2 '10' rows and the second 2 '10' rows, but leave out the last '10' row.
For the 40s, I would want to extact and group together the first 2 '40' rows and the last 2 '40' rows.
March 12, 2007 at 7:33 pm
Dave
If I have time, I would be happy to try to write up some SQL for you but I am not totally clear on what your resultset should look like.
March 13, 2007 at 6:33 am
Sorry for the confusion...
I need to select rows in this table where for each EMPID the span in the value of the difference column is 3 or less.
For instance, for EMPID 10
EMPID DIFFERENCE
10 0
10 1
10 16
10 18
10 24
I need SQL to group rows together when the span in the difference column is 3 or less.
So, I would want to pull
The first 2 rows as a group because the span between 0 and 1 is 3 or less.
The 3rd and 4th rows as a group b/c the span between 16 and 18 is 3 or less.
The 5th row I would now want to select b/c the value of 24 is 6 more than its previous row 18 (not 3 or less).
March 13, 2007 at 7:53 am
I meant to say the 5th row I would NOT want to select b/c the value of 24 is 6 more than its previous row 18 (not 3 or less).
March 14, 2007 at 9:23 am
I may be adding steps to this that are unnecessary.....
The original table
EMPID START DATE TIME END DATE TIME
10 01/01/2006 01/04/2006
10 01/07/2006 01/08/2006
10 01/09/2006 01/10/2006
10 01/11/2006 01/12/2006
20 01/01/2006 01/04/2006
20 01/07/2006 01/08/2006
20 01/09/2006 01/10/2006
20 01/11/2006 01/12/2006
30 01/04/2007 01/04/2007
30 01/05/2007 01/05/2007
30 01/14/2007 01/14/2007
30 01/16/2007 01/16/2007
30 01/27/2007 01/27/2007
For each EMPID row...
I need to take the END DATE TIME and compare it to the very next START DATE TIME...if the span is 3 days or less, I need to include both rows in my result.
So, for row1 and row 2 of EMPID 10...
The first row END DATE TIME = 01/04/2006
The second row START DATE TIME = 01/07/2006
RESULT: Don't include these rows b/c it's > 3 days.
Here's where it gets tricky b/c there is overlap...
For row2 and row3 of EMPID 10....
row2's END DATE TIME = 01/08/2006
row3's START DATE TIME = 01/09/2006
RESULT: I want to include rows 2 and 3 in my result b/c the span is < 3 days even though I said that I didn't want row 2 in the previous example.
The overlap is confusing b/c I'm trying to pull groups of rows (it can be 2 rows or a lot of rows) where the span is 3 days.
March 14, 2007 at 1:46 pm
quick question, you said the following:
The first row END DATE TIME = 01/04/2006
The second row START DATE TIME = 01/07/2006
you would not want these records since it is greater than three days but actually, this equals three days. so do you want everything has a diff of < 3?
March 14, 2007 at 2:24 pm
Dave
This is ugly but it looks like it gives you what you want.:
select a.empid, a.start_date_time, a.end_date_time
from
table_a a
inner join (
select orig.*, datediff(dd, orig.end_date_time, x.min_start_date) as day_diff, dateadd(dd, datediff(dd, orig.end_date_time, x.min_start_date), orig.end_date_time ) next_start_date
from
table_a orig
left join (
select orig.empid, orig.start_date_time, orig.end_date_time, min(nd.start_date_time) min_start_date
from
table_a orig
inner join table_a nd
on orig.empid = nd.empid
and orig.end_date_time < nd.start_date_time
group by orig.empid, orig.start_date_time, orig.end_date_time) x
on orig.empid = x.empid
and orig.start_date_time = x.start_date_time
and orig.end_date_time = x.end_date_time
where
datediff(dd, orig.end_date_time, x.min_start_date) < 3
) da
on a.empid = da.empid
and a.start_date_time = da.start_date_time
union
select a.empid, a.start_date_time, a.end_date_time
from
table_a a
inner join (
select orig.*, datediff(dd, orig.end_date_time, x.min_start_date) as day_diff, dateadd(dd, datediff(dd, orig.end_date_time, x.min_start_date), orig.end_date_time ) next_start_date
from
table_a orig
left join (
select orig.empid, orig.start_date_time, orig.end_date_time, min(nd.start_date_time) min_start_date
from
table_a orig
inner join table_a nd
on orig.empid = nd.empid
and orig.end_date_time < nd.start_date_time
group by orig.empid, orig.start_date_time, orig.end_date_time) x
on orig.empid = x.empid
and orig.start_date_time = x.start_date_time
and orig.end_date_time = x.end_date_time
where
datediff(dd, orig.end_date_time, x.min_start_date) < 3
) da
on a.empid = da.empid
and a.start_date_time = da.next_start_date
order by empid, start_date_time, end_date_time
March 14, 2007 at 7:02 pm
WOW..this is a beauty and it's getting me a lot closer, but for some of the EMPIDs, I'm missing some records....for instance...
For the following
EMPID START_DATE END_DATE
301626 2007-01-05 02:15:00.000 2007-01-05 06:49:00.000
301626 2007-01-21 12:58:00.000 2007-01-21 17:37:00.000
301626 2007-01-23 15:55:00.000 2007-01-23 22:15:00.000
Your SQL is pulling the row
301626 2007-01-21 12:58:00.000 2007-01-21 17:37:00.000
When it should definitely exclude the first row, but needs to pull the 3rd row as well b/c it falls within the 3 day or less criteria.
Another example:
EMPID START_DATE END_DATE
401098 2007-01-17 12:31:00.000 2007-01-17 18:14:00.000
401098 2007-01-18 22:01:00.000 2007-01-19 00:24:00.000
401098 2007-01-21 11:48:00.000 2007-01-21 15:01:00.000
Your SQL is pulling the rows
401098 2007-01-17 12:31:00.000 2007-01-17 18:14:00.000
401098 2007-01-18 22:01:00.000 2007-01-19 00:24:00.000
When it should pull all three rows b/c the last row is less then 3 days from the 2nd row's end_date
Any ideas....I really appreciate what you've done here and I'm grateful for any additional information.
March 15, 2007 at 6:30 am
Dave
I was happy to get you this far but I cannot tell what the problem is from the original dataset you sent. The SQL I put together returned the following resultset, which appears to be all of the records that should return:
10 2006-01-07 00:00:00.000 2006-01-08 00:00:00.000
10 2006-01-09 00:00:00.000 2006-01-10 00:00:00.000
10 2006-01-11 00:00:00.000 2006-01-12 00:00:00.000
20 2006-01-07 00:00:00.000 2006-01-08 00:00:00.000
20 2006-01-09 00:00:00.000 2006-01-10 00:00:00.000
20 2006-01-11 00:00:00.000 2006-01-11 00:00:00.000
30 2007-01-04 00:00:00.000 2007-01-04 00:00:00.000
30 2007-01-05 00:00:00.000 2007-01-05 00:00:00.000
30 2007-01-14 00:00:00.000 2007-01-14 00:00:00.000
30 2007-01-16 00:00:00.000 2007-01-16 00:00:00.000
Try working with the SQL to see if your data would return a different resultset to your liking.
Good luck
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply