各位大虾帮忙分析一下

[yydy]

两个项目数据表
一个是项目L数据表
测点 日期 值
Ladd Ldate Lval
一个是项目Y数据表
测点 日期 值
Yadd Ydate Yval
统计成
####年#季度项目L、项目Y数据统计表

#月 #月 #月 #月 #月 #月
测点 LData1 LData2 LData3 YData1 YData2 YData3

1、2、3月份为一季度，4、5、6为二季度......
利用视图形成两个虚表
ttest
测点 日期 项目 值
tadd tdate tItem tval
c 2003-1-5 L 1
c 2003-2-5 L 2
c 2003-3-5 L 3
d 2003-1-5 L 1
d 2003-2-5 L 2
d 2003-3-5 L 3
.......
ptest
测点 日期 项目 值
tadd tdate tItem tval
d 2003-1-5 Y 1
d 2003-2-5 Y 2
d 2003-3-5 Y 3
c 2003-1-5 Y 1
c 2003-2-5 Y 2
c 2003-3-5 Y 3
.......

SELECT tunit, SUM(CASE month(tdate) WHEN '1' THEN tval ELSE 0 END) AS Lm1,
SUM(CASE month(tdate) WHEN '2' THEN tval ELSE 0 END) AS Lm2,
SUM(CASE month(tdate) WHEN '3' THEN tval ELSE 0 END) AS Lm3,
SUM(CASE month(pdate) WHEN '1' THEN pval ELSE 0 END) AS Ym1,
SUM(CASE month(pdate) WHEN '2' THEN pval ELSE 0 END) AS Ym2,
SUM(CASE month(pdate) WHEN '3' THEN pval ELSE 0 END) AS Ym3
FROM tTest, pTest
WHERE tunit = punit
GROUP BY tunit
然后将数据保存

各位大虾帮忙分析一下
能否帮助写一个功能强大实现起来方便简单的存储过程、游标
来实现统计表

[caiyunxia]

CREATE PRO PRO_TEST
@STDATE DATETIME --传本季度的第一天的日期
AS
DECLARE @N INT
SET @N=month(tdate)

SELECT tunit, SUM(CASE month(tdate) WHEN @N THEN tval ELSE 0 END) AS Lm1,
SUM(CASE month(tdate) WHEN @N+1 THEN tval ELSE 0 END) AS Lm2,
SUM(CASE month(tdate) WHEN @N+2 THEN tval ELSE 0 END) AS Lm3,
SUM(CASE month(pdate) WHEN @N THEN pval ELSE 0 END) AS Ym1,
SUM(CASE month(pdate) WHEN @N+1 THEN pval ELSE 0 END) AS Ym2,
SUM(CASE month(pdate) WHEN @N+2 THEN pval ELSE 0 END) AS Ym3
FROM tTest, pTest
WHERE tunit = punit AND TDATE>=@STDATE AND TDATE<DATEADD(M,4,@STDATE)
GROUP BY tunit

[caiyunxia]

CREATE PRO PRO_TEST
@STDATE DATETIME --传本季度的第一天的日期
AS
DECLARE @N INT,@sql nvarchar(3000)
SET @N=month(tdate)
set @sql =''
set @sql =@sql + 'SELECT tunit, SUM(CASE month(tdate) WHEN @ THEN tval ELSE 0 END) AS L'+cast(@ as varchar(2))+'月, SUM(CASE month(tdate) WHEN @+1 THEN tval ELSE 0 END) AS L'+ cast(@+1 as varchar(2))+'月,SUM(CASE month(tdate) WHEN @+2 THEN tval ELSE 0 END) AS L' +cast(@+2 as varchar(2))+'月,SUM(CASE month(pdate) WHEN @ THEN pval ELSE 0 END) AS Y 'cast(@ as varchar(2))+'月,SUM(CASE month(pdate) WHEN @+1 THEN pval ELSE 0 END) AS Y'+cast(@+1 as varchar(2))+'月,
SUM(CASE month(pdate) WHEN @+2 THEN pval ELSE 0 END) AS Y'+cast(@ as varchar(2))+'月, FROM tTest, pTest WHERE tunit = punit AND TDATE>=@ST AND TDATE<DATEADD(M,4,@ST) GROUP BY tunit'
exec @sql, N'@ int ,@st datetime',@N,@STDATE