SQL需求：按日期及A/B/C班次统计4个月历史生产去重数据

Got it, let's fix this up properly. Your original single-shift query works for a day, but we need to scale it to cover the last 4 months, grouping by every day and each of the three shifts, while counting distinct PRD_SERIAL_NUMBER values. Let's break this down step by step.

First, a quick note on your original query: the GETDATE()-26 part is definitely a typo (that subtracts 26 days, not adjusts the hour). We'll correct that in the expanded solution below.

The Approach #

We need two key components to make this work:

1. A list of every day in the last 4 months, paired with each of the three shifts and their exact time windows.
2. A way to match your production data to these shifts and count unique serial numbers per group.

The SQL Code (SQL Server) #

Here's a complete, tested query that meets all your requirements:

WITH DateShifts AS (
    -- Generate all dates in the last 4 months
    SELECT 
        DATEADD(day, number, DATEADD(month, -4, CAST(GETDATE() AS DATE))) AS ShiftDate
    FROM 
        master..spt_values
    WHERE 
        type = 'P'
        AND DATEADD(day, number, DATEADD(month, -4, CAST(GETDATE() AS DATE))) <= CAST(GETDATE() AS DATE)
    
    -- Pair each date with its three shifts and time ranges
    CROSS APPLY (
        VALUES
            ('A', DATEADD(hour, 6, CAST(ShiftDate AS DATETIME)), DATEADD(hour, 14, CAST(ShiftDate AS DATETIME))),
            ('B', DATEADD(hour, 14, CAST(ShiftDate AS DATETIME)), DATEADD(hour, 22, CAST(ShiftDate AS DATETIME))),
            ('C', DATEADD(hour, 22, CAST(ShiftDate AS DATETIME)), DATEADD(hour, 6, CAST(DATEADD(day, 1, ShiftDate) AS DATETIME)))
    ) AS Shifts(ShiftName, ShiftStart, ShiftEnd)
)
SELECT 
    ds.ShiftDate AS [date],
    ds.ShiftName AS shift_name,
    COUNT(DISTINCT tn.PRD_SERIAL_NUMBER) AS distinct_prd_count
FROM 
    DateShifts ds
LEFT JOIN 
    table_name tn 
        ON tn.LAST_UPDATED_DATE >= ds.ShiftStart
        AND tn.LAST_UPDATED_DATE < ds.ShiftEnd
        AND tn.status = '02' -- Remove quotes if status is a numeric type
GROUP BY 
    ds.ShiftDate, ds.ShiftName
ORDER BY 
    ds.ShiftDate, ds.ShiftName;

How It Works #

• DateShifts CTE: This creates our base dataset. First, we generate every day in the last 4 months using master..spt_values (a built-in table with sequential numbers). Then, we use CROSS APPLY to attach each of the three shifts to every date with their correct time windows:
  • A班: 6:00 AM to 2:00 PM same day
  • B班: 2:00 PM to 10:00 PM same day
  • C班: 10:00 PM same day to 6:00 AM next day
• LEFT JOIN: Ensures we get a row for every date-shift combination, even if there are no matching records (count will be 0). If you only want shifts with existing data, swap this for an INNER JOIN.
• COUNT(DISTINCT ...): Properly counts unique PRD_SERIAL_NUMBER values per date and shift, avoiding duplicate counts of the same serial number.
• GROUP BY + ORDER BY: Groups results by date and shift, then sorts them chronologically for easy readability.

Quick Adjustments for Your Schema #

• If status is a numeric column (not a string), remove the quotes around '02'.
• If you need to adjust the date range (e.g., exactly 120 days instead of 4 calendar months), modify the DATEADD(month, -4, ...) part to DATEADD(day, -120, ...).
• For SQL Server 2022+, you can replace the master..spt_values date generation with GENERATE_SERIES for cleaner code:

  SELECT DATEADD(day, value, DATEADD(month, -4, CAST(GETDATE() AS DATE))) AS ShiftDate
  FROM GENERATE_SERIES(0, DATEDIFF(day, DATEADD(month, -4, CAST(GETDATE() AS DATE)), CAST(GETDATE() AS DATE)))
  

内容的提问来源于stack exchange，提问作者Kiran Madake