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 THEN lt2+1
END,
‘Gt2Lt5′=
CASE
WHEN mins >= 2 and mins < 5 THEN lt5+1
END,
‘Gt5Lt10′=
CASE
WHEN mins >= 5 and mins < 10 THEN lt10+1
END,
‘Gt10Lt15′=
CASE
WHEN mins >= 10 and mins < 15 THEN lt15+1
END,
‘Gt15′=
CASE
WHEN 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) < 10 THEN ‘0′+cast(replace(datediff(mi,CT,ST)/60,’-',”)as varchar(10))
ELSE cast(replace(datediff(mi,CT,ST)/60,’-',”)as int)
END

from
(
select
ST=substring(flags,charindex(‘calledforservicetime_flag’, flags) +1 + len(‘calledforservicetime_flag’),charindex(‘/calledforservicetime_flag’, flags) – 2 -(charindex(‘calledforservicetime_flag’,flags) + len(‘calledforservicetime_flag’))),
CT=checkintime
from tblactivity where (tblactivity.checkindate between ‘01/01/2006′ and ‘12/18/2008′)
)a where CT<>”