按地点与周六周末日期分组：SQL终止员工统计报表实现咨询

Got it, let's walk through how to build exactly the SQL report you need, step by step. I'll break down the logic and provide a complete, adaptable solution.

Core Requirements Recap #

First, let's make sure we're aligned on what you need:

• Count employees terminated on or after March 1, 2016
• Group results by Location and Saturday as the Week End Date
• Include rows for locations with 0 terminated employees in a week (no missing rows)

Step 1: Generate a List of Target Saturday Dates #

We need every Saturday starting from March 1, 2016, up to your desired end date (like the 1/12/2019 in your example). Here are two common ways to do this:

Option A: Recursive CTE (Works for SQL Server, PostgreSQL, etc.) #

This generates dates on the fly without a pre-existing calendar table:

WITH DateRange AS (
    -- Start with the first Saturday on/after 2016-03-01
    SELECT CASE
        WHEN DATEPART(WEEKDAY, '2016-03-01') = 7 THEN CAST('2016-03-01' AS DATE)
        ELSE DATEADD(DAY, 7 - DATEPART(WEEKDAY, '2016-03-01'), CAST('2016-03-01' AS DATE))
    END AS WeekEndDate
    UNION ALL
    -- Add 7 days each time to get the next Saturday
    SELECT DATEADD(DAY, 7, WeekEndDate)
    FROM DateRange
    -- Stop at your desired end date (adjust this to match your report's cutoff)
    WHERE WeekEndDate <= '2019-12-01'
)
SELECT WeekEndDate FROM DateRange;

Option B: Use a Calendar Table (More Efficient for Large Datasets) #

If you have a pre-built Calendar table with all dates, just filter for Saturdays:

SELECT Date AS WeekEndDate
FROM Calendar
WHERE Date >= '2016-03-01'
  AND DATEPART(WEEKDAY, Date) = 7; -- Note: Adjust this for your DB (e.g., PostgreSQL uses DATE_PART('dow', Date) = 6 for Saturday)

Step 2: Get All Unique Locations #

We need every location from your employee table to ensure we show 0 counts for weeks with no terminations:

SELECT DISTINCT Location FROM Employee;

Step 3: Combine Dates, Locations, and Termination Counts #

We'll use a cross join to pair every Saturday with every location, then left join to termination stats to include 0 counts. Here's the full query (SQL Server example):

WITH DateRange AS (
    SELECT CASE
        WHEN DATEPART(WEEKDAY, '2016-03-01') = 7 THEN CAST('2016-03-01' AS DATE)
        ELSE DATEADD(DAY, 7 - DATEPART(WEEKDAY, '2016-03-01'), CAST('2016-03-01' AS DATE))
    END AS WeekEndDate
    UNION ALL
    SELECT DATEADD(DAY, 7, WeekEndDate)
    FROM DateRange
    WHERE WeekEndDate <= '2019-12-01'
),
AllLocations AS (
    SELECT DISTINCT Location FROM Employee
),
TerminationStats AS (
    SELECT
        Location,
        -- Map each termination date to its corresponding week-ending Saturday
        CASE
            WHEN DATEPART(WEEKDAY, TerminationDate) = 7 THEN TerminationDate
            ELSE DATEADD(DAY, 7 - DATEPART(WEEKDAY, TerminationDate), TerminationDate)
        END AS WeekEndDate,
        COUNT(EmployeeId) AS TerminatedCount
    FROM Employee
    WHERE CurrentStatus = 'Terminated'
      AND TerminationDate >= '2016-03-01'
      AND TerminationDate <= '2019-12-01' -- Match the DateRange cutoff
    GROUP BY Location,
        CASE
            WHEN DATEPART(WEEKDAY, TerminationDate) = 7 THEN TerminationDate
            ELSE DATEADD(DAY, 7 - DATEPART(WEEKDAY, TerminationDate), TerminationDate)
        END
)
SELECT
    al.Location,
    dr.WeekEndDate,
    ISNULL(ts.TerminatedCount, 0) AS [No of Employees Terminated]
FROM DateRange dr
CROSS JOIN AllLocations al
LEFT JOIN TerminationStats ts
    ON dr.WeekEndDate = ts.WeekEndDate AND al.Location = ts.Location
ORDER BY dr.WeekEndDate, al.Location;

Key Notes for Adaptation #

• Database Dialect Adjustments:
  • PostgreSQL: Replace DATEPART(WEEKDAY, ...) with DATE_PART('dow', ...) (0 = Sunday, 6 = Saturday)
  • MySQL: Use DAYOFWEEK(...) where 7 = Saturday
• Week Definition: This query assumes weeks run Sunday to Saturday. If your week starts on Monday, tweak the DATEADD logic to map dates to the correct Saturday.
• Performance: For large employee tables, add indexes on TerminationDate and Location to speed up the grouping. Using a calendar table instead of a recursive CTE will also improve efficiency.

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