Datetime Diff in day and hour excluding weekends


  
create table test (id int identity(1,1), Checkindatetime datetime, Checkoutdate date,	Checkouttime time)
Insert into test values('Sep 1, 2015 4:37:37 AM',	'Sep 3, 2015',	'11:19:29')
,('Sep 3, 2015   3:45:44 PM',	'Sep 4, 2015',	'18:42:31')
,('Sep 2, 2015   9:05:19 AM',	'Sep 2, 2015',	'13:23:20')
,('Sep 2, 2015   9:11:20 AM',	'Sep 8, 2015',	'14:15:35')


--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,mydate as (
select id, Cast(dateadd(day,n-1, Checkindatetime) as date) dt , Checkoutdate,
row_number() Over(partition by id Order by Cast(dateadd(day,n-1, Checkindatetime) as date)) rn1,
row_number() Over(partition by id Order by Cast(dateadd(day,n-1, Checkindatetime) as date) desc) rn2,
Checkindatetime, Cast(Checkoutdate as datetime) + Cast(Checkouttime as   datetime) Checkoutdatetime 
 
from test 
cross apply (select n from Nums) d(n)
WHERE dateadd(day,n-1,Checkindatetime)<=Cast(Checkoutdate as datetime) + Cast(Checkouttime as   datetime)
)


,myctedt as (
Select id,  min(Checkindatetime) Checkindatetime, max(Checkoutdatetime) Checkoutdatetime,
Sum(Datediff(hour, case when rn1=1 then Checkindatetime else dt end ,
case when rn2=1 then Checkoutdatetime else dateadd(day,1,dt) end ) ) diffinHr 
from mydate
WHERE datepart(weekday,dt) not in (7,1) --exclude Saturday and 
Group by id)

select id,Checkindatetime,Checkoutdatetime 
 , Cast(diffinHr/24 as varchar(2))+' day(s) '+ Cast(diffinHr%24 as varchar(2)) +' hour(s)'   as [day:hh]
from myctedt 
Order by id 



drop table test
 

http://forums.asp.net/t/2068480.aspx?how+to+count+for+datetime

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s