SQL Querys examples Date-Time Difference

SQL Query examples Date-Time Difference

SQL example 1:
select checkindate, isnull(sum(lessthan2), 0) as lessthan2, isnull(sum(Gt2Lt5), 0) as Gt2Lt5, isnull(sum(Gt5Lt10), 0) as Gt5Lt10, isnull(sum(Gt10Lt15), 0) as Gt10Lt15, isnull(sum(Gt15), 0) as Gt15, sum(mins),sum(mins)/(isnull(sum(lessthan2), 0)+isnull(sum(Gt2Lt5), 0)+isnull(sum(Gt5Lt10), 0)+isnull(sum(Gt10Lt15), 0)+isnull(sum(Gt15), 0)) as Mins
from
(
select checkindate, mins,
'lessthan2'=
CASE
WHEN mins >= 0 and mins <>= 2 and mins <>= 5 and mins <>= 10 and mins <>= 15 THEN gt15+1
END

from
(
select lt2=0,lt5=0,lt10=0,lt15=0,gt15=0,checkindate,mins = cast(replace(datediff(mi,logintime,checkintime)%60,'-','') as Int) from tblactivity where (logintime<>'' and checkintime<>'' and checkindate between cast('01/01/2006' as datetime) and cast('07/01/2006' as datetime))
)a

)b group by checkindate




SQL example 2: Time difference calculation

select cast(datediff(mi,'11:00 AM','7:10 PM')/60 as varchar(10))+':'+cast(datediff(mi,'11:00 AM','7:10 PM')%60 as varchar(10))+':00'




SQL example 3: Time difference calculation
select CT,ST,
Mins =cast(replace(datediff(mi,CT,ST)%60,'-','') as varchar(10)),'Hours'=

CASE
WHEN cast(replace(datediff(mi,CT,ST)/60,'-','')as int) < st="substring(flags,charindex('calledforservicetime_flag'," ct="checkintime">''

More SQL Server Examples @: http://silvernight.wordpress.com/