跨年度的非自然月的报表的再次求教。
现有这样一个“物品领用明细表”Test，一共有5个字段：ID(自动编号)，GetUseDate(领用时间)，GetUseDept(领用部门)，GoodsName(领用物品名称)，GoodsQTY(领用数量)。其中“领用部门”的数量是固定的，只有5个部门：转运部、作业部、车队、仓储部、维修部。
现在想要做一个存储过程，通过设定四个参数：年份、起始月份、结束月份和领用物品名称，来查询制作如下格式的报表：
（注：月份为非自然月，存在跨年份的问题，比如2012年1月份的数据范围应为2011.11.26-2012.01.25）
请高手帮忙！！感谢！
[解决办法]
exec pro_baobiao 2012,1,3,'柴油'
/*
月份/部门 转运部 作业部 车队 仓储部 维修部 合计
---------- ----------- ----------- ----------- ----------- ----------- -----------
1 33809 55852 49255 24111 5637 168664
2 32280 53435 46083 25273 5869 162940
3 32198 52052 47500 23901 6945 162596
合计 98287 161339 142838 73285 18451 494200
上年同期 101792 168729 145406 79538 18991 514456
同比 -3505 -7390 -2568 -6253 -540 -20256
*/
GO
if object_id('pro_baobiao') is not null drop proc pro_baobiao
GO
/*
该过程为解CSDN帖而建
功能:格式化返回报表
参数:@year 年份
@BgnMonth 起始月份
@EndMonth 结束月份
@GoodsName 物料名称
作者:磊仔(tanleittl)
*/
CREATE PROC pro_baobiao
@year int,
@BgnMonth int,
@EndMonth int,
@GoodsName varchar(50)
AS
set nocount on
;with CET1 as
(
select cast(b.[month] as varchar(10))[月份/部门],a.GetUseDept,GoodsQTY from test a, dbo.fn_monthlist(26) b
where a.GetUseDate >= b.StartT and a.GetUseDate < b.EndT
and b.[year] = @year and b.[month] >= @BgnMonth and b.[month] <= @EndMonth and GoodsName = @GoodsName
)select *,[转运部] + [作业部] + [车队] + [仓储部] + [维修部] [合计] into #Ta
from CET1 pivot(sum(GoodsQTY) for GetUseDept in([转运部],[作业部],[车队],[仓储部],[维修部]))p
;with CET1 as
(
select cast(b.[month] as varchar(10))[月份/部门],a.GetUseDept,GoodsQTY from test a, dbo.fn_monthlist(26) b
where a.GetUseDate >= b.StartT and a.GetUseDate < b.EndT
and b.[year] = @year - 1 and b.[month] >= @BgnMonth and b.[month] <= @EndMonth and GoodsName = @GoodsName
)select *,[转运部] + [作业部] + [车队] + [仓储部] + [维修部] [合计] into #Pre_Ta
from CET1 pivot(sum(GoodsQTY) for GetUseDept in([转运部],[作业部],[车队],[仓储部],[维修部]))p
;with CET1 AS
(
select '合计'今年,sum([转运部])[转运部],sum([作业部])[作业部],sum([车队])[车队]
,sum([仓储部])[仓储部],sum([维修部])[维修部],sum([合计])[合计] from #Ta
),CET2 AS
(
select '上年同期'上年同期,sum([转运部])[转运部],sum([作业部])[作业部],sum([车队])[车队]
,sum([仓储部])[仓储部],sum([维修部])[维修部],sum([合计])[合计] from #Pre_Ta
),CET3 AS
(
select '同比' as [同比],a.[转运部] - b.[转运部] as [转运部],
a.[作业部] - b.[作业部] as [作业部],a.[车队] - b.[车队] as [车队],
a.[仓储部] - b.[仓储部] as [仓储部],a.[维修部] - b.[维修部] as [维修部],
a.[合计] - b.[合计] as [合计] from CET1 a , CET2 b
)
select * from #Ta
union all select * from CET1
union all select * from CET2
union all select * from CET3
drop table #Ta,#Pre_Ta
GO
--用于生成示例数据
if OBJECT_ID('Test') is not null drop table Test
create table Test(ID [bigint] identity,
GetUseDate [datetime] NULL,
GoodsName varchar(50) NULL,
GetUseDept varchar(50) NULL, GoodsQTY int)
INSERT INTO dbo.Test SELECT dateadd(dd,1+abs(checksum(newid()))%(900), '2010-06-01'),
case when abs(checksum(newid()))%(2) >0 then '汽油' else '柴油' end,
case when abs(checksum(newid()))%(5) > 3 then '转运部'
when abs(checksum(newid()))%(5) > 2 then '作业部'
when abs(checksum(newid()))%(5) > 1 then '车队'
when abs(checksum(newid()))%(5) > 0 then '仓储部'
else '维修部' end,
50+rand(checksum(newid()))*(100)
DECLARE @i AS INT, @rc AS INT,@max int;
SET @rc = 1;
SET @max = 100000;
set @i = ceiling(log10(@max)/LOG10(2))-1;
WHILE @rc <= @i
BEGIN
INSERT INTO dbo.Test SELECT dateadd(dd,1+abs(checksum(newid()))%(900), '2010-06-01'),
case when abs(checksum(newid()))%(2) >0 then '汽油' else '柴油' end,
case when abs(checksum(newid()))%(5) > 3 then '转运部'
when abs(checksum(newid()))%(5) > 2 then '作业部'
when abs(checksum(newid()))%(5) > 1 then '车队'
when abs(checksum(newid()))%(5) > 0 then '仓储部'
else '维修部' end,
50+rand(checksum(newid()))*(100)
FROM dbo.Test;
SET @rc = @rc +1 ;
END
select @max = @max - @@IDENTITY
INSERT INTO dbo.Test
SELECT top(@max) dateadd(dd,1+abs(checksum(newid()))%(900), '2010-06-01'),
case when abs(checksum(newid()))%(2) >0 then '汽油' else '柴油' end,
case when abs(checksum(newid()))%(5) > 3 then '转运部'
when abs(checksum(newid()))%(5) > 2 then '作业部'
when abs(checksum(newid()))%(5) > 1 then '车队'
when abs(checksum(newid()))%(5) > 0 then '仓储部'
else '维修部' end,
50+rand(checksum(newid()))*(100) FROM dbo.Test
GO
if OBJECT_ID('fn_MonthList') is not null drop function fn_MonthList
GO
create function dbo.fn_MonthList(@bgn as int) returns table
as
return
with CET1 AS
(SELECT datepart(yy,dateadd(mm, n - 1,'20000101')) as [year],
datepart(mm,dateadd(mm, n - 1,'20000101')) as [month],
dateadd(hh,8,dateadd(dd,@bgn-1,dateadd(mm,-1,dateadd(d,datediff(d,0,dateadd(mm, n - 1,'20000101')),0)
- DATEPART(dd, dateadd(mm, n - 1,'20000101'))+1))) as StartT,
dateadd(hh,8,dateadd(dd,@bgn-1,dateadd(d,datediff(d,0,dateadd(mm, n - 1,'20000101')),0)
- DATEPART(dd, dateadd(mm, n - 1,'20000101'))+1)) as EndT
FROM dbo.Nums
WHERE n <= DATEDIFF(mm, '20000101', '20300101')
)SELECT * FROM CET1
GO
--生产自定义月序列
if OBJECT_ID('fn_MonthList') is not null drop function fn_MonthList
GO
create function dbo.fn_MonthList(@bgn as int) returns table
as
return
with CET1 AS
(SELECT datepart(yy,dateadd(mm, n - 1,'20000101')) as [year],
datepart(mm,dateadd(mm, n - 1,'20000101')) as [month],
dateadd(hh,8,dateadd(dd,@bgn-1,dateadd(mm,-1,dateadd(d,datediff(d,0,dateadd(mm, n - 1,'20000101')),0)
- DATEPART(dd, dateadd(mm, n - 1,'20000101'))+1))) as StartT,
dateadd(hh,8,dateadd(dd,@bgn-1,dateadd(d,datediff(d,0,dateadd(mm, n - 1,'20000101')),0)
- DATEPART(dd, dateadd(mm, n - 1,'20000101'))+1)) as EndT
FROM dbo.Nums
WHERE n <= DATEDIFF(mm, '20000101', '20300101')
)SELECT * FROM CET1
GO
