Aggregate functions based on date time

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)

Aggregate functions based on date time

Author

Topic

mezicx
Starting Member

2 Posts

Posted - 2009-11-25 : 03:55:08

Hello to all i hope someone can help with this as im really stumped as to how to solve this problem.

Basically i have a simple table with a value and a time stamp that is automatically populated from electricity meters. The value is always the current amount of energy used through the meter eg.

VALUE, TIMESTAMP
25 25/10/2009 12:01
55 25/10/2009 12:11
62 25/10/2009 12:21

What i need to know is the total amount of energy used between two given dates. I know that i can achieve this simply by using the MAX(VALUE) - MIN(VALUE) between the given times, however my problem is further complicated by the fact that i need to know how much energy is used between the hours of 12 midnight and 7am and also for the rest of the day. As the spread can be over several days all the solutions i have thought of up to now don't work and i am wondering if this is even possible with SQL.

Please Help

Thanks in advance

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-11-25 : 14:02:02

Something like this?

create table MeterReadings (MeterReadings_id uniqueidentifier, Meters_ID uniqueidentifier, ValueMeter integer, DateReading datetime, DateOnly char(23), TimeOnly char(8))

insert into MeterReadings (MeterReadings_id, Meters_ID, ValueMeter, DateReading)
Select newid(), '3803BC40-08FE-4343-B2CA-DCF6A3C878A8', 15, '2009-01-09 04:30:00.000' UNION ALL
Select newid(), '3803BC40-08FE-4343-B2CA-DCF6A3C878A8', 25, '2009-01-09 06:30:00.000' UNION ALL
Select newid(), '3803BC40-08FE-4343-B2CA-DCF6A3C878A8', 70, '2009-01-09 12:30:00.000' UNION ALL
Select newid(), '3803BC40-08FE-4343-B2CA-DCF6A3C878A8', 100, '2009-01-09 13:30:00.000' UNION ALL
Select newid(), '3803BC40-08FE-4343-B2CA-DCF6A3C878A8', 123, '2009-01-10 04:30:00.000' UNION ALL
Select newid(), '3803BC40-08FE-4343-B2CA-DCF6A3C878A8', 190, '2009-01-10 06:30:00.000' UNION ALL
Select newid(), '3803BC40-08FE-4343-B2CA-DCF6A3C878A8', 234, '2009-01-10 12:30:00.000' UNION ALL
Select newid(), '3803BC40-08FE-4343-B2CA-DCF6A3C878A8', 250, '2009-01-10 13:30:00.000' UNION ALL
Select newid(), '3803BC40-08FE-4343-B2CA-DCF6A3C878A8', 302, '2009-01-10 14:30:00.000'

select * from MeterReadings order by datereading asc

update MeterReadings set timeonly = CONVERT(CHAR(8),datereading,8),
dateonly = CONVERT(CHAR(8),datereading,112) FROM MeterReadings

select * from MeterReadings order by datereading asc

select meters_id, dateonly, MMAX.MaxValue, MMIN.MinValue, MMAX.MaxValue-MMIN.MinValue as result from MeterReadings m
cross apply(select max(valuemeter) As MaxValue from MeterReadings m2 where m2.meters_id = m.meters_id
AND m2.dateonly = m.dateonly
AND m2.timeonly > '00:00:00' and m2.timeonly < '07:00:00') MMAX
cross apply(select min(valuemeter) As Minvalue from MeterReadings m3 where m3.meters_id = m.meters_id
AND m3.dateonly = m.dateonly
AND m3.timeonly > '00:00:00' and m3.timeonly < '07:00:00') MMIN
where datereading >= '2009-01-09 00:00:00.000'
AND datereading <= '2009-01-11 00:00:00.000'
group by meters_id, dateonly, MMAX.MaxValue, MMIN.MinValue

mezicx
Starting Member

2 Posts

Posted - 2009-12-02 : 08:22:43

Thanks for the reply, i have to addmit this is beyond my understanding so far. however i have have solved the problem in a workable fashion by using the group by clause and the day of year. I have crafted a function that the turns a query into to a table and then sums from that. Just by trying to pick apart your solution i have learnt alot.

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-12-02 : 09:23:47

not a problem - would like to see your solution - maybe I would learn something too ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources