Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

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!!
0
ravichand-sql
Asked:
ravichand-sql
3 Solutions
 
ravichand-sqlAuthor Commented:
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!!!
0
 
Tony303Commented:
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

0
 
ravichand-sqlAuthor Commented:
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,
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
SharathData EngineerCommented:
Create a function like this if possible to calculate the hours difference between two dates excluding the weekends.
CREATE FUNCTION dbo.GetHourDifference (@start DATETIME, @end DATETIME)  
RETURNS INT AS
BEGIN
DECLARE @Hours INT
;WITH CTE 
     AS (SELECT CAST(CONVERT(VARCHAR, @start, 101) AS DATETIME) AS startdate, 
                CASE 
                  WHEN DATEDIFF(D, @start, @end) = 0 THEN DATEDIFF(HH, @start, @end) 
                  ELSE DATEDIFF(HH, @start, CAST(CONVERT(VARCHAR, @start, 101) AS DATETIME) 
                                            + 1) 
                END                                             AS hhnutes, 
                CAST(CONVERT(VARCHAR, @end, 101) AS DATETIME)   AS enddate 
         UNION ALL 
         SELECT startdate + 1, 
                CASE 
                  WHEN startdate + 1 = enddate THEN DATEDIFF(HH, CAST(CONVERT(VARCHAR, @end, 101) AS DATETIME), @end)
                  ELSE 1440 
                END, 
                enddate 
           FROM CTE 
          WHERE startdate < enddate) 
SELECT @Hours = ISNULL(SUM(hhnutes), 0) 
  FROM CTE 
 WHERE DATEPART(DW, startdate) BETWEEN 2 AND 6 
RETURN(@Hours)
END

Open in new window

Now you can apply this function in a query to exclude records if hours difference is greater than 24 hours.
SELECT COUNT(*)
  FROM Test
 WHERE dbo.GetHourDifference(Start_Time,End_Time) < 24

Open in new window


Here is the example: http://sqlfiddle.com/#!3/b4cc2
0
 
Tony303Commented:
I like Sharath's process too.

Here is the Nested Case Statement solution.
I have demo data here too....

I hope this helps

CREATE TABLE #TimeTable
(ID INT NULL,
StartDate DateTime NULL,
EndDate DateTime NULL)


INSERT INTO #TimeTable (ID,StartDate,EndDate)
VALUES (1,'2013-11-28 13:05:06.000','2013-11-29 14:30:06.000'),
(2,'2013-11-20 09:10:06.000','2013-11-22 14:30:06.000'),
(3,'2013-11-25 13:05:06.000','2013-11-26 09:30:06.000'),
(4,'2013-11-17 23:05:06.000','2013-11-18 01:30:06.000')

SELECT *
FROM #TimeTable

--Build Up
SELECT CASE WHEN DATEPART(dw,StartDate) IN (1,7) THEN 0 ELSE 1 END AS StartDateNotWeekend,
CASE WHEN DATEPART(dw,EndDate) IN (1,7) THEN 0 ELSE 1 END AS EndDateNotWeekend,
CASE WHEN DATEDIFF(hh,StartDate,EndDate) < = 24 THEN 1 ELSE 0 END AS TargetLessThan24HrData,
CASE WHEN DATEPART(dw,StartDate) IN (1,7) THEN 0 
	 ELSE CASE WHEN DATEPART(dw,EndDate) IN (1,7) THEN 0 ELSE 
												CASE WHEN DATEDIFF(hh,StartDate,EndDate) < = 24 THEN 1 ELSE 0 END  
				END 
	  END AS ThisIsTheValidData
FROM #TimeTable

--FinalData
SELECT 
SUM(CASE WHEN DATEPART(dw,StartDate) IN (1,7) THEN 0 
	 ELSE CASE WHEN DATEPART(dw,EndDate) IN (1,7) THEN 0 ELSE 
												CASE WHEN DATEDIFF(hh,StartDate,EndDate) < = 24 THEN 1 ELSE 0 END  
				END 
	  END) AS ThisIsTheValidData
FROM #TimeTable

Open in new window

0
 
Scott PletcherSenior DBACommented:
The code below works correctly regardless of any date and/or language settings, such as DATEFIRST or SET LANGUAGE GERMAN.

NOTE: Code is used ONLY to produce the correct total of days efficienty, NOT to produce the correct individual or total hour difference, since a specific total hours isn't required to determine if the total is less than 24 hours.


IF OBJECT_ID('tempdb..#times') IS NOT NULL
    DROP TABLE #times
CREATE TABLE #times (
    scenario int NOT NULL,
    start_time datetime NOT NULL,
    end_time datetime NULL
    )
INSERT INTO #times
SELECT 1, '20131126 03:37 PM', '20131126 11:56 PM' UNION ALL --<24
SELECT 2, '20131126 03:37 PM', '20131127 08:36 AM' UNION ALL --<24
SELECT 3, '20131129 01:22 PM', '20131202 09:40 AM' UNION ALL --<24
SELECT 4, '20131129 01:22 AM', '20131130 11:22 PM' UNION ALL --<24
SELECT 5, '20131129 01:22 PM', '20131203 09:40 AM' -->24


SELECT
    SUM(CASE WHEN DATEDIFF(HOUR, start_time_adj, end_time_adj) < 24 THEN 1 ELSE 0 END) AS [<24_hours_count]
FROM #times
CROSS APPLY (
    SELECT DATEDIFF(DAY, 0, start_time) % 7 AS start_dayofweek, DATEDIFF(DAY, 0, end_time) % 7 AS end_dayofweek
) AS ca1
CROSS APPLY (
    SELECT CASE WHEN start_dayofweek < 5 THEN start_time
                ELSE DATEADD(WEEK, DATEDIFF(WEEK, 0, start_time) + 1, 0) END AS start_time_adj,
           CASE WHEN end_dayofweek >= 5 THEN DATEADD(DAY, -2, DATEADD(WEEK, DATEDIFF(WEEK, 0, start_time) + 1, 0))
                WHEN DATEDIFF(DAY, start_time, end_time) >= 4 THEN end_time
                WHEN start_dayofweek > end_dayofweek THEN DATEADD(DAY, -2, end_time)
                ELSE end_time END AS end_time_adj                
) AS ca2
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now