Grabbing max sampletime and value daily

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 2008 Forums

Transact-SQL (2008)

Grabbing max sampletime and value daily

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2013-06-04 : 14:04:03

I have a table that stores hourly values for disk space. My goal is to grab that last value on a daily basis for a 6 month period, the results would include one sample daily, and that one sample would be from the last sample collected.

select table_id,sampletime,sampleavg from HN_QOS_DATA_0138 where table_id = '46752'

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-04 : 15:15:32

Like this:

WITH    cte
AS ( SELECT   table_id ,
            sampletime ,
            sampleavg ,
            ROW_NUMBER() OVER ( PARTITION BY CAST(sampletime AS DATE) ORDER BY sampletime DESC ) AS RN
   FROM     HN_QOS_DATA_0138
   WHERE    table_id = '46752'
            AND sampletime > DATEADD(mm, -6, GETDATE())
 )
SELECT  table_id ,
        sampletime ,
        sampleavg
FROM    cte
WHERE   RN = 1;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 00:10:41

[code]
select table_id,sampletime,sampleavg
from
(
select table_id,sampletime,sampleavg ,
max(sampletime) over (partition by datediff(dd,0,sampletime)) AS maxdate
from HN_QOS_DATA_0138
where table_id = '46752'
and sampletime >= DATEADD(mm,DATEDIFF(mm, 0, GETDATE())-6,0)
)t
WHERE sampletime = maxdate

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2013-06-05 : 09:29:41

quote:
Originally posted by visakh16

select table_id,sampletime,sampleavg
from
(
select table_id,sampletime,sampleavg ,
max(sampletime) over (partition by datediff(dd,0,sampletime)) AS maxdate
from HN_QOS_DATA_0138
where table_id = '46752'
and sampletime >= DATEADD(mm,DATEDIFF(mm, 0, GETDATE())-6,0)
)t
WHERE sampletime = maxdate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thanks everyone, visakh16, thats what i was looking for, appreciate the feedback/support

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 13:49:23

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources