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

General SQL Server Forums

New to SQL Server Programming

Query

Author

Topic

jfthreed
Starting Member

7 Posts

Posted - 2010-03-29 : 11:11:05

I need to create a new database, it will store a reading every 15 minutes, I need to be able to get the difference between the readings both every hour and every 15 and 30 minutes. Not sure where to start.
If necessary I will store the difference every 15 minutes as well as the value.

If I do the latter how can I add the values ie

1:15 difference 10
1:30 difference 5
1:45 difference 1
2:00 difference 60
2:15 difference 8
2:30 difference 22
2:45 difference 3
3:00 difference 4

1 till 2 difference is 16
2 till 3 difference is 93
3 till 4 difference is 4

Please Help - Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 11:44:03

Sorry didnt understand how you got values 10,5,.. can you explain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jfthreed
Starting Member

7 Posts

Posted - 2010-03-29 : 11:51:07

Sorry will try again

I want to store energy used every 15 minutes (but not sure whether to store the avtual amount of energy used or the current value)

Once I have the data in the database evry 15 minutes, ie the 10,5 etc

I want to be able to run an sql statement that will return the total values (or difference depending on what values I store).

But although I am storing the values every 15 minutes I want to be able to return the total energy used every 30 minutes or hourly.

Hope this makes more sense - thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 11:56:53

ok. now it makes sense.
see the below logic

http://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jfthreed
Starting Member

7 Posts

Posted - 2010-03-29 : 11:57:16

I can use the command below to return the total every hour but still not sure how to total 30 minutes or get the difference (If I decide to store the actual meter value instead of the difference)

SELECT dateadd(hour, datediff(hour, 0, datetime), 0), sum(reading)
FROM latestreading15
WHERE (datetime >= '2007-10-03 00:00' AND datetime < '2020-10-03 01:00')
GROUP BY dateadd(hour, datediff(hour, 0, datetime), 0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 12:02:52

quote:
Originally posted by jfthreed

I can use the command below to return the total every hour but still not sure how to total 30 minutes or get the difference (If I decide to store the actual meter value instead of the difference)

SELECT dateadd(hour, datediff(hour, 0, datetime), 0), sum(reading)
FROM latestreading15
WHERE (datetime >= '2007-10-03 00:00' AND datetime < '2020-10-03 01:00')
GROUP BY dateadd(hour, datediff(hour, 0, datetime), 0)

did you check the logic given in link?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jfthreed
Starting Member

7 Posts

Posted - 2010-03-29 : 12:05:51

Sorry replied before I saw link, will take a look now - thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 12:06:45

cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jfthreed
Starting Member

7 Posts

Posted - 2010-03-29 : 12:08:00

OK had a quick look and I notice that your example works with SQL 2008 - unfortunately I am using Express 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 12:15:47

quote:
Originally posted by jfthreed

OK had a quick look and I notice that your example works with SQL 2008 - unfortunately I am using Express 2005

I repeat..
Understand the logic used inside the function and apply it. dont worry about creating function and etc. The only SQL 2008 specific thing I've used inside is table valued parameters. you can still use logic very easily in SQL 2000/2005

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jfthreed
Starting Member

7 Posts

Posted - 2010-03-29 : 12:17:33

OK thanks, will give it a go - sorry this is all new to me. Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 12:20:14

no probs. concentrate on below part


SELECT DATEADD(mi,DATEDIFF(mi,0,LogTime)/@SlotWidth*@SlotWidth,0) As Start , DATEADD(mi,(DATEDIFF(mi,0,LogTime)/@SlotWidth+1)*@SlotWidth-1,0) AS [End], SUM(Value)
FROM @InputTable
GROUP BY DATEADD(mi,DATEDIFF(mi,0,LogTime)/@SlotWidth*@SlotWidth,0), DATEADD(mi,(DATEDIFF(mi,0,LogTime)/@SlotWidth+1)*@SlotWidth-1,0)

and replace fields,variables with actual fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jfthreed
Starting Member

7 Posts

Posted - 2010-03-30 : 05:16:20

Perfect thanks - so much. I can now make a start with the right information - thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-30 : 13:07:11

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources