CODEWITHSUNDEEP
sql-server-2008
usp
usp

Reputation: 797

SQL Server query to retrieve the latest records for a given set of IDs

I have a table like this:

ModuleID   TimeStamp           Value   TotalPerDay
1          8/5/2012 9:00 PM    0.25     0.25
2          8/5/2012 9:00 PM    0.15     0.15
3          8/5/2012 9:00 PM    0        0
4          8/5/2012 9:00 PM    1.25     1.25
1          8/5/2012 10:00 PM   0.05     0.30
3          8/5/2012 10:00 PM   0.89     0.89
4          8/5/2012 10:00 PM   0.14     1.39
4          8/5/2012 11:00 PM   0.70     2.09
4          8/5/2012 12:00 PM   0.43     2.52
...

And I have 15000 modules updating this table every hour. In addition, at midnight, the TotalPerDay values get reset.

I am struggling with a query that should pull the latest TotalPerDay value for a given set of ModuleID's.

I tried using a GROUP BY clause on ModuleID. But how can I retrieve the latest TotalPerDay value without using an aggregating function?

By the way, I am using SQL Server 2008.

Thanks in advance!

Upvotes: 0

Views: 98

Answers (2)

Void Ray
Void Ray

Reputation: 10219

If I understand your question correctly, then this should give you the latest total per day by module (if time stamp is unique):

; with MyCte (moduleId, TimeStamp)
AS(
    select moduleId, max(TimeStamp)
    from modules
    group by moduleId
)
select * from modules a
join MyCte b
on a.moduleId = b.moduleId
    and a.TimeStamp = b.TimeStamp

Based on your comments to Gordon, you are looking for a SUM per Day per module? If yes, then here you go:

select ModuleId, 
       convert(varchar,cast(TimeStamp as datetime),101),
       sum(totalPerDay) as GrandTotal
from modules
group by ModuleId, convert(varchar,cast(TimeStamp as datetime),101)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271023

So the query you want is:

select moduleid, sum(value) as UpdatedTotalPerDay
from t
where moduleid in ( . . . )
group by moduleid

What is wrong with this query? If you have an index on moduleid, the performance should be good.

If this has history, then you probably also want the condition

where cast(timestamp as date) = cast(getdate() as date)

Upvotes: 1

Related Questions