sum up commision of weekend and add in Mon

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)

sum up commision of weekend and add in Mon

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2012-04-01 : 11:30:32

Hi,

I have a table which has commission. I want to sum up the commission of sat and sun (weekend) and add in Mon.

create table #temp1
(
date_comm datetime,
commision int,
week_day nvarchar(50)
)

insert into #temp1 values('2012-03-30 00:00:00.000',130,'Fri')
insert into #temp1 values('2012-03-31 00:00:00.000',350,'Sat')
insert into #temp1 values('2012-04-01 00:00:00.000',30,'Sun')
insert into #temp1 values('2012-04-02 00:00:00.000',40,'Mon')
insert into #temp1 values('2012-04-03 00:00:00.000',60,'Tue')
insert into #temp1 values('2012-04-04 00:00:00.000',80,'Wed')
insert into #temp1 values('2012-04-05 00:00:00.000',100,'Thur')
insert into #temp1 values('2012-04-06 00:00:00.000',330,'Fri')
insert into #temp1 values('2012-04-07 00:00:00.000',350,'Sat')
insert into #temp1 values('2012-04-08 00:00:00.000',630,'Sun')
insert into #temp1 values('2012-04-09 00:00:00.000',370,'Mon')
insert into #temp1 values('2012-04-10 00:00:00.000',230,'Tue')

My output should be:
2012-03-30 00:00:00.000 130 Fri
2012-03-31 00:00:00.000 350 Sat
2012-04-01 00:00:00.000 30 Sun
2012-04-02 00:00:00.000 420 Mon
2012-04-03 00:00:00.000 60 Tue
2012-04-04 00:00:00.000 80 Wed
2012-04-05 00:00:00.000 100 Thur
2012-04-06 00:00:00.000 330 Fri
2012-04-07 00:00:00.000 350 Sat
2012-04-08 00:00:00.000 630 Sun
2012-04-09 00:00:00.000 1350 Mon
2012-04-10 00:00:00.000 230 Tue

Regards,
Sachin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-01 : 12:03:49

[code]
select t.date_comm,
commision = case when week_day = 'Mon' then w.commision else t.commision end,
t.week_day
from #temp1 t
cross apply
(
select commision = sum(commision)
from #temp1 x
where x.date_comm >= dateadd(day, -2, t.date_comm)
and x.date_comm <= t.date_comm
) w
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sachingovekar
Posting Yak Master

101 Posts

Posted - 2012-04-01 : 12:44:44

thanks a lot khtan. It works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources