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