Take the 2-minute tour ×

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!

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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)
    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)
share|improve this answer
This is exactly what I need. Thanks. –  usp Aug 7 '12 at 23:59
Hey, i tried both your queries and it seems like that second one works faster than the first! Why is that? –  usp Aug 8 '12 at 16:40
Well, they are completely different; first query should return a very last entry per day per module and consists of a CTE (like a sub-query) and a select with a join. The second query should return a sum of all totals per day per module and consists of only one select statement with an aggregate. Basically, the first query is more complex and should take about double the time. –  Void Ray Aug 8 '12 at 18:58
add comment

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)
share|improve this answer
SUM(Value) will calculate the SUM of the VALUEs for that MODULE since the beginning. I need only for a day. –  usp Aug 7 '12 at 23:35
Then use the WHERE clause to get records only from today. –  Gordon Linoff Aug 7 '12 at 23:36
Great!!! I think that should work. –  usp Aug 7 '12 at 23:48
I am sorry Gordon. I can only mark one response as answer and Dimitry's response is exactly what I am trying to do. Thanks for your help though. –  usp Aug 7 '12 at 23:58
add comment

Your Answer


