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
------解决思路----------------------
如果你一个问题还没解决，就先不结贴，继续跟进提问啊~~
我按你们的样子改了一个，应该适合你们，可以改月份和年，只是如果2月只有28天，也会显示31列
DECLARE @Year VARCHAR(4),@Month VARCHAR(2)
SET @Year=2013
SET @Month=1
DECLARE @StartDate DATETIME,@EndDate DATETIME
SET @StartDate=@Year+'-'+@Month+'-01'
SET @EndDate=DATEADD(MONTH,1,@StartDate)
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 DAY(a.PS_DD)=1 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[1号数量],SUM(CASE WHEN DAY(a.PS_DD)=1 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[1号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=2 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[2号数量],SUM(CASE WHEN DAY(a.PS_DD)=2 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[2号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=3 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[3号数量],SUM(CASE WHEN DAY(a.PS_DD)=3 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[3号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=4 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[4号数量],SUM(CASE WHEN DAY(a.PS_DD)=4 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[4号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=5 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[5号数量],SUM(CASE WHEN DAY(a.PS_DD)=5 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[5号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=6 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[6号数量],SUM(CASE WHEN DAY(a.PS_DD)=6 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[6号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=7 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[7号数量],SUM(CASE WHEN DAY(a.PS_DD)=7 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[7号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=8 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[8号数量],SUM(CASE WHEN DAY(a.PS_DD)=8 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[8号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=9 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[9号数量],SUM(CASE WHEN DAY(a.PS_DD)=9 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[9号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=10 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[10号数量],SUM(CASE WHEN DAY(a.PS_DD)=10 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[10号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=11 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[11号数量],SUM(CASE WHEN DAY(a.PS_DD)=11 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[11号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=12 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[12号数量],SUM(CASE WHEN DAY(a.PS_DD)=12 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[12号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=13 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[13号数量],SUM(CASE WHEN DAY(a.PS_DD)=13 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[13号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=14 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[14号数量],SUM(CASE WHEN DAY(a.PS_DD)=14 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[14号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=15 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[15号数量],SUM(CASE WHEN DAY(a.PS_DD)=15 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[15号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=16 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[16号数量],SUM(CASE WHEN DAY(a.PS_DD)=16 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[16号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=17 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[17号数量],SUM(CASE WHEN DAY(a.PS_DD)=17 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[17号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=18 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[18号数量],SUM(CASE WHEN DAY(a.PS_DD)=18 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[18号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=19 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[19号数量],SUM(CASE WHEN DAY(a.PS_DD)=19 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[19号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=20 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[20号数量],SUM(CASE WHEN DAY(a.PS_DD)=20 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[20号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=21 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[21号数量],SUM(CASE WHEN DAY(a.PS_DD)=21 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[21号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=22 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[22号数量],SUM(CASE WHEN DAY(a.PS_DD)=22 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[22号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=23 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[23号数量],SUM(CASE WHEN DAY(a.PS_DD)=23 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[23号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=24 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[24号数量],SUM(CASE WHEN DAY(a.PS_DD)=24 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[24号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=25 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[25号数量],SUM(CASE WHEN DAY(a.PS_DD)=25 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[25号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=26 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[26号数量],SUM(CASE WHEN DAY(a.PS_DD)=26 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[26号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=27 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[27号数量],SUM(CASE WHEN DAY(a.PS_DD)=27 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[27号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=28 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[28号数量],SUM(CASE WHEN DAY(a.PS_DD)=28 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[28号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=29 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[29号数量],SUM(CASE WHEN DAY(a.PS_DD)=29 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[29号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=30 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[30号数量],SUM(CASE WHEN DAY(a.PS_DD)=30 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[30号金额]
,SUM(CASE WHEN DAY(a.PS_DD)=31 THEN (CASE WHEN a.ps_id='SA' THEN a.qty ELSE -a.qty END ) ELSE 0 END)[31号数量],SUM(CASE WHEN DAY(a.PS_DD)=31 THEN (CASE WHEN a.ps_id='SA' THEN a.AMT ELSE -a.AMT END ) ELSE 0 END)[31号金额]
FROM TF_PSS a,prdt b Where a.prd_no=b.prd_no and a.ps_id LIKE 'S[AB]'
AND a.PS_DD>=@StartDate AND a.PS_DD<@EndDate
GROUP BY a.prd_no,b.name Order by a.prd_no
------解决思路----------------------
-- 选择的年月（忽略日）
DECLARE @YearMonth datetime
SET @YearMonth = '2015-02-13'
DECLARE @thisMonth datetime
DECLARE @nextMonth datetime
SET @thisMonth = Convert(datetime,
Convert(varchar(7),@YearMonth,120)+'-01',
120)
SET @nextMonth = DateAdd(month,1,@thisMonth)
SELECT cast(a.prd_no as varchar(15)) as 货号,
cast(b.name as varchar(30)) as 货品名称,
累计数量=SUM(a.sign * a.qty),
累计金额=SUM(a.sign * a.AMT),
一号数量=SUM(CASE WHEN a.day=1 THEN a.sign * a.qty ELSE 0 END),
一号金额=SUM(CASE WHEN a.day=1 THEN a.sign * a.AMT ELSE 0 END),
-- ...
三十一号数量=SUM(CASE WHEN a.day=31 THEN a.sign * a.qty ELSE 0 END),
三十一号金额=SUM(CASE WHEN a.day=31 THEN a.sign * a.AMT ELSE 0 END)
FROM (-- 先对a进行过滤和预处理
SELECT prd_no,
DatePart(day,PS_DD) [day], -- 只需要日
CASE WHEN ps_id = 'SA' THEN 1 ELSE -1 END [sign], -- 先提出来了
amt,
qty
FROM TF_PSS
WHERE PS_DD >= @thisMonth
AND PS_DD < @nextMonth
AND ps_id LIKE 'S[A-B]%'
) a
JOIN prdt b
ON a.prd_no=b.prd_no
GROUP BY a.prd_no,b.name
ORDER BY a.prd_no