这个数据的1号总是对不上其他的确没有问题
DECLARE @Year VARCHAR(4),@Mon VARCHAR(2)
set @Year='2015'
set @Mon='1'
DECLARE @StartDate DATETIME,@EndDate DATETIME
SET @StartDate=@Year+'-'+@Mon+'-01'
SET @EndDate=DATEADD(MONTH,1,@StartDate)
SELECT s.name 业务员
,SUM(CASE WHEN DAY(a.PS_DD)=1 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[1号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=1 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[1号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=2 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[2号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=2 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[2号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=3 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[3号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=3 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[3号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=4 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[4号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=4 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[4号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=5 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[5号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=5 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[5号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=6 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[6号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=6 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[6号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=7 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[7号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=7 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[7号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=8 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[8号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=8 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[8号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=9 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[9号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=9 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[9号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=10 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[10号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=10 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[10号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=11 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[11号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=11 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[11号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=12 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[12号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=12 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[12号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=13 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[13号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=13 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[13号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=14 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[14号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=14 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[14号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=15 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[15号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=15 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[15号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=16 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[16号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=16 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[16号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=17 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[17号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=17 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[17号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=18 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[18号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=18 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[18号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=19 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[19号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=19 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[19号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=20 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[20号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=20 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[20号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=21 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[21号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=21 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[21号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=22 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[22号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=22 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[22号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=23 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[23号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=23 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[23号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=24 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[24号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=24 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[24号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=25 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[25号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=25 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[25号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=26 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[26号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=26 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[26号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=27 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[27号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=27 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[27号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=28 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[28号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=28 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[28号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=29 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[29号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=29 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[29号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=30 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[30号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=30 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[30号退货金额]
,SUM(CASE WHEN DAY(a.PS_DD)=31 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[31号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=31 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[31号退货金额]
FROM mf_pss m
left join TF_PSS a on a.ps_no =m.ps_no and a.ps_id =m.ps_id
left join prdt b on a.prd_no=b.prd_no
left join salm s on s.sal_no=m.sal_no
Where a.ps_id LIKE 'S[AB]'
AND a.PS_DD>=@StartDate AND a.PS_DD<=@EndDate
GROUP BY s.name
Order by s.name
单独拿出一个查询
DECLARE @Year VARCHAR(4),@Mon VARCHAR(2)
set @Year='2015'
set @Mon='1'
DECLARE @StartDate DATETIME,@EndDate DATETIME
SET @StartDate=@Year+'-'+@Mon+'-01'
SET @EndDate=DATEADD(MONTH,1,@StartDate)
SELECT a.PS_DD, s.name 业务员
,SUM(CASE WHEN DAY(a.PS_DD)=1 AND a.ps_id='SA' THEN a.amt ELSE 0 END)[1号销售金额]
,SUM(CASE WHEN DAY(a.PS_DD)=1 AND a.ps_id='SB' THEN a.amt ELSE 0 END)[1号退货金额]
FROM mf_pss m
left join TF_PSS a on a.ps_no =m.ps_no and a.ps_id =m.ps_id
left join prdt b on a.prd_no=b.prd_no
left join salm s on s.sal_no=m.sal_no
Where a.ps_id in('SA','SB') -- a.ps_id LIKE 'S[AB]'
AND a.PS_DD>=@StartDate AND a.PS_DD<=@EndDate AND s.sal_no='2123'
GROUP BY a.PS_DD, s.name
Order by s.name
明明查的1日的却出来其他日的，查2号虽然其他是 0不影响数据，但是查1日的会加上2月1日的数据 哪里有问题？
------解决思路----------------------
你这么一长串完全可以写个动态sql语句拼接一下啊，而且你这个对一个月30天28天的月份来说会多出好多空白列啊
------解决思路----------------------
昨天的最后一个贴，那个就是动态语句的，那个就可以了