i need some help in SQL Query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

i need some help in SQL Query

Author

Topic

mohan g
Starting Member

1 Post

Posted - 2004-08-19 : 15:09:19

i need some help in building an SQL query (to show the result in a crystal report ver. 9.0)

i am doing a real time project.i have to log the statuses (ON/OFF) of each bulb continuously and i store the data in a table (i have given the sample data) sql server2000 DB table.

i want the latest 'ON' (glowing) time of each bulb. i.e the difference between the "latest" ON and OFF changetimes.

if the query is not possible to build then is there any other way to get the result and show in a crystal report ?

please help me to get the result

thanx in advance
Mohan G

BulbNo State ChangeTime

2 ON 19/08/2004 21:22:10
2 OFF 19/08/2004 21:20:10
4 ON 19/08/2004 21:18:50
4 OFF 19/08/2004 21:17:00
7 ON 19/08/2004 21:12:50
7 OFF 19/08/2004 21:12:40
9 ON 19/08/2004 21:12:30
9 OFF 19/08/2004 21:11:00
10 ON 19/08/2004 21:12:30
10 OFF 19/08/2004 21:11:00
2 ON 19/08/2004 20:22:10
2 OFF 19/08/2004 20:20:10
5 ON 19/08/2004 20:12:30
5 OFF 19/08/2004 20:11:00
7 ON 19/08/2004 20:12:50
7 OFF 19/08/2004 20:12:40
9 ON 19/08/2004 20:12:30
9 OFF 19/08/2004 20:11:00

The result should be : to show the lastest total ON time

BulbNo State UP Time (in seconds)
2 ON 120
4 ON 110
5 ON 90
7 ON 10
9 ON 90
10 ON 90

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-19 : 19:25:06

Do you have any primary key on the table?

mk_garg

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-08-19 : 19:34:35

[code]select bo.bulbno,'On' as state, abs(datediff(ss,max(bo.changetime),max(bi.changetime))) uptime
from bulblog bo
join bulblog bi on bi.bulbno = bo.bulbno
where bo.state = 'On' and bi.state='Off'
group by bo.bulbno[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources