如何基于Cal表日期区间计算POS表的MTD等时段销售额？

Since your Cal table already maintains the MTD start and end weeks for each corp_year_week, we can leverage that existing data to compute MTD sales efficiently without writing repetitive CASE statements. Here are two straightforward approaches:

Approach 1: Correlated Subquery (Simple & Direct) #

This method uses the MTD interval from the Cal table to fetch all sales for the same store within that range, aggregated as MTD sales:

SELECT 
    pos.corp_year_week, 
    pos.store, 
    store.rep, 
    SUM(pos.sales) AS week_sales,
    -- Calculate MTD sales by summing all sales for the store in the current MTD interval
    (SELECT SUM(p.sales)
     FROM pos p
     JOIN cal c ON p.corp_year_week = c.corp_year_week
     WHERE p.store = pos.store
       AND c.corp_year_week BETWEEN cal.mtd_start_week AND cal.mtd_end_week) AS mtd_sales
FROM pos 
INNER JOIN cal ON pos.corp_year_week = cal.corp_year_week 
INNER JOIN store ON pos.store = store.store 
GROUP BY pos.corp_year_week, pos.store, store.rep, cal.mtd_start_week, cal.mtd_end_week

How it works: #

• For each row in your grouped result (per week, store, rep), we pull the mtd_start_week and mtd_end_week from the Cal table.
• The correlated subquery then sums all sales from the same store where the week falls within that MTD interval.
• This fully reuses the pre-defined intervals in your Cal table—no manual interval coding needed.

Approach 2: Window Function with CTE (Scalable) #

If you prefer using window functions for better performance with large datasets, this CTE-based approach maps each POS record to its MTD interval first, then calculates aggregated sales:

WITH pos_mtd_context AS (
    SELECT 
        pos.corp_year_week,
        pos.store,
        store.rep,
        pos.sales,
        cal.mtd_start_week,
        cal.mtd_end_week
    FROM pos
    INNER JOIN cal ON pos.corp_year_week = cal.corp_year_week
    INNER JOIN store ON pos.store = store.store
)
SELECT 
    corp_year_week,
    store,
    rep,
    SUM(sales) AS week_sales,
    -- Sum all sales for the store that fall within the current row's MTD interval
    SUM(CASE WHEN pc2.corp_year_week BETWEEN pc1.mtd_start_week AND pc1.mtd_end_week THEN pc2.sales ELSE 0 END) 
        OVER (PARTITION BY pc1.store, pc1.rep) AS mtd_sales
FROM pos_mtd_context pc1
JOIN pos_mtd_context pc2 ON pc1.store = pc2.store AND pc1.rep = pc2.rep
GROUP BY pc1.corp_year_week, pc1.store, pc1.rep, pc1.mtd_start_week, pc1.mtd_end_week

Key Notes: #

• Ensure your Cal table has accurate mtd_start_week and mtd_end_week values for every corp_year_week (e.g., the first week of a month should have start/end as itself, the second week should span from first to second, etc.).
• Both approaches avoid hardcoding date intervals—any updates to MTD rules only need to be made in the Cal table, not your SQL.

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