Date Difference - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Date Difference

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2006-09-13 : 19:05:25

Greetings,

Is there any way of doing a DateDiff when comparing dates from two different rows and different columns? Below is some sample data.

EMPID Reason Start End LOA Days Days Btw
45932 Sick 11/12/99 11/29/99 017
45932 Sick 05/30/00 06/30/00 031
45932 Msik 10/23/00 08/04/01 285
45932 Fmla 08/05/01 09/27/01 053
45932 Over 11/01/01 04/30/02 180
45932 Over 07/02/03 09/30/03 090

I would like to see how many days there were between the first end date of 11/29/99 and the next start date which was 05/30/00 and then doing the rest as well, is this possible?

The next question would then be is it possible to put a filter in the WHERE statement having it only show data where the difference between the two dates is 15 days or less? If this is possible then I would only see the following:

EMPID Reason Start End LOA Days Days Btw
45932 Msik 10/23/00 08/04/01 285
45932 Fmla 08/05/01 09/27/01 053 01

Any help would be greatly appreciated, and as always many thanks in advance.

GC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-13 : 19:29:57

[code]
declare @table table
(
EMPID int,
Reason varchar(10),
Start_LOA datetime,
End_LOA datetime,
Days int
)
insert into @table
select '45932', 'Sick', '11/12/99', '11/29/99', 017 union all
select '45932', 'Sick', '05/30/00', '06/30/00', 031 union all
select '45932', 'Msik', '10/23/00', '08/04/01', 285 union all
select '45932', 'Fmla', '08/05/01', '09/27/01', 053 union all
select '45932', 'Over', '11/01/01', '04/30/02', 180 union all
select '45932', 'Over', '07/02/03', '09/30/03', 090

select t1.*
from @table t1 left join @table t2
on t1.EMPID = t2.EMPID
and t2.Start_LOA = (select min(x.Start_LOA) from @table x where x.EMPID = t2.EMPID and x.Start_LOA > t1.Start_LOA)
left join @table t3
on t1.EMPID = t3.EMPID
and t3.Start_LOA = (select max(x.Start_LOA) from @table x where x.EMPID = t3.EMPID and x.Start_LOA < t1.Start_LOA)
where (
datediff(day, t1.End_LOA, t2.Start_LOA) <= 15
or datediff(day, t3.End_LOA, t1.Start_LOA) <= 15
)
[/code]

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2006-09-14 : 18:11:23

KH,

Worked perfectly.

Many Thanks!!

GC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources