July 6, 2010 at 2:44 am
Hi all,
I have table which has columns ModifiedDateTime (datetime),Ignition (bit).
Modified Date Time Ignition
------------------- --------
2010-07-05 13:36:24.470 0
2010-07-05 13:37:28.513 0
2010-07-05 13:38:33.560 1
2010-07-05 13:39:38.623 1
2010-07-05 13:40:43.670 1
2010-07-05 13:41:48.720 0
2010-07-05 13:43:05.767 1
2010-07-05 13:43:59.797 1
2010-07-05 13:45:03.860 0
2010-07-05 13:46:08.907 1
And so on . I want to calculate Total hours Ignition was on how should I write the query on same column with respective check.
Regards
Asif
July 6, 2010 at 5:21 am
Hi Asif, could you give some more detail on what you are trying to achieve. i.e. are you trying to count how many hours per day ignition is "on" or between 2 dates etc?
Is this table inserted into at regular intervals? (it doesn t appear to have any logical distance between the date stamps)
Thanks
July 6, 2010 at 8:40 am
Hi,
I am trying to get total how many hours per day ignition is "on". It is not count. From this data
2010-07-05 13:36:24.470 0
2010-07-05 13:37:28.513 0
2010-07-05 13:38:33.560 1
2010-07-05 13:40:38.623 1
2010-07-05 13:40:43.670 1 3
2010-07-05 13:41:48.720 0
2010-07-05 13:43:05.767 1
2010-07-05 13:43:59.797 1 1
2010-07-05 13:45:03.860 0
2010-07-05 13:49:08.907 1 4
Total Ignition On is : 7 mins
July 6, 2010 at 9:24 am
Create table T1 (ModifiedDate Datetime, Ignition BIT)
INSERT INTO T1 (modifieddate,Ignition)
Select '2010-07-05 13:36:24.470', 0
UNION ALL
SELECT '2010-07-05 13:37:28.513', 0
UNION ALL
SELECT '2010-07-05 13:38:33.560' ,1
UNION ALL
SELECT '2010-07-05 13:39:38.623' ,1
UNION ALL
SELECT '2010-07-05 13:40:43.670' ,1
UNION ALL
SELECT '2010-07-05 13:41:48.720' ,0
UNION ALL
SELECT '2010-07-05 13:43:05.767' ,1
UNION ALL
SELECT '2010-07-05 13:43:59.797',1
UNION ALL
SELECT '2010-07-05 13:45:03.860' ,0
UNION ALL
SELECT '2010-07-05 13:46:08.907' ,1
Select DATEDIFF(minute,Min(modifieddate), Max(modifieddate))
from t1 where ignition=1 and Modifieddate>='2010-07-05'
July 6, 2010 at 9:26 am
Asif, we kind of need some rules here. For example, is the first instance of Ignition=1 meant to signify the engine was turned on at this time? And did it stay on until the next instance of Ignition=0? Is there a turn on and turn off time field anywhere?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 6, 2010 at 1:48 pm
Try this
Create table T (ModifiedDate Datetime, Ignition BIT)
INSERT INTO T (modifieddate,Ignition)
Select '2010-07-05 13:36:24.470', 0
UNION ALL
SELECT '2010-07-05 13:37:28.513', 0
UNION ALL
SELECT '2010-07-05 13:38:33.560' ,1
UNION ALL
SELECT '2010-07-05 13:39:38.623' ,1
UNION ALL
SELECT '2010-07-05 13:40:43.670' ,1
UNION ALL
SELECT '2010-07-05 13:41:48.720' ,0
UNION ALL
SELECT '2010-07-05 13:43:05.767' ,1
UNION ALL
SELECT '2010-07-05 13:43:59.797',1
UNION ALL
SELECT '2010-07-05 13:45:03.860' ,0
UNION ALL
SELECT '2010-07-05 13:46:08.907' ,1
WITH T3
AS (
SELECT Rank2,
DATEDIFF(ss,min(modifieddate),max(modifieddate)) AS TotalSecond --for each period
FROM (
select modifieddate
,ignition
,R+ignition+ROW_NUMBER() over(order by modifieddate DESC) AS Rank2
FROM (
select *,
ROW_NUMBER() over(order by modifieddate) AS R --generate row number
from T
) T1
WHERE T1.ignition=1
)T2
GROUP BY Rank2
)
SELECT SUM(TotalSecond) --unit as second
FROM T3
No Signature
July 6, 2010 at 3:19 pm
Greg Snidow (7/6/2010)
Asif, we kind of need some rules here. For example, is the first instance of Ignition=1 meant to signify the engine was turned on at this time? And did it stay on until the next instance of Ignition=0? Is there a turn on and turn off time field anywhere?
Yeah, it looks like your code runs once per minute, and if that is the case, you could just take a COUNT of ignition=1 and divide by 60 for number of hours.
EDIT: never mind, looked and saw that it isn't EXACTLY once per minute and so my method would not work.
July 6, 2010 at 10:07 pm
yes Ignition =1 means turn on and ignition 0 means turn off with respective modified date time
July 7, 2010 at 7:41 am
asifchouhan08 (7/6/2010)
yes Ignition =1 means turn on and ignition 0 means turn off with respective modified date time
Then try this
Create table T (ModifiedDate Datetime, Ignition BIT)
INSERT INTO T (modifieddate,Ignition)
Select '2010-07-05 13:36:24.470', 0
UNION ALL
SELECT '2010-07-05 13:37:28.513', 0
UNION ALL
SELECT '2010-07-05 13:38:33.560' ,1
UNION ALL
SELECT '2010-07-05 13:39:38.623' ,1
UNION ALL
SELECT '2010-07-05 13:40:43.670' ,1
UNION ALL
SELECT '2010-07-05 13:41:48.720' ,0
UNION ALL
SELECT '2010-07-05 13:43:05.767' ,1
UNION ALL
SELECT '2010-07-05 13:43:59.797',1
UNION ALL
SELECT '2010-07-05 13:45:03.860' ,0
UNION ALL
SELECT '2010-07-05 13:46:08.907' ,1
--drop table #T
SELECT *, ROW_NUMBER() over(order by modifieddate) AS RowN
INTO #T
FROM T
WITH T2
AS (
SELECT modifieddate
,ignition
,RowN
,RowN + ROW_NUMBER() OVER(ORDER BY modifieddate DESC) AS PeriodN
FROM #T
WHERE ignition=1
),
T3
AS (
SELECT PeriodN,
min(modifieddate) AS TurnOnTime,
max(RowN)+1 AS TurnOffRow
FROM T2
GROUP BY PeriodN
)
SELECT SUM(DATEDIFF(ss,T3.TurnOnTime,T1.modifieddate)) AS TotalSecond
FROM T3
LEFT JOIN #T T1
ON T3.TurnOffRow=T1.RowN
No Signature
July 7, 2010 at 1:17 pm
asifchouhan08 (7/6/2010)
yes Ignition =1 means turn on and ignition 0 means turn off with respective modified date time
Asif, I am going off of these rules, and thought I would offer another solution. This is probably a good time for you to stop and read this..http://www.sqlservercentral.com/articles/T-SQL/68467/. And, as Joe suggested, do some additional reading.
Anyhow, this may work for you if you can make temp tables. Also, if you are looking at some kind of continuous process, where the engine might turn on at 11:59 pm, and run until 12:01 am, but you only want to look at run times per day, you will have to deal with that. This solution does not deal with changes of days, but could easily be adapted to do so.
--Create the test table with a column for the elapse time
IF OBJECT_ID('TempDB..#ElapsedTime','u') IS NOT NULL
DROP TABLE #ElapsedTime
CREATE TABLE #ElapsedTime
(
ModifiedDateTime DATETIME,
Ignition BIT,
ElapsedSeconds INT
)
GO
--Insert the test data
INSERT INTO #ElapsedTime (ModifiedDateTime,Ignition)
SELECT '2010-07-05 13:36:24.470',0 UNION ALL
SELECT '2010-07-05 13:37:28.513',0 UNION ALL
SELECT '2010-07-05 13:38:33.560',1 UNION ALL
SELECT '2010-07-05 13:40:38.623',1 UNION ALL
SELECT '2010-07-05 13:40:43.670',1 UNION ALL
SELECT '2010-07-05 13:41:48.720',0 UNION ALL
SELECT '2010-07-05 13:43:05.767',1 UNION ALL
SELECT '2010-07-05 13:43:59.797',1 UNION ALL
SELECT '2010-07-05 13:45:03.860',0 UNION ALL
SELECT '2010-07-05 13:49:08.907',1
GO
--Create clustered index on ModifiedDateTime. The assumption
--is that they will *always* be in chronological order
CREATE CLUSTERED INDEX IX_#ElapsedTime_ModifiedDateTime
ON #ElapsedTime (ModifiedDateTime)
--Set up the local variables for use in updating the ElapsedSeconds column
DECLARE @PreviousTime DATETIME,
@ElapsedSeconds INT,
@PreviousIgnition BIT
--Set the initial values for the variables
SELECT @PreviousTime = 0
SELECT @PreviousIgnition = 0
SELECT @ElapsedSeconds = 0
--Let 'er rip
UPDATE #ElapsedTime
SET
@ElapsedSeconds = ElapsedSeconds =
--Set the elapsed time based on the rules:
--1 preceeded by a 0 = an ignition start time
--1 preceeded by a 1 = engine still running
--0 preceeded by a 1 = an ignition stop time
CASE WHEN (Ignition = 1 AND @PreviousIgnition = 1) OR
(Ignition = 0 AND @PreviousIgnition = 1)
THEN DATEDIFF(second,@PreviousTime,ModifiedDateTime)
ELSE 0
END,
--Reset @PreviousIgnition with the current ignition for
--use in the next run
@PreviousIgnition = Ignition,
--Reset @PreviousTime with the current ModifiedDateTime
--for use in the next run
@PreviousTime = ModifiedDateTime
SELECT
'Minutes ' = SUM(ElapsedSeconds)/60,
'Seconds ' = SUM(ElapsedSeconds)%60
FROM #ElapsedTime
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 13, 2010 at 1:55 pm
Something like this should work.
DECLARE @T TABLE (ModifiedDate DATETIME, Ignition BIT)
INSERT INTO @t (modifieddate,Ignition)
SELECT '2010-07-05 13:36:24.470', 0
UNION ALL
SELECT '2010-07-05 13:37:28.513', 0
UNION ALL
SELECT '2010-07-05 13:38:33.560' ,1
UNION ALL
SELECT '2010-07-05 13:39:38.623' ,1
UNION ALL
SELECT '2010-07-05 13:40:43.670' ,1
UNION ALL
SELECT '2010-07-05 13:41:48.720' ,0
UNION ALL
SELECT '2010-07-05 13:43:05.767' ,1
UNION ALL
SELECT '2010-07-05 13:43:59.797',1
UNION ALL
SELECT '2010-07-05 13:45:03.860' ,0
UNION ALL
SELECT '2010-07-05 13:46:08.907' ,1
;WITH cte
AS(
SELECT
modifieddate,
Ignition,
ROW_NUMBER() OVER(ORDER BY modifieddate ASC) AS seq
FROM @t
)
SELECT SUM(DATEDIFF(SECOND,t1.modifieddate,t2.modifieddate)) AS SecondsIgnit
FROM cte t1
INNER JOIN cte t2 ON t1.seq+1 = t2.seq
WHERE t1.ignition = 1 AND t2.Ignition = 1
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply