如何仅使用JOIN语句在SQL中补充缺失Snap日期的0值记录

Got it, here's how you can achieve this using only JOIN operations (no CTE + UNION) by leveraging a derived table to create the full set of expected records, then left joining to your existing data to fill in missing values:

Step-by-Step Explanation: #

1. Create a base set of expected records: We first generate all the Snap dates from DIM_SNAP that should exist for your specified project (PRJ_DWID=1032), element (PRJ_ELMT_DWID=1010985), and Snap ID (SNAP_DWID=730) in 2022. This ensures we have a row for every required date, even if it's missing from the Project table.
2. Left join to existing data: We join this base set to your existing Project data (along with the dimension tables) on matching keys and Snap date. This preserves all rows from the base set, even when there's no corresponding record in Project.
3. Fill missing values: Use COALESCE to replace NULL values (from missing records) with 0 for the cost fields, and default the RPT_PRD to the Snap date when no existing value is present.

Final SQL Query: #

SELECT 
  base.PRJ_DWID,
  base.SNAP_DWID,
  base.PRJ_ELMT_DWID,
  COALESCE(CONVERT(date, dim_prd.PRD_BEG_DT), base.SNAP_DATE) AS [RPT_PRD],
  base.SNAP_DATE AS SNAP,
  COALESCE(proj.FCST_RAW_COST, 0) AS [Estimate to Completion],
  COALESCE(proj.ACCTD_RAW_COST, 0) AS [Actual Cost of Work Performed]
FROM (
  -- Derived table to create all expected Snap date records
  SELECT 
    '1032' AS PRJ_DWID,
    '730' AS SNAP_DWID,
    '1010985' AS PRJ_ELMT_DWID,
    CONVERT(date, REPLACE(ds.EFF_PRD, N'-', N' 1, 20')) AS SNAP_DATE
  FROM DataTable.DIM_SNAP ds
  WHERE ds.DWID = '730'
    AND CONVERT(date, REPLACE(ds.EFF_PRD, N'-', N' 1, 20')) LIKE '%2022%'
) AS base
-- Left join to Project table to get existing records
LEFT JOIN DataTable.Project proj
  ON base.PRJ_DWID = proj.PRJ_DWID
  AND base.SNAP_DWID = proj.SNAP_DWID
  AND base.PRJ_ELMT_DWID = proj.PRJ_ELMT_DWID
  AND base.SNAP_DATE = CONVERT(date, REPLACE(ds_proj.EFF_PRD, N'-', N' 1, 20'))
-- Left join to DIM_SNAP for existing project records
LEFT JOIN DataTable.DIM_SNAP ds_proj
  ON proj.SNAP_DWID = ds_proj.DWID
-- Left join to DIM_PRD for existing project records
LEFT JOIN DataTable.DIM_PRD dim_prd
  ON proj.PRD_DWID = dim_prd.DWID
ORDER BY base.SNAP_DATE;

Key Notes: #

• The derived table (base) acts as a template for all required records, ensuring no Snap dates are missing from the final results.
• LEFT JOIN ensures that even if a Snap date has no matching entry in the Project table, it will still appear in the output.
• COALESCE handles default values exactly as you specified: 0 for cost fields, and the Snap date for RPT_PRD when no existing period data is available.
• Adding ORDER BY helps verify that all dates are present and sorted chronologically.

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