将当周数据与往年同周数据进行比较
将当周数据与往年同周数据进行比较
提问于 2019-05-14 18:55:42
我有这个表，其中包含按商店和日期销售。
-------------------------------------------
P_DATE - P_STORE - P_SALES
-------------------------------------------
2019-02-05 - S1 - 5000
2019-02-05 - S2 - 9850
2018-06-17 - S1 - 6980
2018-05-17 - S2 - 6590
..
..
..
-------------------------------------------我想将今年最后10周每个商店的销售总和与前几年的同一周进行比较。
我想要这样的结果：
---------------------------------------------------
Week - Store - Sales-2019 - Sales2018
---------------------------------------------------
20 - S1 - 2580 - 2430
20 - S2 - 2580 - 2430
.
.
10 - S1 - 5905 - 5214
10 - S2 - 4789 - 6530
---------------------------------------------------我试过这个：
Select
[Week] = DATEPART(WEEK, E_Date),
[Store] = E_store
[Sales 2019] = Case when Year(P_date) = '2019' Then Sum (P_Sales)
[Sales 2018] = Case when Year(P_date) = '2018' Then Sum (P_Sales)
From
PIECE
Group by
DATEPART(WEEK, E_Date),
E_store我需要你的帮助。
回答 2
Stack Overflow用户
发布于 2019-05-14 19:32:45
这就是你要找的吗？
DECLARE @t TABLE (TransactionID INT, Week INT, Year INT, Amount MONEY)
INSERT INTO @t
(TransactionID, Week, Year, Amount)
VALUES
(1, 20, 2018, 50),
(2, 20, 2019, 20),
(3, 19, 2018, 35),
(4, 19, 2019, 40),
(5, 20, 2018, 70),
(6, 20, 2019, 80)
SELECT TOP 10 Week, [2018], [2019] FROM (SELECT Week, Year, SUM(Amount) As Amount FROM @t GROUP BY Week, Year) t
PIVOT
(
SUM(Amount)
FOR Year IN ([2018], [2019])
) sq
ORDER BY Week DESC
Stack Overflow用户
发布于 2019-05-14 19:42:21
此脚本将考虑包括当前周在内的10周-
WITH wk_list (COMMON,DayMinus)
AS
(
SELECT 1,0 UNION ALL
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 1,4 UNION ALL
SELECT 1,5 UNION ALL
SELECT 1,6 UNION ALL
SELECT 1,7 UNION ALL
SELECT 1,8 UNION ALL
SELECT 1,9
)
SELECT
DATEPART(ISO_WEEK, P_DATE) WK,
P_STORE,
SUM(CASE WHEN YEAR(P_DATE) = 2019 THEN P_SALES ELSE 0 END) SALES_2019,
SUM(CASE WHEN YEAR(P_DATE) = 2018 THEN P_SALES ELSE 0 END) SALES_2018
FROM your_table
WHERE YEAR(P_DATE) IN (2019,2018)
AND DATEPART(ISO_WEEK, P_DATE) IN
(
SELECT A.WKNUM-wk_list.DayMinus AS [WEEK NUMBER]
FROM wk_list
INNER JOIN (
SELECT 1 AS COMMON,DATENAME(ISO_WEEK,GETDATE()) WKNUM
) A ON wk_list.COMMON = A.COMMON
)
GROUP BY DATEPART(ISO_WEEK, P_DATE),P_STORE但如果您想排除当前周，只需替换上述脚本中的以下部分
, wk_list (COMMON,DayMinus)
AS
(
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 1,4 UNION ALL
SELECT 1,5 UNION ALL
SELECT 1,6 UNION ALL
SELECT 1,7 UNION ALL
SELECT 1,8 UNION ALL
SELECT 1,9 UNION ALL
SELECT 1,10
)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56128826
复制相关文章
相似问题
腾讯云开发者
