0

In a view have these two dates coming from a table: 

2014-12-17 14:01:03.523 - 2014-12-20 09:59:28.783

I need to know the date diff in hours assuming that in a day i can count the hours just from 08 AM and 5 PM.

Of course saturdays and sundays must not be included.

I tried using this code inside the select but i only got the diff in days, excluding saturdays and sundays.

(DATEDIFF(HOUR, convert(datetime,t.EXT_DATAINS-2), convert(datetime,b.EXT_DATAINS-2)) + 1) 
  -(DATEDIFF(wk, convert(datetime,t.EXT_DATAINS-2), convert(datetime,b.EXT_DATAINS-2)) * 2)
  -(CASE WHEN DATENAME(dw, convert(datetime,t.EXT_DATAINS-2)) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, convert(datetime,b.EXT_DATAINS-2)) = 'Saturday' THEN 1 ELSE 0 END) differenza

Example:

2014-12-17 : 3hrs
2014-12-18 : 8hrs
2014-12-19 : 8hrs
2014-12-20 : 2hrs 
Tot        : 21hrs
marco burrometo
  • 1,055
  • 3
  • 16
  • 33
  • 1
    possible duplicate of [Calculate business hours between two dates](http://stackoverflow.com/questions/5274208/calculate-business-hours-between-two-dates) – Dan Jan 14 '15 at 14:49
  • 1
    There's no standard function to help you do this. In addition to defining your "business hours" (do lunch time count?) and holiday schedule, you have to define what happens when the range start or end outside the business hours. For example: if it goes from 3PM - 6PM, how many hours is that? How about 3AM - 10PM? – Code Different Jan 14 '15 at 14:53
  • What do you want as a result from difference between those two dates? – Giorgi Nakeuri Jan 14 '15 at 14:56
  • Could you show us what you've tried, e.g. a CTE or numbers table used to run from the start date to the end date with a `CASE` to determine the number of hours to include for each date and a `SUM` for the final result? – HABO Jan 14 '15 at 14:57
  • What will be the difference for those two dates 2014-12-17 14:01:03.523 - 2014-12-20 09:59:28.783 – Giorgi Nakeuri Jan 14 '15 at 15:02
  • It will be the working time of a machine, that always starts at 8 AM and finishes at 5 PM with no pause. – marco burrometo Jan 14 '15 at 15:04
  • :) can u say exactly what is the value you expect?? Just write difference between dates 2014-12-17 14:01:03.523 - 2014-12-20 09:59:28.783 will be ...(5 hrs, 8 hrs or ...) – Giorgi Nakeuri Jan 14 '15 at 15:21
  • "I want the difference in minutes" and "I need to know the date diff in hours" are in conflict. Can you resolve the discrepancy? Would fractional hours help? – HABO Jan 14 '15 at 15:33
  • U provided example as list. Again i can not understand what do you want to achieve. 2014-12-17 : 3hrs 3 haours is difference between which dates? 2014-12-18 : 8hrs 8 hours between wich dates? – Giorgi Nakeuri Jan 14 '15 at 15:34
  • 3, 8 ,8 and 2 hours are the effective working hours in each day of the interval. of course i want the total. It's OK Minutes a hours differential. – marco burrometo Jan 14 '15 at 15:48

2 Answers2

2

Use a Recursive CTE to get all Hours with Dates.

METHOD 1 : Get all dates with hours between FromDate and ToDate

DECLARE @FROMDATE DATETIME='2014-12-17 14:01:03.523'
DECLARE @TODATE DATETIME='2014-12-20 09:59:28.783'

;WITH CTE AS
(
   SELECT @FROMDATE FROMDATE
   UNION ALL
   SELECT DATEADD(HH,1,FROMDATE)
   FROM CTE
   WHERE FROMDATE<@TODATE 
)
SELECT ISNULL(CAST(CAST(FROMDATE AS DATE)AS VARCHAR(12)),'Tot')FROMDATE,
CAST(COUNT(FROMDATE)AS VARCHAR(4))+'hrs' [HOURS] 
FROM CTE
WHERE DATEPART(HH,FROMDATE) BETWEEN 9 AND 16
AND DATENAME(DW,FROMDATE)<>'SATURDAY' AND DATENAME(DW,FROMDATE)<>'SUNDAY'
GROUP BY CAST(FROMDATE AS DATE)
WITH ROLLUP

METHOD 2 : Gets missing dates between FromDate and ToDate with 8 as hardcoded as Hrs

This method will be more implementable - Performance Tuned

DECLARE @FROMDATE DATETIME='2014-12-17 14:01:03.523'
DECLARE @TODATE DATETIME='2014-12-20 09:59:28.783'

;WITH CTE AS
(
   -- Get missing dates between FromDate and ToDate
   SELECT  DATEADD(DAY,1,@FROMDATE) FROMDATE,8 HRS
   UNION ALL
   SELECT DATEADD(DAY,1,FROMDATE),8
   FROM CTE
   WHERE FROMDATE < DATEADD(DAY,-1,@TODATE) 
)
,CTE2 AS
(
   -- Gets the Hours for FromDate
   SELECT CAST(@FROMDATE AS DATE) DATES, CAST(CAST(DATEDIFF
   (
      MINUTE,@FROMDATE,CAST(CAST(CAST(@FROMDATE AS DATE) AS VARCHAR(12))+' 17:00:00' AS DATETIME)
   )AS NUMERIC(18,2))/60 AS DECIMAL(18,0)) HRS
   WHERE DATENAME(DW,@FROMDATE)<>'SATURDAY' AND DATENAME(DW,@FROMDATE)<>'SUNDAY'

   UNION ALL

   -- Select Hours in between dates
   SELECT CAST(FROMDATE AS DATE) NEWDATE,HRS
   FROM CTE
   WHERE DATENAME(DW,FROMDATE)<>'SATURDAY' AND DATENAME(DW,FROMDATE)<>'SUNDAY'

   UNION ALL

   -- Select Hours for ToDate
   SELECT CAST(@TODATE AS DATE), CAST(CAST(DATEDIFF
   (
      MINUTE,CAST(CAST(CAST(@TODATE AS DATE) AS VARCHAR(12))+' 08:00:00' AS DATETIME),@TODATE
   )AS NUMERIC(18,2))/60 AS DECIMAL(18,0))
   WHERE DATENAME(DW,@TODATE)<>'SATURDAY' AND DATENAME(DW,@TODATE)<>'SUNDAY'
)
-- Use ROLLUP to find the sum of Hours and show it in last row
SELECT ISNULL(CAST(DATES AS VARCHAR(20)),'Tot')DATES,
CAST(SUM(HRS)AS VARCHAR(4))+'hrs' HRS
FROM CTE2
GROUP BY DATES
WITH ROLLUP
Sarath Subramanian
  • 20,027
  • 11
  • 82
  • 86
0

@marco burrometo

Create a static table which will have all the calendar functionality like holiday functionality,saturday and sunday is a holiday. It will help you a lot.

karan arora
  • 176
  • 9