按季度计算月销售额
提问于 2015-05-29 16:01:25
我需要计算给定季度的月销售额。
这是我用来计算上一个季度的代码。
set @quarter = datepart(QQ, getdate()) - 1
if @quarter = 0
begin
set @quarter = 4
set @year = datepart(year, getdate()) -1
end
else set @year = datepart(year, getdate())下面是我的代码，用于计算整个季度的月平均销售额。
SELECT TOP 5 d.sdealer_name, COUNT(c.icontract_id) / 3 as 'AverageMonthlySales'
FROM dealers d
INNER JOIN contracts c
ON c.sdealer_number = d.sdealer_number
WHERE (d.sdealer_number NOT LIKE '%demo%'
AND d.sdealer_status in ('A', 'R')
AND c.sagent_number = @sagent_number
AND c.sstatus in ('P', 'A', 'C', 'E')
AND c.iproduct_type_id in (4)
AND DATEPART(QQ, c.dtcontract_sale_date) = @quarter
AND DATEPART(year, c.dtcontract_sale_date) = @year)
GROUP BY d.sdealer_name
ORDER BY COUNT(distinct c.icontract_id) desc如何动态计算给定季度每个月的总销售额？
回答 1
Stack Overflow用户
发布于 2015-05-29 22:25:14
ctrl+h ...只需将季度日期部分替换为月。有没有什么原因你还没试过？
set @month = datepart(month, getdate()) - 1
if @month = 0
begin
set @month = 12
set @year = datepart(year, getdate()) -1
end
else set @year = datepart(year, getdate())
SELECT TOP 5 d.sdealer_name, COUNT(c.icontract_id) as 'AverageMonthlySales'
FROM dealers d
INNER JOIN contracts c
ON c.sdealer_number = d.sdealer_number
WHERE (d.sdealer_number NOT LIKE '%demo%'
AND d.sdealer_status in ('A', 'R')
AND c.sagent_number = @sagent_number
AND c.sstatus in ('P', 'A', 'C', 'E')
AND c.iproduct_type_id in (4)
AND DATEPART(month, c.dtcontract_sale_date) = @month
AND DATEPART(year, c.dtcontract_sale_date) = @year)
GROUP BY d.sdealer_name
ORDER BY COUNT(distinct c.icontract_id) desc页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/30534081
复制相关文章
腾讯云开发者
