如何总结当月之前所有月份的总销售额

I am trying to get total sales for all previous months prior to current month. So if i pull a report from 01.12.2017 to 31.12.2017, the query should pull january to november sales.

我想在本月之前的所有前几个月获得总销售额。因此,如果我从2017年12月1日到2017年12月31日提交报告,那么查询应该从1月到11月销售。

I have this query which seems to do it:

我有这个查询似乎这样做:

SELECT
sum(case when month(create_date) = 
  month(DATEADD(m, -12, GETDATE()))
  and year(create_date) = year(DATEADD(m, -12, GETDATE()))
  then Forecast_Revenue else 0 end) as [Sales_12_mo_ago],
sum(case when month(create_date) = 
  month(DATEADD(m, -11, GETDATE()))
  and year(create_date) = year(DATEADD(m, -11, GETDATE()))
  then forecast_revenue else 0 end) as [Sales_11_mo_ago],
sum(case when month(create_date) = 
  month(DATEADD(m, -10, GETDATE()))
  and year(create_date) = year(DATEADD(m, -10, GETDATE()))
  then forecast_revenue else 0 end) as [Sales_10_mo_ago],
sum(case when month(create_date) = 
  month(DATEADD(m, -9, GETDATE()))
  and year(create_date) = year(DATEADD(m, -9, GETDATE()))
  then forecast_revenue else 0 end) as [Sales_9_mo_ago],
sum(case when month(create_date) = 
  month(DATEADD(m, -8, GETDATE()))
  and year(create_date) = year(DATEADD(m, -8, GETDATE()))
  then forecast_revenue else 0 end) as [Sales_8_mo_ago],
sum(case when month(create_date) = 
  month(DATEADD(m, -7, GETDATE()))
  and year(create_date) = year(DATEADD(m, -7, GETDATE()))
  then forecast_revenue else 0 end) as [Sales_7_mo_ago],
sum(case when month(create_date) = 
  month(DATEADD(m, -6, GETDATE()))
  and year(create_date) = year(DATEADD(m, -6, GETDATE()))
  then forecast_revenue else 0 end) as [Sales_6_mo_ago],
sum(case when month(create_date) = 
  month(DATEADD(m, -5, GETDATE()))
  and year(create_date) = year(DATEADD(m, -5, GETDATE()))
  then forecast_revenue else 0 end) as [Sales_5_mo_ago],
sum(case when month(create_date) = 
  month(DATEADD(m, -4, GETDATE()))
  and year(create_date) = year(DATEADD(m, -4, GETDATE()))
  then forecast_revenue else 0 end) as [Sales_4_mo_ago],
sum(case when month(create_date) = 
  month(DATEADD(m, -3, GETDATE()))
  and year(create_date) = year(DATEADD(m, -3, GETDATE()))
  then forecast_revenue else 0 end) as [Sales_3_mo_ago],
sum(case when month(create_date) = 
  month(DATEADD(m, -2, GETDATE()))
  and year(create_date) = year(DATEADD(m, -2, GETDATE()))
  then forecast_revenue else 0 end) as [Sales_2_mo_ago],
sum(case when month(create_date) = 
  month(DATEADD(m, -1, GETDATE()))
  and YEAR(create_date) = year(DATEADD(m, -1, GETDATE()))
  then forecast_revenue else 0 end) as [Sales_1_mo_ago]

FROM
AMGR_Opportunity
 where Objective in ('New Opportunity, New Customer', 'New Opportunity, Existing Customer', 'Repeat Order, Existing Customer', 'Winback Customer')
 and Creator_Id in ('TELE1','CRM','TELE2', 'YONELA', 'TELE3', 'Int1')
 and status in (1,2)

I however need a overall total for all months prior to current month. How can i achieve this? I would also like to know if there isnt an easy way to run this query? I can get previous month by doing this query below

但是,我需要在当月之前的所有月份总计。我怎样才能做到这一点?我还想知道是否有一种简单的方法来运行此查询?我可以通过下面的查询得到上个月

SELECT month(create_date) as month_name, sum(Forecast_Revenue) as sum_of_month
FROM dbo.AMGR_Opportunity_Tbl
WHERE Month(Convert(date,Create_date))= Month(DateAdd(month, -1, convert(date,getDate())))
 AND Year(Create_Date)=YEAR(getDate()) AND status IN (1,2)
 and Objective in ('New Opportunity, New Customer', 'New Opportunity, Existing Customer', 'Repeat Order, Existing Customer', 'Winback Customer')
 and Creator_Id in ('TELE1','CRM','TELE2', 'YONELA', 'TELE3', 'Int1')
    GROUP BY month(create_date);

Is there a way that i can retrieve all previous 11 month sales using the second query option above?

有没有办法可以使用上面的第二个查询选项检索所有之前的11个月销售?

2 个解决方案

#1

it seems i figured it out as below:

看来我觉得如下:

SELECT SUM(Forecast_Revenue) as Sum_of_month
FROM AMGR_Opportunity
WHERE create_date < DATEADD(mm, DATEDIFF(mm, 0, '20171101'), 0)   
AND create_date >= DATEADD(mm, DATEDIFF(mm, 0, '20171101') -12, 0)  and Status IN (1,2)
and Creator_Id in ('TELE1','CRM','TELE2', 'YONELA', 'TELE3', 'Int1')
and Objective in ('New Opportunity, New Customer', 'New Opportunity, Existing Customer', 'Repeat Order, Existing Customer', 'Winback Customer')
group by month(create_date)

i changed the -1 to -12 to get all the months and rewrote the query.

我将-1更改为-12以获取所有月份并重写查询。

#2

Use this

WITH CTE
AS
(
SELECT
    DateDif = 'Sales_'+CAST(DATEDIFF(M,create_date,EOMONTH(DATEADD(M,-1,GETDATE()))+1) AS VARCHAR(10))+'_mo_ago',
    Forecast_Revenue,
    FROM AMGR_Opportunity
        WHERE Objective in ('New Opportunity, New Customer', 'New Opportunity, Existing Customer', 'Repeat Order, Existing Customer', 'Winback Customer')
            AND Creator_Id in ('TELE1','CRM','TELE2', 'YONELA', 'TELE3', 'Int1')
            AND status in (1,2)
)           
SELECT
*
FROM CTE
PIVOT
(
    SUM(forecast_revenue)
    FOR
    DateDif IN
    (
        [Sales_1_mo_ago],
        [Sales_2_mo_ago],
        [Sales_3_mo_ago],
        [Sales_4_mo_ago],
        [Sales_5_mo_ago],
        [Sales_6_mo_ago],
        [Sales_7_mo_ago],
        [Sales_8_mo_ago],
        [Sales_9_mo_ago],
        [Sales_10_mo_ago],
        [Sales_11_mo_ago],
        [Sales_12_mo_ago]
    )
)Pvt

#1