Multiple "Top 1"'s

Question

Multiple "Top 1"'s

brett.y

Ten Centuries

Points: 1310
More actions
May 12, 2011 at 2:19 am

#240092

I have a table containing kWh's where each new row increments in value every 1 minute so the data on a graph would be a 45 degree line from 0 to infinity.
I want a query to grab a period of time (X) and in smaller chunks (Y) deduct the latest result Y from the earliest result Y giving the amount used in the period Y, as many results need to fill the period X. I can do this as a one off result:
select NIGHT-MORNING from
(select top 1 d_wheat_usage as MORNING
from ap7_wheat_usage
where convert(varchar,t_stamp,103)= convert(varchar,dateadd(dd,-1,getdate()),103)
order by t_stamp asc) A
cross join
(select top 1 d_wheat_usage AS NIGHT
from ap7_wheat_usage
where convert(varchar,t_stamp,103)= convert(varchar,dateadd(dd,-1,getdate()),103)
order by t_stamp desc) B
But how do I do this to get multiple Top1's.
say I take a week as my X period and I want hourly difference's (9:59-9:01 & 10:59-10:01 & 11:59-11:01) etc to give 168 hourly results, each result the difference between the end of the hour and the beginning of the hour?
It works with the SUM where I can have WHERE period of a month and GROUP BY day of t_stamp to give multiple results but cannot do this with top 1. Hope this is clear cause I need help:-)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

brett.y Ten Centuries Points: 1310 More actions · Answer 1

EDIT: I have found a method that works but I would still like to know if this is possible on timestamps (latest and earliest per sub-period).

My select is now (MAX(value of kWh)-MIN(value of kWh)) rather than Top 1 (Top 1 would need doing twice ASC and DESC).

So I am using the value kWh column rather than trying to break up into timestamp sub-periods.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

brett.y (5/12/2011)
EDIT: I have found a method that works but I would still like to know if this is possible on timestamps (latest and earliest per sub-period).
My select is now (MAX(value of kWh)-MIN(value of kWh)) rather than Top 1 (Top 1 would need doing twice ASC and DESC).
So I am using the value kWh column rather than trying to break up into timestamp sub-periods.

Unless you're absolutely guaranteed to have timestamps on the hour and at minute 59 for every hour, the MAX/MIN method you used is probably the safest. I will suggest, however, that your calulations are missing a minute of usage... you should be subtracting (as an example) the 10:00 reading from the 11:00 reading and NOT subtracting it from the 10:59 reading. That will also make your queries a whole lot easier, IMHO.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)