declare @start as datetime
declare @tb table(dDate datetime)
set @start=cast(@year+'-'+@month+'-01' as datetime)
while month(@start)=cast(@month as int)
begin
insert @tb select @start
set @start=dateadd(day,1,@start)
endselect a.dDate, isnull(b.s1_sumMoney,'0') as s1 , isnull(b.s2_sumMoney,'0') as s2 from
(select convert(varchar(10),dDate ,120)as dDate from @tb) a
left join
(
SELECT
SUM(case when t2.PianQu= 'S1' then t3.[Money] else 0 end) AS s1_sumMoney,
SUM(case when t2.PianQu= 'S2' then t3.[Money] else 0 end) AS s2_sumMoney,
-- 其他部门以此类推
convert(varchar(10),Date_Input,120) as da
FROM Book t1,Oper t2
WHERE t1.BookID=t2.BookID
group by convert(varchar(10),Date_Input,120)
) b
on a.dDate=b.da
declare @tb table(dDate datetime)
set @start=cast(@year+'-'+@month+'-01' as datetime)
while month(@start)=cast(@month as int)
begin
insert @tb select @start
set @start=dateadd(day,1,@start)
endselect a.dDate, isnull(b.s1_sumMoney,'0') as s1 , isnull(b.s2_sumMoney,'0') as s2 from
(select convert(varchar(10),dDate ,120)as dDate from @tb) a
left join
(
SELECT
SUM(case when t2.PianQu= 'S1' then t3.[Money] else 0 end) AS s1_sumMoney,
SUM(case when t2.PianQu= 'S2' then t3.[Money] else 0 end) AS s2_sumMoney,
-- 其他部门以此类推
convert(varchar(10),Date_Input,120) as da
FROM Book t1,Oper t2
WHERE t1.BookID=t2.BookID
group by convert(varchar(10),Date_Input,120)
) b
on a.dDate=b.da
SUM(case when t2.PianQu= 'S1' then t3.[Money] else 0 end)+@变量 AS s1_sumMoney
这个月的销售额总和要怎么办?形式如下:(要加一行合计)
dDate s1 s2 .......
2005-09-01 .00 50.00
2005-09-02 72.00 .00
2005-09-03 .00 36.00
.......
2005-09-30
合计 100.00 100.00 ...