Question

An analytics engineer is building a daily sales fact table in a Fabric warehouse. The source data only contains rows for dates on which at least one sale occurred, leaving gaps for days with zero sales. The reporting team needs a continuous row for every calendar day in the reporting period, with sales amounts of 0 for days that had no transactions. Which T-SQL approach best produces this continuous daily result?

Accepted Answer

LEFT JOIN a complete calendar date dimension to the sales data on the date key, then wrap the sales amount in ISNULL(SalesAmount, 0).

Answer

INNER JOIN the sales data to the calendar date dimension on the date key so only matching dates are returned.

Answer

Use SELECT DISTINCT on the sales table's date column to remove duplicate dates before aggregating.

Answer

GROUP BY the sales date and use COUNT(*) to identify which dates are missing from the result set.