Calculate Total Hours Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculate Total Hours Problem

Author

Topic

asifchouhan
Starting Member

3 Posts

Posted - 2010-07-06 : 04:09:07

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

asifchouhan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 04:57:57

So what is the expected result using the sample data posted above?
For example, what is the ignition state between 13:40:43.670 and 2010-07-05 13:41:48.720 ?

N 56°04'39.26"
E 12°55'05.63"

asifchouhan
Starting Member

3 Posts

Posted - 2010-07-06 : 05:20:46

Expected Result Calculate Where Total Hours Ignition was 1

Regards

Asif

asifchouhan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 05:23:12

So what is the expected result using the sample data posted above?
And how would you want to present the result?

N 56°04'39.26"
E 12°55'05.63"

asifchouhan
Starting Member

3 Posts

Posted - 2010-07-06 : 05:27:19

2010-07-05 13:38:33.560 1
2010-07-05 13:39:38.623 1

Ignition was on 2 min

2010-07-05 13:40:43.670 1

Ignition was on 1 min

2010-07-05 13:43:05.767 1
2010-07-05 13:43:59.797 1

Igition was on 2 min

2010-07-05 13:46:08.907 1

Ignition was on 1 min

So Total min ignition was on is 6 mins

Regards
Asif

asifchouhan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 05:50:04

SELECT SUM(CAST(Ignition AS TINYINT))
FROM YourTableNameHere

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 05:50:34

SELECT COUNT(*) FROM YourTableNameHere WHERE Ignition = 1

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-06 : 05:57:00

[code]
declare @sample table
(
Modified DateTime ,
Ignition int
)
insert into @sample
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 Modified, Ignition, row_no = row_number() over (order by Modified)
from @sample
)
select sum(ceiling(datediff(second, c1.Modified, c2.Modified) / 60.0))
from cte c1
inner join cte c2 on c1.row_no = c2.row_no - 1
and c2.Ignition = 1
where c1.Ignition = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources