获取过去12个月的总销售额，即使值为空

tnkciper 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(310)

我有下面的代码，其中它目前给出了本年度每月的总销售额，我需要得到总销售额从上一年的最后一个月到今年的本月。
我的问题如下：

;WITH mcte AS (
 SELECT DATEADD(year, -1, getdate()) as MONTH_NAME
 UNION ALL
 SELECT DATEADD(MONTH,1,MONTH_NAME)
 FROM mcte
 WHERE DATEPART(MONTH,MONTH_NAME) < 12),octe AS(
 SELECT (DATENAME (MONTH, DATEADD ( MONTH, DATEPART(MONTH, OI.CreatedDate), -1) )) AS MONTH_NAME,
 SUM (OI.ItemQty * OI.TotalPrice) AS TOTAL_SALES
 FROM Order_Item OI            
 GROUP BY MONTH(OI.CreatedDate))
 SELECT DATENAME(MONTH,m.MONTH_NAME) + '' + DATENAME(YEAR,m.MONTH_NAME) as 
 MONTH_NAME, o.TOTAL_SALES FROM mcte m LEFT JOIN octe o ON o.MONTH_NAME = DATENAME(MONTH,m.MONTH_NAME)

我也有记录了

MONTH_NAME     TOTAL_SALES
July2019       54023.45
August2019     NULL
December2019   NULL
September2019  NULL
October2019    NULL
November2019   NULL

这里我只获取上一年的数据，不获取本年的数据。有人能给我提供指导吗。
谢谢您

sql sql-server tsql

来源：https://stackoverflow.com/questions/62773496/get-total-sales-of-last-12-months-even-if-values-are-null

2条答案

按热度按时间

zlhcx6iw1#

尝试这样做：

DECLARE @CurDate DATE = GET_DATE()
DECLARE @OneYearPrior DATE = DATEADD(YEAR, -1, @CurDate)

WITH relevant_months(start_date, month_of_sale, year_of_sale) AS (
    SELECT 
        @CurDate AS start_date, 
        MONTH(@CurDate) as month_of_sale, 
        YEAR(@CurDate) as year_of_sale
    UNION ALL
    SELECT DATEADD(MONTH, -1, start_date) AS start_date,
        MONTH(DATEADD(MONTH, -1, start_date)) as month_of_sale, 
        YEAR(DATEADD(MONTH, -1, start_date)) AS year_of_sale
    FROM relevant_months
    WHERE DATEADD(MONTH, -1, start_date) >= @OneYearPrior
),
relevant_data AS (
    SELECT OI.CreatedDate, 
        OI.ItemQty, 
        OI.TotalPrice, 
        MONTH(OI.CreatedDate), AS month_of_sale, 
        YEAR(OI.CreatedDate) AS year_of_sale
    FROM Order_Item OI
    WHERE OI.CreatedDate >= DATEADD(YEAR, -1, GETDATE())
)
SELECT rm.month_of_sale as month, rm.year_of_sale as year, 
    SUM(rd.ItemQty*rd.TotalPrice) as total_sales
FROM relevant_months rm
LEFT JOIN relevant_data rd
    ON rm.month_of_sale = rd.month_of_sale
    AND rm.year_of_sale = rd.year_of_sale
GROUP BY rm.month_of_sale, rm.year_of_sale
ORDER BY rm.year_of_sale asc, rm.month_of_sale asc

赞(0）回复(0）举报 2021-07-24

8yoxcaq72#

您最多只能生成12个月。尝试将第一个cte替换为：

WITH mcte AS (
      SELECT DATEADD(year, -1, getdate()) as MONTH_NAME
      UNION ALL
      SELECT DATEADD(MONTH,1,MONTH_NAME)
      FROM mcte
      WHERE month_name < GETDATE()
     ),

注意区别在于 WHERE 条款。
整个查询应如下所示：

WITH months AS (
      SELECT DATEFROMPARTS(YEAR(getdate()) - 1, MONTH(getdate()), 1) as month
      UNION ALL
      SELECT DATEADD(MONTH, 1, month)
      FROM months
      WHERE EOMONTH(month) < GETDATE()
     )
SELECT m.month, SUM(OI.ItemQty * OI.TotalPrice) AS TOTAL_SALES
FROM months m LEFT JOIN
     Order_Item OI oi
     ON oi.CreatedDate >= m.month AND
        oi.CreatedDate < DATEAADD(month, 1, m.month)          
GROUP BY m.month

赞(0）回复(0）举报 2021-07-24

我来回答

获取过去12个月的总销售额，即使值为空

2条答案

相关问题

热门标签

最新问答