SQLTeam.com | Weblogs | Forums

Suming previous dates data with current date

sql2008r2

#1

Hi all,

Can somebody please help with this query, i would like to sum previous days demand column with the current days demand

Day Demand ColumnWanted Outcome
Monday 1775 Null 1775
Tuesday 1695 Monday+Tuesday(Demand) 3470
Wednesday 1315 Monday+Tuesday+Wednesday(Demand) 4785
.

create table T (
[Demand] int
,[Day] varchar(255)
,[Date]datetime
)

insert into T (Demand,Day,Date)
values
(1775,'Monday','2015-10-05 00:00:00.000')
,(1695,'Tuesday','2015-10-06 00:00:00.000')
,(1315,'Wednesday','2015-10-07 00:00:00.000')
,(1060,'Thursday','2015-10-08 00:00:00.000')
,(1725,'Friday','2015-10-09 00:00:00.000')

select * from t
Result

Demand Day Date
1775 Monday 2015-05-10 00:00:00.000
1695 Tuesday 2015-06-10 00:00:00.000
1315 Wednesday 2015-07-10 00:00:00.000
1060 Thursday 2015-08-10 00:00:00.000
1725 Friday 2015-09-10 00:00:00.000

Desired Result

Demand Day Date DesiredColumn
1775 Monday 2015-05-10 00:00:00.000 1775
1695 Tuesday 2015-06-10 00:00:00.000 3470
1315 Wednesday 2015-07-10 00:00:00.000 4785
1060 Thursday 2015-08-10 00:00:00.000 5845
1725 Friday 2015-09-10 00:00:00.000 7570

Thanks in advance


#2
SELECT    T.* , DesiredColumn
FROM    T 
    CROSS APPLY
    (
        SELECT    DesiredColumn = SUM(Demand)
        FROM    T x
        WHERE    x.[Date]    <= T.[Date]
    ) C

#3

Thank you @Khtan