General T-SQL 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

SQL Server 2005 Forums

Transact-SQL (2005)

General T-SQL Problem

Author

Topic

sujitkar
Starting Member

4 Posts

Posted - 2009-03-12 : 08:04:04

hey guys....I am new in SQL Server. Kindly help me with the solution to the following problem-

I have one table in sql server which is configured to be updated automatically with the data from several energy_meters installed at the households. the meters send readings to the database server every 30 mins using GPRS. the columns are - meter_id, date, time, reading_value. Now I have been asked to find out the total consumption per day per household. I have no idea, how to do that.

Looking forward for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 08:18:57

SELECT meter_id, sum(reading_value) FROM Table1
GROUP BY meter_id

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

sujitkar
Starting Member

4 Posts

Posted - 2009-03-13 : 02:39:16

Thanks peso for your reply. But that wont work I suppose. Because the "reading_value" is the value till that particular moment, it just go on increasing with every reading since it is not the value for that particular period.

Please Help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 03:23:16

SELECT meter_id, max(reading_value) FROM Table1
GROUP BY meter_id

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

sujitkar
Starting Member

4 Posts

Posted - 2009-03-13 : 05:21:25

Bu Peso, this will give only the last meter_reading for each meter. it wont give the meter_reading for each day each meter.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-13 : 05:29:47

Try this

SELECT meter_id,day,sum(reading_value) FROM Table1
GROUP BY meter_id,day

This will give for each meter,each day....

Regards

Senthil.C
Willing to update...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 05:48:56

[code]SELECT meter_id,
DATEADD(DAY, DATEDIFF(DAY, 0, [date]), 0) AS theDate,
MAX(reading_value) - MIN(reading_value)
FROM Table1
GROUP BY meter_id,
DATEADD(DAY, DATEDIFF(DAY, 0, [date]), 0)
ORDER BY meter_id,
DATEADD(DAY, DATEDIFF(DAY, 0, [date]), 0) DESC[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources