CODEWITHSUNDEEP
sql
Brad
Brad

Reputation: 21160

How do I query for a max value and return date found

I am trying to query for a maximium value from 5am to 5am the next morning. I would also like to have the start of query date in the results.

here is what I have so far

 Select    Max(Value) as RWQ22003DTDDS  from History     
 WHERE Datetime>='2009-08-21 05:00:00'
 AND Datetime<='2009-08-22 05:00:00' and Tagname ='RWQ22003DTDDS'

I would like the date of "2009-08-21" to be in the results.

datetime,   value
------------------
2008-08-21,  2216    
2008-08-20,  4312

etc. and to do this for 7 days previous

UPDATE :

here's another approch I came up with

        declare @dec int
declare @SqlQry as varchar(4000)
declare @dd  as nvarchar(50) 
declare @ResolvedQry as varchar(4000)
set @dec = 0

set @SqlQry =''
WHILE (@dec <= 7)
        BEGIN

set @dd = cast(datepart(mm,getdate()-@dec)as nvarchar) +'/'+ 
          cast(datepart(dd,getdate()-@dec)as nvarchar) +'/'+  
          cast(datepart(yyyy,getdate()-@dec)  as nvarchar)+' 06:00:00'


set @ResolvedQry = ' Select  cast(   convert(datetime,'''+@dd+''',102) as datetime)    as [Date],  
                     Max(Value) as RWQ22003DTDDS  from History  
                     WHERE Datetime>='''+   convert(varchar, dateadd(mi,5,convert(datetime,@dd,102)))    +
                     ''' and Datetime<='''+   convert(varchar, dateadd(mi,-5,convert(datetime,@dd,102)+1))    +'''
                     and Tagname =''RWQ22003DTDDS'''

  if(@dec <7)
begin
set @ResolvedQry =@ResolvedQry + ' union'
end

set @SqlQry =  @SqlQry + @ResolvedQry 

set @dec = @dec + 1

END

 set  @SqlQry ='select * from ( ' + @SqlQry + ') as dt order by [Date] desc'
print  @SqlQry
exec(@SqlQry)

results:

  Date                RWQ22003DTDDS
------------------- ----------------------
Aug 21 2009  5:00AM 3586
Aug 20 2009  5:00AM 7233
Aug 19 2009  5:00AM 9099
Aug 18 2009  5:00AM 9099
Aug 17 2009  5:00AM 8909
Aug 16 2009  5:00AM 8516
Aug 15 2009  5:00AM 8064
Aug 14 2009  5:00AM 7437

Comments?

Upvotes: 0

Views: 320

Answers (4)

Emre Aydinceren
Emre Aydinceren

Reputation: 96

SELECT CONVERT(CHAR(10), [datetime] ,110) , MAX([value])

.....

GROUP BY CONVERT(CHAR(10), [datetime] ,110)

Upvotes: 0

Steve Kass
Steve Kass

Reputation: 7184

Here is a SQL Server solution for what I think you want. It shouldn't be hard to adapt to another dialect of SQL.

Notes: Because of the LEFT join, there will be a result for days with no History; I've changed <= to < so that rows where "Datetime" falls at exactly 5:00 am will only be counted in one day.

create table Seven(
  daysBack int primary key
);
insert into Seven values
  (0),(1),(2),(3),(4),(5),(6);

declare @today date = cast(current_timestamp as date);

select
  dateadd(day,-daysBack,@today) as QueryDateFrom,
  Max(Value) as RWQ22003DTDDS
from Seven left outer join History
on "Datetime" >= dateadd(day,-daysBack,@today)
and "Datetime" < dateadd(day,1-daysBack,@today)
group by dateadd(day,-daysBack,@today)

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562388

I solve this kind of query this way:

CREATE TEMPORARY TABLE Timespan (
 Start DATETIME,
 End DATETIME
);

INSERT INTO Timespan VALUES
 ('2009-08-21 05:00:00', '2009-08-22 05:00:00'),
 ('2009-08-20 05:00:00', '2009-08-21 05:00:00'),
 ('2009-08-19 05:00:00', '2009-08-20 05:00:00'),
 ('2009-08-18 05:00:00', '2009-08-19 05:00:00'),
 ('2009-08-17 05:00:00', '2009-08-18 05:00:00'),
 ('2009-08-16 05:00:00', '2009-08-17 05:00:00'),
 ('2009-08-15 05:00:00', '2009-08-16 05:00:00');

Select h1.Value as RWQ22003DTDDS, h1.Datetime
FROM Timespan t JOIN History h1 ON 
  (h1.Datetime BETWEEN t.Start AND t.End AND h1.Tagname = 'RWQ22003DTDDS')
LEFT JOIN History h2 ON
  (h2.Datetime BETWEEN t.Start AND t.End AND h2.Tagname = 'RWQ22003DTDDS')
  AND (h1.Value < h2.Value OR (h1.Value = h2.Value AND h1.Id < h2.Id))
WHERE h2.Value IS NULL;

Upvotes: 1

KM.
KM.

Reputation: 103607

try this (assumes that multiple rows are ok, if the YourValue is not a PK):

SELECT
    YourTable.*
    FROM YourTable
        INNER JOIN (SELECT
                        MAX(YourValue) AS YourValue
                        FROM YourTable
                        WHERE YourDate>=_StartDateTime 
                            AND YourDate<=_EndDateTime_
                   ) dt ON YourTable.YourValue=dt.YourValue

Upvotes: 3

Related Questions