Suming DateDiff
-
Hi,
I have 2 columns called StopTime and StartTime.
StopTime,StartTime
10:05:55, 10:10:55
10:45:55, 11:20:55
11:35:49, 12:49:49
13:49:55, 14:18:55
To get the sum of the difference bewteen the 2 time columns i can use
SUM(DATEDIFF(MI,STOPTIME,STARTTIME)) AS 'Downtime'.
What i need to do is get the downtime for an hour i.e how long was it down between 10:00 - 11:00.
The answer to the above would be 20 min i.e
DateDiff(10:05:55, 10:10:55) = 5
DateDiff(10:45:55, 11:20:55) = 15 as i only want the downtime for 10:00 to 11:00
Is there any way i can do this?
Thanks in advance
Shane
-
Shane,
Try this:
declare @time1 datetime
declare @time2 datetime
set @time1 = '10:00:00'
set @time2 = '11:00:00'
select SUM(DATEDIFF(MI,stoptime, case when starttime <= @time2 then starttime else @time2 end)) AS 'Downtime'
from
where stoptime = @time1
Hope that helps,
-
Shane,
Do you want only the downtime between 10:00 and 11:00 or do you want the downtime for every hour of a given day? Also, is there a date associated with the times to discriminate by day?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
DECLARE @from datetime, @to datetime
SET @from = '2006-04-11 10:00:00'
SET @to = '2006-04-11 11:00:00'
SELECT COUNT(*)
FROM (SELECT DATEADD(minute,n.number,@from) AS [Time]
FROM master.dbo.spt_values n
WHERE n.type = 'P'
AND DATEADD(minute,n.number,@from) <= @to) t
INNER JOIN
a ON t.[Time] >= a.StopTime AND t.[Time] <= a.StartTime
spt_values is an undocumented table and should be replaced with a permanent Numbers table
Far away is close at hand in the images of elsewhere.
Anon. -
Hi All,
Thanks for the responses.
I think i have a solution based on grambowk T-SQL code(see below).
I am not sure if what i have done is the best way of doing it but it seems to work.
Basically what i am try to do is, given two datetime's, sum the difference between the stoptime and the starttime (i.e how long the machine was down for(downtime)) between the two dates.
Sounds fairly straight forward but the problem occours if these times span across the hour.
example
StopTime,StartTime
23-Aug-2004 09:45:55, 23-Aug-2004 10:10:55
23-Aug-2004 10:40:55, 23-Aug-2004 11:20:55
Downtime for 10:00 to 11:00 should be (10 + 20 = 30)
Below is the code i am currently using and as i said it seems to be working fine but if anyone has a better way of doing it then i am open to suggestions.
Regards
Shane
declare @Stop_IN datetime
declare @Start_IN datetime
set @Stop_IN = '23-Aug-2004 14:00'
set @Start_IN = '23-Aug-2004 15:00'
select
SUM
(
DATEDIFF
(MI,
(case when stoptime <= @Stop_IN then @Stop_IN else stoptime end),
(case when starttime <= @Start_IN then starttime else @Start_IN end)
 
)
AS 'Downtime'
from tbldowntime
and
(
(stoptime <= @Start_IN and stoptime >= @Stop_IN)
OR
(starttime >= @Stop_IN and starttime <= @Start_IN)
)
-
declare @times TABLE (
STOPTIME datetime,
STARTTIME datetime)
INSERT INTO @times
SELECT '10:05:55', '10:10:55'
UNION
SELECT '10:45:55', '11:20:55'
UNION
SELECT '11:35:49', '12:49:49'
UNION
SELECT '13:49:55', '14:18:55'
SELECT datepart(hh, STOPTIME) as HourStop, SUM(DATEDIFF(MI,STOPTIME,STARTTIME)) AS 'Downtime'
FROM (
SELECT STOPTIME,
Case when datepart(hh, STOPTIME) < datepart(hh, StartTime) then dateadd(hh, datepart(hh, STOPTIME)+1, 0) else STARTTIME end as STARTTIME
from @times
UNION
SELECT Case when datepart(hh, STOPTIME) < datepart(hh, StartTime) then dateadd(hh, datepart(hh, StartTime), 0) else STOPTIME end as STOPTIME,
STARTTIME
from @times ) DT
GROUP BY datepart(hh, STOPTIME)
_____________
Code for TallyGenerator -
And if you may possibly have more than 1 hour stoppage time:
declare @times TABLE (
STOPTIME datetime,
STARTTIME datetime)
INSERT INTO @times
SELECT '10:05:55', '10:10:55'
UNION
SELECT '10:45:55', '11:20:55'
UNION
SELECT '11:35:49', '12:49:49'
UNION
SELECT '13:49:55', '18:18:55'
declare @Numbers TABLE (
NUMBER smallint)
INSERT @Numbers
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6
UNION
SELECT 7
UNION
SELECT 8
SELECT datepart(hh, STOPTIME) as Hour_BeingDown, SUM(DATEDIFF(MI,STOPTIME,STARTTIME)) AS 'Downtime'
FROM (
SELECT STOPTIME,
Case when datepart(hh, STOPTIME) < datepart(hh, StartTime) then dateadd(hh, datepart(hh, STOPTIME)+1, 0) else STARTTIME end as STARTTIME
from @times
UNION
SELECT Case when datepart(hh, STOPTIME) < datepart(hh, StartTime) then dateadd(hh, datepart(hh, StartTime), 0) else STOPTIME end as STOPTIME,
STARTTIME
from @times
UNION
SELECT dateadd(hh, datepart(hh, STOPTIME) + Number, 0) as STOPTIME,
dateadd(hh, datepart(hh, STOPTIME) + Number + 1, 0) as STARTTIME
from @times T
INNER JOIN @Numbers N on DATEDIFF(hh, STOPTIME, STARTTIME) > Number) DT
GROUP BY datepart(hh, STOPTIME)
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply