SQL Server 2008 Sum and Select Max Only?

Question

I am trying to understand how to do a Sum when I need to only use the Max/top values for a date period. This is kind of like SQL Server: SELECT only the rows with MAX(DATE) but not exactly. Thanks for reading.

declare @tbl Table ( tableid int , eventdate date,  valuec char(5) , valued int , recordedwhen datetime2(3) )
insert into @tbl values ( 0 , '2012-03-22' , '11111' , 3 , '2012-03-23 17:21:01.083' )
insert into @tbl values ( 1 , '2012-03-22' , '22222' , 3 , '2012-03-23 17:21:01.083' )
insert into @tbl values ( 2 , '2012-03-22' , '22222' , 4 , '2012-03-23 18:21:01.083' )
insert into @tbl values ( 3 , '2012-03-22' , '22222' , 5 , '2012-03-23 18:21:01.083' )

select 
        eventdate, valuec , sum(valued) as valuedSum , recordedwhen
        from 
        @tbl
        group by eventdate, valuec, recordedwhen

I get three rows. But what I actually want is this:

--   
--   eventdate  valuec valuedSum   recordedwhen
--   ---------- ------ ----------- ----------------------
--   2012-03-22 11111  3           2012-03-23 17:21:01.08
--   2012-03-22 22222  9           2012-03-23 18:21:01.08

I do not want the row where tableid=1 because that is part of an older batch of data. The newer rows represent a later insert (all recordedwhen values with the same datetime are the same for a batch)

So if a new row was inserted like this:

insert into @tbl values ( 4 , '2012-03-22' , '22222' , 6 , '2012-03-24 18:21:01.083' )

Then the data would should like this:

--   eventdate  valuec valuedSum   recordedwhen
--   ---------- ------ ----------- ----------------------
--   2012-03-22 11111  3           2012-03-23 17:21:01.08
--   2012-03-22 22222  6           2012-03-24 18:21:01.08

score 2 · Accepted Answer · answered Mar 26 '12 at 20:15

You basically need two selects. You can do either an inner select, or CTE. The following produces the result you desire

create table #tbl ( tableid int , eventdate date,  valuec char(5) , valued int , recordedwhen datetime2(3) )
insert into #tbl values ( 0 , '2012-03-22' , '11111' , 3 , '2012-03-23 17:21:01.083' )
insert into #tbl values ( 1 , '2012-03-22' , '22222' , 3 , '2012-03-23 17:21:01.083' )
insert into #tbl values ( 2 , '2012-03-22' , '22222' , 4 , '2012-03-23 18:21:01.083' )
insert into #tbl values ( 3 , '2012-03-22' , '22222' , 5 , '2012-03-23 18:21:01.083' )
insert into #tbl values ( 4 , '2012-03-22' , '22222' , 6 , '2012-03-24 18:21:01.083' )

;
with t (eventdate, valuec, maxrecordedwhen)
as (select eventdate, valuec, max(recordedwhen)
    from #tbl
    group by eventdate, valuec)
select t.eventdate, t.valuec, sum(valued) as valuedsum, maxrecordedwhen
from t
    join #tbl on t.eventdate = #tbl.eventdate and t.valuec = #tbl.valuec
where t.maxrecordedwhen = #tbl.recordedwhen
group by t.eventdate, t.valuec, t.maxrecordedwhen

drop table #tbl

Notice that the maximum batch date is found in the CTE and then used to filter the summation result.

This looks great. Still trying to understand why you added the Where clause instead of adding an additional Join condition, is there an optimization there I am not seeing? — Snowy, Mar 26 '12 at 20:54
I believe both would produce the same execution plan. It just made sense to me to use the `where` clause. — Joel, Mar 28 '12 at 17:19

SQL Server 2008 Sum and Select Max Only?

1 Answers1