April 21, 2009 at 12:23 am
Hi All,
I have two date time and have to calculate difference between these date time.
But the problem is that I have to consider only business hours i.e. 7:30 to 4:30 for each day.
eg if start date is 01-01-2009 08:45:00
and end date 31-01-2009 13:12:00
then for each date I have to consider only business hour and have to leave remaining hours in date difference calculation.
Thanks
April 21, 2009 at 1:35 am
Hi,
It is something same like this:
Sometime ago Mr.Jacob Sebastian (MVP, SQL SERVER) given TSQL Challenge.
Please refer that link. I solved that Challenge and also Topper in it. You can find my profile there :).
Let me know if this is the same that you require.
Thanks,
Tejas
Tejas Shah
April 21, 2009 at 1:37 am
Try This...
Declare @edTime varchar(1000)
Declare @vTotTime int
-- Specify your End Biz hours Time here.
-- This time will join with each date to get the time difference for the specifies date
Set @edTime = '15:00:00'
Declare @t1 Table (id int identity(1,1),Dates datetime)
Insert into @t1
Select getdate()
Insert into @t1
Select getdate()
--Select Dateadd(minute,10,getdate() + 1)
Insert into @t1
Select getdate()
--Select Dateadd(minute,20,getdate() + 1)
Select @vTotTime = Sum(DateDiff(minute,Dates,Convert(datetime,Convert(varchar(20),Dates,110) + ' ' + @edTime)))
From @t1
--Select @vTotTime
Select Cast(@vTotTime /60 as varchar(10)) + ':'+ Cast(@vTotTime % 60 as varchar(10))
April 21, 2009 at 1:50 am
Hi Tejas,
I have the same problem what you have discussed in the given link.
Please guide me or send me the query for that
Thanks
April 21, 2009 at 1:55 am
Hi,
My Query is:
DECLARE @t TABLE (ID INT IDENTITY, StartDate DATETIME, EndDate DATETIME)
INSERT INTO @t (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00
INSERT INTO @t (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00
INSERT INTO @t (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45
INSERT INTO @t (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15
;with cte as(
SELECT ID,
StartDate AS OrgStartDate,
EndDate AS OrgEndDate,
StartDate,
EndDate
FROM @T
UNION ALL
SELECT t.ID,
c.OrgStartDate AS OrgStartDate,
c.OrgEndDate AS OrgEndDate,
DATEADD(d, 1, DATEADD(dd,0, DATEDIFF(dd,0,c.StartDate))) AS StartDate,
c.EndDate
FROM @t t
INNER JOIN cte c ON t.ID = c.ID
AND DATEADD(d, 1, DATEADD(dd,0, DATEDIFF(dd,0,c.StartDate))) <= c.EndDate
)
, cte2 AS(
SELECT ID,
OrgStartDate AS OrgStartDate,
OrgEndDate AS OrgEndDate,
DATENAME(dw,StartDate) AS DayName,
CASE
WHEN StartDate <= DATEADD(hh,8,DATEADD(dd,0, DATEDIFF(dd,0,c.StartDate)))
THEN DATEADD(hh,8,DATEADD(dd,0, DATEDIFF(dd,0,c.StartDate)))
ELSE StartDate
END AS StartDate,
CASE
WHEN EndDate EndDate THEN 0
ELSE DATEDIFF(mi,StartDate,EndDate)
END) / 60 AS INT) AS Hours,
CAST(SUM(CASE
WHEN StartDate > EndDate THEN 0
ELSE DATEDIFF(mi,StartDate,EndDate)
END) % 60 AS INT) AS Minutes
from cte2
WHERE DAYNAME NOT IN('Saturday', 'SUNDAY')
GROUP BY ID, OrgStartDate, OrgEndDate
Thanks,
Tejas
Tejas Shah
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply