sql server programming: Finding Peak -- sql server programming

Re: Finding Peak

rdjabarov
6/13/2005 9:36:01 PM

Maybe this will work?

select min(p1.[DateTime]), max(p2.[Amount]) from dbo.peaks p1
inner join (select [DateTime], [Amount]
from dbo.peaks) p2 on p1.[DateTime] = p2.[DateTime]
group by convert(char(10), p1.[DateTime], 101)

[quoted text, click to view]

Re: Finding Peak

Steve Kass
6/14/2005 1:40:49 AM

This solution will give results that do not match
rows of the table. If there are readings every hour,
min(p1.[DateTime[) will always be 0:00, and
max(p2.Amount]) will not necessarily be the
amount at 0:00.

A solution like this is possible:

select min(dateTimeCol) as dateTimeCol, amount
from (
select dateTimeCol, amount
from yourTable as T1
where amount = (
select max(amount)
from yourTable as T2
where T2.dateTimeCol >= dateadd(day,datediff(day,0,T1.dateTimeCol),0)
and T2.dateTimeCol < dateadd(day,datediff(day,0,T1.dateTimeCol),1)
)
) as T
group by dateadd(day,datediff(day,0,T.dateTimeCol),0), amount

SK

[quoted text, click to view]

Re: Finding Peak

rdjabarov
6/17/2005 9:27:06 AM

Well, my solution was tested with many more rows that are given by the
original poster with readings every minute as well as with significan breaks
between the intervals. How do you gather that it will not work?

[quoted text, click to view]

Re: Finding Peak

Steve Kass
6/17/2005 2:26:58 PM

Here is a repro that includes your query. The table dbo.peaks
contains two rows, and your query returns a one-row result,
but the row returned is not in the original table.

As far as I can tell, your query will always return the
earliest datetime for each day next to the largest amount
for each day, regardless of whether the largest amount
occured at the earliest time or not. I don't think your
join does anything, either. You should get the same result
(but still not the correct one) with

select min([DateTime]), max([Amount])
from peaks
group by convert(char(10),p1.[DateTime],101)

but I didn't think this part out completely.

SK

create table peaks (
[DateTime] smalldatetime,
[Amount] int
)
insert into peaks values ('2005-01-01T10:00:00', 10)
insert into peaks values ('2005-01-01T20:00:00', 50)
go

select [DateTime], Amount from dbo.peaks

select min(p1.[DateTime]), max(p2.[Amount]) from dbo.peaks p1
inner join (select [DateTime], [Amount]
from dbo.peaks) p2 on p1.[DateTime] = p2.[DateTime]
group by convert(char(10), p1.[DateTime], 101)
go

/* Output:
DateTime Amount
------------------------------------------------------ -----------
2005-01-01 10:00:00 10
2005-01-01 20:00:00 50

------------------------------------------------------ -----------
2005-01-01 10:00:00 50

*/

drop table peaks

[quoted text, click to view]

Re: Finding Peak

rdjabarov
7/5/2005 11:25:04 AM

....and this is EXACTLY what the poster asked for, and this is how I
interpreted it (language barier, you know ;)): Retrieve the PEAK amount for
each day.

The answer I originally gave is solely based on this interpretation. If
this interpretation is incorrect, then and only then we can continue this
debate. Otherwise, - I don't see a point beating this horse ;)

[quoted text, click to view]

Re: Finding Peak

--CELKO--
7/5/2005 1:29:39 PM

CREATE TABLE Samplings
(sample_time DATETIME NOT NULL PRIMARY KEY,
sample_amt INTEGER NOT NULL);

SELECT C1.cal_date, S1.sample_time, S1.sample_amt
FROM Samplings AS S1, Calendar AS C1
WHERE S1.sample_time BETWEEN C1.cal_date AND DATEADD(DAY, C1.cal_date,
1)
AND NOT EXISTS
(SELECT *
FROM Samplings AS S2
WHERE S2.sample_time BETWEEN C1.cal_date AND DATEADD(DAY,
C1.cal_date, 1)
AND S2.sample_amt > S1.sample_amt
AND S2.sample_time < S1.sample_time);

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.