SQL Server DATEPART

Question

我正在使用AdventureWorks2012示例数据库。

我正试图使用datepart函数计算2006年的季度总交易额。我需要SEASON列返回1,2,3,4 (而不仅仅是3,4 )，TOTAL列返回值

0, 0, 83537.4000000, 134826.4400000

很抱歉，如果这让人费解的话，这是我第一次使用堆栈溢出。请帮帮我！

以下是代码：

WITH GROSSINCOME AS
(
    SELECT 
        A.ORDERID, 
        SUM((B.QTY * B.unitprice * (1 - B.DISCOUNT))) + A.FREIGHT AS    TOTAL,
        A.orderdate
    FROM 
        SALES.Orders AS A 
    JOIN 
        SALES.OrderDetails AS B ON A.orderid = B.orderid
    GROUP BY 
        A.orderid, A.freight, A.orderdate
)
SELECT 
    DATEPART(QUARTER, orderdate) AS SEASON,
    SUM(TOTAL) AS TOTAL
FROM  
    GROSSINCOME 
WHERE 
    YEAR(orderdate) = '2006'
GROUP BY 
    DATEPART(QUARTER,orderdate);

Answer 1

只是为了修复黑暗代码，下面的代码应该可以工作。

WITH GROSSINCOME AS
(
SELECT A.ORDERID,SUM(B.QTY*B.unitprice*(1-B.DISCOUNT)) + A.FREIGHT AS TOTAL,
A.orderdate
FROM SALES.Orders AS A JOIN SALES.OrderDetails AS B
ON A.orderid=B.orderid
WHERE YEAR(A.orderdate)='2006'
GROUP BY A.orderid,A.freight,A.orderdate
)

SELECT T.VAL AS SEASON,
SUM(ISNULL(TOTAL,0)) AS TOTAL
FROM GROSSINCOME
        RIGHT JOIN (VALUES(1),(2),(3),(4))as T(VAL) ON T.VAL =  DATEPART(QUARTER,orderdate)
GROUP BY T.VAL;

Answer 2

尝尝这个

WITH GROSSINCOME AS
(
SELECT A.ORDERID, SUM((B.QTY*B.unitprice*(1-B.DISCOUNT))) + A.FREIGHT AS TOTAL,
A.orderdate
FROM SALES.Orders AS A JOIN SALES.OrderDetails AS B
ON A.orderid=B.orderid
GROUP BY A.orderid,A.freight,A.orderdate
)

SELECT T.VAL AS SEASON,
SUM(TOTAL) AS TOTAL
FROM GROSSINCOME
        RIGHT JOIN (VALUES(1),(2),(3),(4))as T(VAL) ON T.VAL =  DATEPART(QUARTER,orderdate)
WHERE YEAR(orderdate)='2006'
GROUP BY T.VAL;

Answer 3

检查刚才修改的查询，它将返回4行，四季从1,2,3,4和它们的总数

;WITH GROSSINCOME AS
(
    SELECT 
        A.ORDERID,SUM((B.QTY*B.unitprice*(1-B.DISCOUNT))) + A.FREIGHT AS TOTAL,
        A.orderdate,DATEPART(QUARTER,orderdate) AS season,YEAR(orderdate) orderyear
    FROM 
        SALES.Orders AS A JOIN SALES.OrderDetails AS B
    ON A.orderid=B.orderid
    GROUP BY A.orderid,A.freight,A.orderdate
)
SELECT 
    season,SUM(TOTAL) AS TOTAL
FROM GROSSINCOME 
WHERE orderyear=2006
GROUP BY season;