基于Worker ID统计每日新增付费员工的SQL查询需求

Got it, let's fix this up. You need to track New_PRN_Paid—the number of workers who are appearing in the paid list for the first time each day. Here's how to adjust your existing SQL to include this metric:

Step-by-Step Explanation #

First, we need to calculate the first paid date for each worker using the cleaned data in #tsl. Then, we can join this to our daily summary to count how many workers have their first paid date equal to the current day in the summary.

Modified Full Query #

Replace the final SELECT block (after the -- **************************************************************** comment) with this updated code:

-- ****************************************************************
-- First, get the first paid date for each worker
IF OBJECT_ID('tempdb..#FirstPaid') IS NOT NULL DROP TABLE #FirstPaid
SELECT 
    WorkerId,
    MIN(DateWorked) AS FirstPaidDate
INTO #FirstPaid
FROM #tsl
GROUP BY WorkerId

-- Now generate the final summary with New_PRN_Paid
SELECT 
    CONVERT(VARCHAR(10), d.[DateWorked], 103) AS [DateWorked],
    SUM(d.[Hours]) AS [Total_Hours],
    COUNT(DISTINCT d.[WorkerId]) AS [PRN_Paid], -- Use DISTINCT to avoid duplicate worker counts
    COUNT(DISTINCT fp.WorkerId) AS [New_PRN_Paid]
FROM (
    SELECT 
        [WorkerId], 
        [DateFrom], 
        [DateWorked], 
        SUM([Hours]) AS [Hours] 
    FROM #tsl 
    GROUP BY [WorkerId], [DateFrom], [DateWorked]
) d
INNER JOIN [Worker] w ON d.[WorkerId] = w.[Id]
LEFT JOIN #FirstPaid fp ON d.WorkerId = fp.WorkerId AND d.DateWorked = fp.FirstPaidDate
GROUP BY [DateWorked]
ORDER BY CONVERT(DATE, DateWorked) ASC

Key Changes Explained #

• #FirstPaid Temp Table: This captures the earliest date each worker appeared in the paid records (using MIN(DateWorked) grouped by WorkerId).
• COUNT(DISTINCT d.[WorkerId]): Fixed the original PRN_Paid count to use DISTINCT—this ensures we count each worker only once per day, even if they have multiple timesheet entries that day.
• LEFT JOIN #FirstPaid: We link each daily worker entry to their first paid date. When the daily DateWorked matches their FirstPaidDate, they count towards New_PRN_Paid. Using COUNT(DISTINCT fp.WorkerId) ensures we only count each new worker once per day.

Example Output #

This will produce exactly the format you need:

DateWorked | Total_Hours | PRN_Paid | New_PRN_Paid
25/09/2017 | 1376.94 | 122 | 0
26/09/2017 | 1403.40 | 126 | 12
27/09/2017 | 1477.25 | 136 | 16
28/09/2017 | 1432.10 | 133 | 3
29/09/2017 | 1305.49 | 123 | 1

内容的提问来源于stack exchange，提问作者Shahbaz Hussain