sql 取舍年份和月份把月换成日显示
sql 选择年份和月份把月换成日显示
请各位大神帮帮忙，不要改动太大。经理发的模板就是这样的 在这基础改该如何进行？？ 添加一个选择年 月的参数然后 显示当月的31天如何改？谢谢。
------解决思路----------------------
这个还是要动态执行比较方便，按照这个写法，你每次都要把每个月的每天的变量写出来
之前那个贴，我在最后面贴了一个有注释的，希望可以帮助理解
这样的需求，还是动态执行好些
------解决思路----------------------
--楼主可以看看这个
http://blog.****.net/maco_wang/article/details/6262162
请各位大神帮帮忙，不要改动太大。经理发的模板就是这样的 在这基础改该如何进行？？ 添加一个选择年 月的参数然后 显示当月的31天如何改？谢谢。
DECLARE @m7e DATETIME
DECLARE @m8s DATETIME
DECLARE @m8e DATETIME
DECLARE @m9s DATETIME
DECLARE @m9e DATETIME
DECLARE @m10s DATETIME
DECLARE @m10e DATETIME
DECLARE @m11s DATETIME
DECLARE @m11e DATETIME
DECLARE @m12s DATETIME
DECLARE @m12e DATETIME
SET @m1s = '2013-01-01'
SET @m1e = '2013-01-31'
SET @m2s = '2013-02-01'
SET @m2e = '2013-02-28'
SET @m3s = '2013-03-01'
SET @m3e = '2013-03-31'
SET @m4s = '2013-04-01'
SET @m4e = '2013-04-30'
SET @m5s = '2013-05-01'
SET @m5e = '2013-05-31'
SET @m6s = '2013-06-01'
SET @m6e = '2013-06-30'
SET @m7s = '2013-07-01'
SET @m7e = '2013-07-31'
SET @m8s = '2013-08-01'
SET @m8e = '2013-08-31'
SET @m9s = '2013-09-01'
SET @m9e = '2013-09-30'
SET @m10s = '2013-10-01'
SET @m10e = '2013-10-31'
SET @m11s = '2013-11-01'
SET @m11e = '2013-11-30'
SET @m12s = '2013-12-01'
SET @m12e = '2013-12-31'
SELECT cast(a.prd_no as varchar(15)) as 货号,cast(b.name as varchar(30)) as 货品名称,
累计数量=SUM(CASE WHEN a.PS_DD BETWEEN @m1s AND @m1e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m2s AND @m2e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m3s AND @m3e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m4s AND @m4e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m5s AND @m5e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m6s AND @m6e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m7s AND @m7e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m8s AND @m8e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m9s AND @m9e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m10s AND @m10e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m11s AND @m11e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m12s AND @m12e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END),
累计金额=SUM(CASE WHEN a.PS_DD BETWEEN @m1s AND @m1e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m2s AND @m2e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m3s AND @m3e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m4s AND @m4e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m5s AND @m5e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m6s AND @m6e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m7s AND @m7e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m8s AND @m8e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m9s AND @m9e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m10s AND @m10e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m11s AND @m11e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)+SUM(CASE WHEN a.PS_DD BETWEEN @m12s AND @m12e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END),
一月数量=SUM(CASE WHEN a.PS_DD BETWEEN @m1s AND @m1e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END),一月金额=SUM(CASE WHEN a.PS_DD BETWEEN @m1s AND @m1e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END),
二月数量=SUM(CASE WHEN a.PS_DD BETWEEN @m2s AND @m2e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END),二月金额=SUM(CASE WHEN a.PS_DD BETWEEN @m2s AND @m2e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END),
三月数量=SUM(CASE WHEN a.PS_DD BETWEEN @m3s AND @m3e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END),三月金额=SUM(CASE WHEN a.PS_DD BETWEEN @m3s AND @m3e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END),
四月数量=SUM(CASE WHEN a.PS_DD BETWEEN @m4s AND @m4e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END),四月金额=SUM(CASE WHEN a.PS_DD BETWEEN @m4s AND @m4e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END),
五月数量=SUM(CASE WHEN a.PS_DD BETWEEN @m5s AND @m5e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END),五月金额=SUM(CASE WHEN a.PS_DD BETWEEN @m5s AND @m5e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END),
六月数量=SUM(CASE WHEN a.PS_DD BETWEEN @m6s AND @m6e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END),六月金额=SUM(CASE WHEN a.PS_DD BETWEEN @m6s AND @m6e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END),
七月数量=SUM(CASE WHEN a.PS_DD BETWEEN @m7s AND @m7e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END),七月金额=SUM(CASE WHEN a.PS_DD BETWEEN @m7s AND @m7e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END),
八月数量=SUM(CASE WHEN a.PS_DD BETWEEN @m8s AND @m8e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END),八月金额=SUM(CASE WHEN a.PS_DD BETWEEN @m8s AND @m8e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END),
九月数量=SUM(CASE WHEN a.PS_DD BETWEEN @m9s AND @m9e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END),九月金额=SUM(CASE WHEN a.PS_DD BETWEEN @m9s AND @m9e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END),
十月数量=SUM(CASE WHEN a.PS_DD BETWEEN @m10s AND @m10e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END),十月金额=SUM(CASE WHEN a.PS_DD BETWEEN @m10s AND @m10e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END),
十一月数量=SUM(CASE WHEN a.PS_DD BETWEEN @m11s AND @m11e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END),十一月金额=SUM(CASE WHEN a.PS_DD BETWEEN @m11s AND @m11e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END),
十二月数量=SUM(CASE WHEN a.PS_DD BETWEEN @m12s AND @m12e THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END),十二月金额=SUM(CASE WHEN a.PS_DD BETWEEN @m12s AND @m12e THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)
FROM TF_PSS a,prdt b
Where a.prd_no=b.prd_no and a.ps_id LIKE 'S%'
GROUP BY a.prd_no,b.name
Order by a.prd_no
------解决思路----------------------
这个还是要动态执行比较方便，按照这个写法，你每次都要把每个月的每天的变量写出来
之前那个贴，我在最后面贴了一个有注释的，希望可以帮助理解
这样的需求，还是动态执行好些
DECLARE @d1 DATETIME不然 不动态执行，无法像你的描述，只输入年和月就得出结果，如上静态处理~~
DECLARE @d2 DATETIME
...
DECLARE @d31 DATETIME
SET @d1 = '2013-01-01'
SET @d2 = '2013-01-31'
...
SET @d31 = '2013-12-31'
SELECT cast(a.prd_no as varchar(15)) as 货号,cast(b.name as varchar(30)) as 货品名称,
累计数量=SUM(CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END),
累计金额=SUM(CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END),
一号数量=SUM(CASE WHEN a.PS_DD>=@d1 AND a.PS_DD<@d2 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END),一号金额=SUM(CASE WHEN a.PS_DD>=@d1 AND a.PS_DD<@d2 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END),
二号数量=SUM(CASE WHEN a.PS_DD>=@d2 AND a.PS_DD<@d3 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END),二号金额=SUM(CASE WHEN a.PS_DD>=@d2 AND a.PS_DD<@d3 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END),
...
三十一号数量=SUM(CASE WHEN a.PS_DD>=@d31 AND a.PS_DD<DATEADD(DAY,1,@d31) THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END),三十一号金额=SUM(CASE WHEN a.PS_DD>=@d31 AND a.PS_DD<DATEADD(DAY,1,@d31) THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)
FROM TF_PSS a,prdt b
Where a.prd_no=b.prd_no and a.ps_id LIKE 'S%'
AND a.PS_DD>=@d1 AND a.PS_DD<DATEADD(DAY,1,@d31)
GROUP BY a.prd_no,b.name
Order by a.prd_no
------解决思路----------------------
--楼主可以看看这个
http://blog.****.net/maco_wang/article/details/6262162