-->测试数据: @A declare @A table (客户名 varchar(4),付款金额 smallint,付款日 datetime) insert into @A select '张xx',1000,'2007-10-1' union all select '张xx',800,'2007-10-11'declare @Date datetime select @Date = max(付款日) from @Aselect 客户名, [付款金(前一周)]=sum(case when datediff(day,@Date,付款日) between (-7-datepart(weekday,@Date)) and (-datepart(weekday,@Date)) then 付款金额 else 0 end), [付款金(本周)]=sum(case when datediff(day,@Date,付款日) between (1-datepart(weekday,@Date)) and (7-datepart(weekday,@Date)) then 付款金额 else 0 end) from @A group by 客户名/* 客户名 付款金(前一周) 付款金(本周) 张xx 1000 800 */
between (-7-datepart(weekday,@Date)) and (-datepart(weekday,@Date)) 改为: between (-6-datepart(weekday,@Date)) and (-datepart(weekday,@Date))你的提问有问题,如果加两条数据: 关xx 2000 2007-10-07 关xx 500 2007-10-14 如何确定“本周”?另外,老美以星期日为每周的第一日(数据库默认就是),中国大多以星期一为每周的第一日。
declare @A table (客户名 varchar(4),付款金额 smallint,付款日 datetime)
insert into @A
select '张xx',1000,'2007-10-1' union all
select '张xx',800,'2007-10-11'declare @Date datetime
select @Date = max(付款日) from @Aselect
客户名,
[付款金(前一周)]=sum(case when datediff(day,@Date,付款日) between (-7-datepart(weekday,@Date)) and (-datepart(weekday,@Date)) then 付款金额 else 0 end),
[付款金(本周)]=sum(case when datediff(day,@Date,付款日) between (1-datepart(weekday,@Date)) and (7-datepart(weekday,@Date)) then 付款金额 else 0 end)
from @A
group by 客户名/*
客户名 付款金(前一周) 付款金(本周)
张xx 1000 800
*/
改为:
between (-6-datepart(weekday,@Date)) and (-datepart(weekday,@Date))你的提问有问题,如果加两条数据:
关xx 2000 2007-10-07
关xx 500 2007-10-14
如何确定“本周”?另外,老美以星期日为每周的第一日(数据库默认就是),中国大多以星期一为每周的第一日。