--“去年同期”是否指去年的这个月??如果是的话。就: select departmentname, 本月销售量=sum(case datediff(mm,saledate,getdate()) when 0 then suttle else 0 end), 本年销售累计=sum(case datediff(yy,saledate,getdate()) when 0 then suttle else 0 end), 去年同期累计=sum(case when datediff(yy,saledate,getdate())=1 and datediff(mm,saledate,getdate())=0 then suttle else 0 end) from weight group by departmentname --如果统计整个去年12个月的: select departmentname, 本月销售量=sum(case datediff(mm,saledate,getdate()) when 0 then suttle else 0 end), 本年销售累计=sum(case datediff(yy,saledate,getdate()) when 0 then suttle else 0 end), 去年累计=sum(case when datediff(yy,saledate,getdate())=1 then suttle else 0 end) from weight group by departmentname
select a.departmentname,a.suttle as 本月销售量,sum(case when year(a.SaleDate)=year(b.SaleDate) then b.suttle else 0 end) as 本年销售累计, sum(case when datediff(mm,dateadd(yy,-1,a.SaleDate),b.SaleDate)=0 then b.suttle else 0 end) as 去年销月售量, sum(case when year(dateadd(yy,-1,a.SaleDate))=year(b.SaleDate) then b.suttle else 0 end) as 去年销售累计 from (select departmentname,sum(suttle) suttle,max(SaleDate)SaleDate from weight where saledate>='本月开始日期' and salsedate <='本月结束日期' group by departmentname)a join weight b on a.departmentname=b.departmentname where datediff(yy,b.SaleDate,a.SaleDate)<=1 group by a.departmentname
这样可不可以呢 select a.departmentname, a.thissuttle, b.thesesuttle, c.thosesuttle from (select departmentname, sum(suttle) as thissuttle from v_weight where CreateDT>='本月开始' and createDt<='本月结束' group by departmentname) a left join (select departmentname, sum(suttle) as thesesuttle from v_weight where CreateDT>='本年开始' and createDt<='本年结束' group by departmentname) b on a.departmentname=b.departmentname left join (select departmentname, sum(suttle) as thosesuttle from v_weight where CreateDT>='去年开始' and createDt<='去年结束' group by departmentname) c on a.departmentname=c.departmentname
--“去年同期”是否指去年的这个月??如果是的话。就:
select departmentname,
本月销售量=sum(case datediff(mm,saledate,getdate()) when 0 then suttle else 0 end),
本年销售累计=sum(case datediff(yy,saledate,getdate()) when 0 then suttle else 0 end),
去年同期累计=sum(case when datediff(yy,saledate,getdate())=1 and datediff(mm,saledate,getdate())=0 then suttle else 0 end)
from weight
group by departmentname
--如果统计整个去年12个月的:
select departmentname,
本月销售量=sum(case datediff(mm,saledate,getdate()) when 0 then suttle else 0 end),
本年销售累计=sum(case datediff(yy,saledate,getdate()) when 0 then suttle else 0 end),
去年累计=sum(case when datediff(yy,saledate,getdate())=1 then suttle else 0 end)
from weight
group by departmentname
a.departmentname,a.suttle as 本月销售量,sum(case when year(a.SaleDate)=year(b.SaleDate) then b.suttle else 0 end) as 本年销售累计,
sum(case when datediff(mm,dateadd(yy,-1,a.SaleDate),b.SaleDate)=0 then b.suttle else 0 end) as 去年销月售量,
sum(case when year(dateadd(yy,-1,a.SaleDate))=year(b.SaleDate) then b.suttle else 0 end) as 去年销售累计
from
(select departmentname,sum(suttle) suttle,max(SaleDate)SaleDate from weight where saledate>='本月开始日期' and salsedate <='本月结束日期' group by departmentname)a
join
weight b on a.departmentname=b.departmentname
where
datediff(yy,b.SaleDate,a.SaleDate)<=1
group by a.departmentname
select a.departmentname, a.thissuttle, b.thesesuttle, c.thosesuttle from
(select departmentname, sum(suttle) as thissuttle from v_weight where CreateDT>='本月开始' and createDt<='本月结束' group by departmentname) a
left join
(select departmentname, sum(suttle) as thesesuttle from v_weight where CreateDT>='本年开始' and createDt<='本年结束' group by departmentname) b
on a.departmentname=b.departmentname
left join
(select departmentname, sum(suttle) as thosesuttle from v_weight where CreateDT>='去年开始' and createDt<='去年结束' group by departmentname) c
on a.departmentname=c.departmentname