asked on

Date Difference in SQL Server

Hi Experts,

I need your help in a query. I am trying to build a query which will give me the count of number of records where the difference between the two dates is less than 24 hours by excluding weekends.

Scenario 1 : If a record has a start_time as '11/26/2013 03:37 PM' and end_time as '11/26/2013 11:56 PM', then this record needs to be considered

Scenario 2 : If a record has a start_time as '11/26/2013 03:37 PM' and end_time as '11/27/2013 08:36 AM', then this record also needs to be considered.

Scenario 3: If a record has a start_time as '11/29/2013 01:22 PM' and end_time as '12/02/2013 09:40 AM', then this record also needs to be considered.

Please help me with the query.

Thanks in advance!!

ASKER

I have this below query, but this is working at the day level..

I need hour level..

declare @startdate datetime = '2013-11-28 13:05:06.000' 
declare @enddate datetime = '2013-11-29 16:30:06.000' 

select datediff(dd,@startdate,@enddate) - 2*datediff(ww,@startdate,@enddate)
       + case when datepart(dw,@startdate)=1 or datepart(dw,@enddate)=7 
	          then -1
              when datepart(dw,@startdate)=7 or datepart(dw,@enddate)=1 
			  then +1
         else 0 
		 end

Open in new window

I want to include this below logic to the above query..

declare @startdate datetime = '2013-11-28 13:05:06.000' 
declare @enddate datetime = '2013-11-28 17:05:06.000' 
select CASE WHEN @enddate IS NOT NULL AND 1.0 * datediff(mi , @startdate, @enddate) / (60 * 24) <= 1 THEN '1' else '100' END

Open in new window

Any help would be greatly appreciated!!!

Tony303

How is the nested case like this work for you? Is an answer of 2 going to be OK??

select datediff(dd,@startdate,@enddate) - 2*datediff(ww,@startdate,@enddate)
       + case when datepart(dw,@startdate)=1 or datepart(dw,@enddate)=7 
	          then -1
              when datepart(dw,@startdate)=7 or datepart(dw,@enddate)=1 
			  then  (CASE WHEN @enddate IS NOT NULL AND 1.0 * datediff(mi , @startdate, @enddate) / (60 * 24) <= 1 
THEN '1' else '100' END) ELSE  
			  +1
         	 end

Open in new window

ASKER

Hi Tony,

Thanks for the reply..

In the query you posted,

If the start date = '2013-11-28 13:05:06.000' and enddate = '2013-11-29 10:05:06.000' the query is resulting in "2"

And if start date = '2013-11-28 13:05:06.000' and enddate = '2013-11-28 17:05:06.000' the query is resulting in "1"

This is not consistent. I would like to take the count of these.

I would need to populate "1" if the difference between the two dates is less than 24 hours and should exclude weekends.

Please help!!

Thanks,

SOLUTION