MYSQL:按天计算 Rowid 和分组以及上周的计数器。

Question

我遇到一个问题，我无法弄清楚如何计算前一周每天的 RowID 数量，然后按计数器进行分组。

This is my current result

This is the result i'm trying to achieve

以下是我当前的查询。

USE database
SELECT COUNTER AS Counter,
            SUM(CASE WHEN PalletFound = 'Y' THEN 1 ELSE 0 END) AS 'Total Pallets Found',
            SUM(CASE WHEN PalletnotFound = 'Y' THEN 1 ELSE 0 END) AS 'Total Pallets Not Found', 
            COUNT(RowID) AS 'Total Counted',
            DATEADD(WK,DATEDIFF(WK,7,GETDATE()),0) AS Mon,
            DATEADD(WK,DATEDIFF(WK,7,GETDATE()),1) AS Tues,
            DATEADD(WK,DATEDIFF(WK,7,GETDATE()),2) AS Wed,
            DATEADD(WK,DATEDIFF(WK,7,GETDATE()),3) AS Thur,
            DATEADD(WK,DATEDIFF(WK,7,GETDATE()),4) AS Fri,
            DATEADD(WK,DATEDIFF(WK,7,GETDATE()),5) AS Sat,
            DATEADD(WK,DATEDIFF(WK,7,GETDATE()),6) AS Sun
       FROM PICounts
       WHERE COUNTER LIKE 'Zoe' OR COUNTER LIKE 'Moe' OR COUNTER LIKE 'Joe'
       GROUP BY Counter

如果有帮助，我们将不胜感激。

谢谢。

Answer 1

找到了我自己的解决方案。只要报告每周同一天(即星期一)运行，就可以使用以下内容。这将涵盖周一至周日的结果，并且设定的日期可以相应更改。

USE db
DECLARE @startdate date
DECLARE  @endDate date

SET @startDate = DATEADD(WEEK,-1,GETDATE())
SET @endDate = DATEADD(DAY,-1,GETDATE())

SELECT COUNTER AS Counter,
    @startdate AS 'W/C',
    COUNT(CASE WHEN datepart(dw,Completed)=1 THEN Completed END) as Sun,
    COUNT(CASE WHEN datepart(dw,Completed)=2 THEN Completed END) as Mon,
    COUNT(CASE WHEN datepart(dw,Completed)=3 THEN Completed END) as Tues,
    COUNT(CASE WHEN datepart(dw,Completed)=4 THEN Completed END) as Wed,
    COUNT(CASE WHEN datepart(dw,Completed)=5 THEN Completed END) as Thurs,
    COUNT(CASE WHEN datepart(dw,Completed)=6 THEN Completed END) as Fri,
    COUNT(CASE WHEN datepart(dw,Completed)=0 THEN Completed END) as Sat,
    COUNT(*) AS 'Total Counted',
    SUM(CASE WHEN PalletFound = 'Y' THEN 1 ELSE 0 END) AS 'Pallets Found',
    SUM(CASE WHEN PalletnotFound = 'Y' THEN 1 ELSE 0 END) AS 'Pallets Not Found',
    sum(Quantity)-sum(OriginalQuantity) AS 'Units Gained/Lost',
    100-AVG(VARIATIONPCENT) AS 'Accuracy %'    

FROM PICounts

WHERE (COUNTER LIKE 'zoe' OR COUNTER LIKE 'Joe' OR COUNTER LIKE 'Moe') 
AND Completed BETWEEN @startdate AND @endDate

GROUP BY Counter

以下是我取得的成果:

Counter    W/C        Sun   Mon   Tues    Wed   Thurs  Fri  Sat  TotalCounted   PalletsFound    PalletsNotFound UnitsGained/Lost    Accuracy %
zoe     11/07/2017     0     0    167      0     114    58    0     339               5              20              -923              92.625369
moe     11/07/2017     0     0    0        90    30     30    0     150               1               2              -324              98
joe     11/07/2017     0     0    30       40    30     93    0     193               9              14              -3655             87.4803