求大神帮我看一段代码,帮我看下问题出在哪里,有点多,要慢慢看
SELECT CASE WHEN GROUPING(tmp.quarter) = 0
AND GROUPING(s.Month) = 1
THEN CAST(tmp.quarter AS VARCHAR) + '季度总汇'
WHEN GROUPING(tmp.quarter) = 1
THEN CAST(s.YEAR AS VARCHAR) + '年合计'
ELSE CAST(s.Year AS VARCHAR) + '年' + CAST(s.Month AS VARCHAR)
+ '月'
END AS '时间' ,
ISNULL(SUM(s.Amount), 0) AS '计划销售金额' ,
( SELECT ISNULL(SUM(CASE WHEN Status LIKE '激活' THEN HtTotal
ELSE 0
END), 0)
FROM dbo.s_Contract
WHERE s.ProjGUID = ProjGUID
AND CAST(DATEPART(MM, QSDate) AS VARCHAR) = CAST(s.Month AS VARCHAR)
) AS '实际销售金额' ,
ISNULL(SUM(s.JHHKAmount), 0) AS '计划回款金额' ,
( SELECT ISNULL(SUM(Amount), 0)
FROM dbo.s_Getin ,
dbo.s_Trade ,
dbo.p_Room
WHERE dbo.s_Getin.SaleGUID = dbo.s_Trade.TradeGUID
AND dbo.s_Trade.RoomGUID = dbo.p_Room.RoomGUID
AND dbo.p_Room.ProjGUID = s.ProjGUID
AND dbo.s_Getin.Status IS NULL
AND DATEPART(MM, GetDate) = s.Month
AND ( ItemName = '首期'
OR ItemName = '楼款'
OR ItemName = '定金'
OR ItemName = '补差款'
OR ItemName = '银行按揭'
OR ItemName = '公积金'
)
) AS '实际回款金额' ,
ISNULL(SUM(s.Bldarea), 0) AS '计划销售面积' ,
( SELECT ISNULL(SUM(CASE WHEN Status LIKE '激活' THEN BldArea
ELSE 0
END), 0)
FROM dbo.s_Contract
WHERE ProjGUID = s.ProjGUID
AND DATEPART(MM, QSDate) = s.Month
) AS '实际销售面积',
ISNULL(SUM(s.Rooms), 0) AS '计划销售套数' ,
( SELECT ISNULL(SUM(CASE WHEN Status LIKE '激活' THEN 1
ELSE 0
END), 0)
FROM dbo.s_Contract
WHERE ProjGUID = s.ProjGUID
AND DATEPART(MM, QSDate) = s.Month
) AS '实际销售套数'
FROM dbo.s_SalesBudget s
INNER JOIN dbo.p_tmp tmp ON s.Month = tmp.MONTH
LEFT JOIN ( SELECT ProjGUID ,
ProjName
FROM dbo.p_Project
) p ON s.projGUID = p.ProjGUID
WHERE p.ProjName = '汇景新城-一期'
GROUP BY s.ProjGUID ,
YEAR ,
tmp.quarter ,
s.Month
WITH ROLLUP
HAVING GROUPING(year) = 0
结果如下:
大家知道问题了吧,小计和合计为什么有的地方没有用,求解,挺蛋疼的
[解决办法]
报表和存储过程就是天生的一对啊。你把数据源列出来,写出你要得到的结果,然后大家才好帮你