34,575
社区成员
发帖
与我相关
我的任务
分享
加上排序:
alter proc wsp
@date varchar(7)
as
select 供货单位 ' ',月累,年累 from
(select 供货单位,
(select sum(原重g) from #t where 供货单位=a.供货单位 and datediff(mm,入库时间,cast(@date+'-1' as datetime))=0) '月累',
(select sum(原重g) from #t where 供货单位=a.供货单位 and datepart(yy,入库时间)=left(@date,4)
and datepart(mm,cast(@date+'-1' as datetime))>=datepart(mm,入库时间))[年累]
from #t a group by 供货单位
union all
select distinct '合计' ' ',(select sum(原重g) from #t where datediff(mm,入库时间,cast(@date+'-1' as datetime))=0) '月累',
(select sum(原重g) from #t where datepart(yy,入库时间)=left(@date,4)
and datepart(mm,cast(@date+'-1' as datetime))>=datepart(mm,入库时间))[年累]
from #t)k
order by replace(replace(replace(供货单位,'一',1),'二',2),'三',3)
哦。那改下:
create proc wsp
@date varchar(7)
as
select 供货单位 ' ',
(select sum(原重g) from #t where 供货单位=a.供货单位 and datediff(mm,入库时间,cast(@date+'-1' as datetime))=0) '月累',
(select sum(原重g) from #t where 供货单位=a.供货单位 and datepart(yy,入库时间)=left(@date,4)
and datepart(mm,cast(@date+'-1' as datetime))>=datepart(mm,入库时间))[年累]
from #t a group by 供货单位
union all
select distinct '合计' ' ',(select sum(原重g) from #t where datediff(mm,入库时间,cast(@date+'-1' as datetime))=0) '月累',
(select sum(原重g) from #t where datepart(yy,入库时间)=left(@date,4)
and datepart(mm,cast(@date+'-1' as datetime))>=datepart(mm,入库时间))[年累]
from #t
exec wsp '2007-1'
这样就可以了`
不好意思。漏了合计。。。存储过程修改如下:
create proc wsp
@date varchar(7)
as
select 供货单位 ' ',
(select sum(原重g) from #t where 供货单位=a.供货单位 and datediff(mm,入库时间,cast(@date+'-1' as datetime))=0) '月累',
(select sum(原重g) from #t where 供货单位=a.供货单位 and datepart(yy,入库时间)=left(@date,4))[年累]
from #t a group by 供货单位
union all
select distinct '合计' ' ',(select sum(原重g) from #t where datediff(mm,入库时间,cast(@date+'-1' as datetime))=0) '月累',
(select sum(原重g) from #t where datepart(yy,入库时间)=left(@date,4))[年累]
from #t
exec wsp '2007-1'
create table #t (入库时间 datetime , 供货单位 varchar(10), 原重g float)
insert into #t values ('2007-1-1','一选厂',12 )
insert into #t values ('2007-1-1','三选厂',20 )
insert into #t values ('2007-1-1','二选厂',60 )
insert into #t values ('2007-1-4','一选厂',21 )
insert into #t values ('2007-2-1','三选厂',30 )
insert into #t values ('2007-2-1','二选厂',10 )
create proc wsp
@date varchar(7)
as
select 供货单位,
(select sum(原重g) from #t where 供货单位=a.供货单位 and datediff(mm,入库时间,cast(@date+'-1' as datetime))=0) '月累',
(select sum(原重g) from #t where 供货单位=a.供货单位 and datepart(yy,入库时间)=left(@date,4))[年累]
from #t a group by 供货单位
--1月份的
exec wsp '2007-1'
--2月份的
exec wsp '2007-2'
---依此类推。。