sql - T-SQL 选择每日总计但每日截止时间不是午夜

Question

我需要计算销售数量，并按日期计算销售总额，这很简单。但曲线间距是——我需要“截止”时间是下午 6 点，而不是午夜。

6pm the day before until 6pm day of.

让我感到困惑的是“分组”。我的计数只提取真实日期，而不是“下午 6 点”信息。

Sort30   Day30      Total                 Counter
-------- ---------- --------------------- -----------
20120810 08/10/2012 675.32                9
20120809 08/09/2012 1314.68               16

查询:

SELECT top 30 CONVERT(VARCHAR(8), chickendate, 112) AS varSort30, 
CONVERT(VARCHAR(10), chickendate, 101) AS varDay30,
SUM(CAST(transAmount AS money)) AS varTotal, 
Count(chickendate) AS varCounter
FROM CHICKEN
WHERE 
    (chickendate >= dateadd(hour, 18, dateadd(day, datediff(day, 0, chickendate), -1))  AND 
    chickendate < dateadd(hour, 18, dateadd(day, datediff(day, 0, chickendate), 0))) 
GROUP BY CONVERT(VARCHAR(8), chickendate, 112), 
    CONVERT(VARCHAR(10), chickendate, 101)
ORDER BY CONVERT(VARCHAR(8), chickendate, 112) DESC

转了一圈又一圈，我觉得有什么东西在盯着我的脸。谢谢。

Answer 1

如果我的逻辑是正确的，这应该会给你正确的结果:

SELECT TOP 30
    CONVERT(VARCHAR(8), modifiedChickenDate, 112) AS varSort30, 
    CONVERT(VARCHAR(10), modifiedChickenDate, 101) AS varDay30,
    SUM(CAST(transAmount AS money)) AS varTotal, 
    COUNT(modifiedChickenDate) AS varCounter
FROM (
    SELECT
        transAmount,
        DATEADD(HOUR, 6, chickendate) AS modifiedChickenDate
    FROM CHICKEN
) sub
GROUP BY
    CONVERT(VARCHAR(8), modifiedChickenDate, 112) AS varSort30, 
    CONVERT(VARCHAR(10), modifiedChickenDate, 101) AS varDay30,
ORDER BY
    CONVERT(VARCHAR(8), modifiedChickenDate, 112) AS varSort30