create procedure test @year int,@month int
As
SELECT stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount, SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_1,
SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_1,
SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_2,
SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_2,
SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_3,
SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_3,
SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_4,
SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_4,
SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_5,
SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_5,
.............
SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_31,
SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_31
FROM v_book, stock
WHERE (v_book.fashion_code = stock.fashion_code) AND
(v_book.size_code = stock.fashion_size)
and stock.tz_year = @year and stock.tz_month = @month
GROUP BY stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount
As
SELECT stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount, SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_1,
SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_1,
SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_2,
SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_2,
SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_3,
SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_3,
SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_4,
SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_4,
SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_5,
SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_5,
.............
SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_31,
SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_31
FROM v_book, stock
WHERE (v_book.fashion_code = stock.fashion_code) AND
(v_book.size_code = stock.fashion_size)
and stock.tz_year = @year and stock.tz_month = @month
GROUP BY stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_5,
之类的语句。直接用For i=1 TO 31 NEXT ,还有。如果这个月没有31号,则不生成31号的记录。有何良策!
1。笨一点的办法
每个月的天数无非就是28,29,30,31四种
你可以把语句写成相应的4种,对应28,29,30,31
让后你可以判断该月份的天数是多少天,就调用哪个语句这个语句可以判断该月份有多少天
declare @n datetime
select @n='20030911'
select datediff(day,@n-day(@n)+1,dateadd(mm,1,@n-day(@n)+1))2。灵活一点的办法,
就是用动态语句,先判断该月有多少天,就生成多少天的查询语句
在利用EXECUTE(@SQL)动态运行查询
@year int,
@month int
AS
SELECT stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount, SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_1,
SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_1,
SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_2,
SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_2,
SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_3,
SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_3,
SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_4,
SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_4,
SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_5,
SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_5,
.............
SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '02' OR
v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_31,
SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '10' OR
v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_31
FROM v_book, stock
WHERE (v_book.fashion_code = stock.fashion_code) AND
(v_book.size_code = stock.fashion_size) AND
stock.tz_year=@year and stock.tz_month=@month --加入条件
GROUP BY stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount
另外,这并没有用到临时表,在什么情况下需要使用临时表。
As
Declare @ExecStr Varchar(3000)
@ExecStr=" SELECT stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,"+char(13)
@ExecStr+=" stock.qc_amount, SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '02' OR"+char(13)
@ExecStr+=" v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_1, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 1 AND (v_book.lb = '10' OR"+char(13)
@ExecStr+=" v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_1, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '02' OR"+char(13)
@ExecStr+=" v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_2, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 2 AND (v_book.lb = '10' OR"+char(13)
@ExecStr+=" v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_2, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '02' OR"+char(13)
@ExecStr+=" v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_3, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 3 AND (v_book.lb = '10' OR"+char(13)
@ExecStr+=" v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_3,"+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '02' OR"+char(13)
@ExecStr+=" v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_4, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 4 AND (v_book.lb = '10' OR"+char(13)
@ExecStr+=" v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_4,"+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '02' OR"+char(13)
@ExecStr+=" v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_5, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 5 AND (v_book.lb = '10' OR"+char(13)
@ExecStr+=" v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_5, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '02' OR"+char(13)
@ExecStr+=" v_book.lb = '01') THEN Amount ELSE 0 END) AS rk_31, "+char(13)
@ExecStr+=" SUM(CASE WHEN day(v_book.date) = 31 AND (v_book.lb = '10' OR"+char(13)
@ExecStr+=" v_book.lb = '11') THEN Amount ELSE 0 END) AS ck_31"+char(13)
@ExecStr+=" FROM v_book, stock"+char(13)
@ExecStr+=" WHERE (v_book.fashion_code = stock.fashion_code) AND "+char(13)
@ExecStr+=" (v_book.size_code = stock.fashion_size)"+char(13)
@ExecStr+=" and stock.tz_year = @year and stock.tz_month = @month"+char(13)
@ExecStr+=" GROUP BY stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code, "+char(13)
@ExecStr+=" stock.qc_amount"+char(13)
Execute (@ExecStr)
提示信息:
服务器: 消息 170,级别 15,状态 1,过程 sp_StockMonthReport,行 4
第 4 行: '@ExecStr' 附近有语法错误。
服务器: 消息 137,级别 15,状态 1,过程 sp_StockMonthReport,行 35
必须声明变量 '@ExecStr'。
@year int,
@month int
AS
declare @sql varchar(8000),@day int,@dd int--得到要查询年月的天数
select @dd=day(dateadd(month,@month,dateadd(year,@year-1900,'1999-01-01'))-1)
,@day=1
,@sql='
SELECT stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount'
while @day<=@dd
select @sql=@sql+',SUM(CASE WHEN day(v_book.date)='+cast(@day as varchar)
+' AND (v_book.lb=''02'' OR v_book.lb=''01'') THEN Amount ELSE 0 END) AS rk_'
+cast(@day as varchar)+',SUM(CASE WHEN day(v_book.date)='+cast(@day as varchar)
+' AND (v_book.lb=''10'' OR v_book.lb=''11'') THEN Amount ELSE 0 END) AS ck_'
+cast(@day as varchar)
,@day=@day+1set @sql=@sql+' FROM v_book, stock
WHERE (v_book.fashion_code = stock.fashion_code) AND
(v_book.size_code = stock.fashion_size) AND
stock.tz_year='+cast(@year as varchar)
+' and stock.tz_month='+cast(@month as varchar)+'
GROUP BY stock.tz_year, stock.tz_month, v_book.fashion_code, v_book.size_code,
stock.qc_amount'
exec(@sql)