I have a table containing three columns: Age (bucket), Date of Registration & Numbers Registered. Currently, only rows exist where there are registration for that date and age bucket. There are currently 11 age buckets and 14 dates. Therefore I'd like the output to have 154 rows (11 x 14). The SQL Code I have is:
SELECT DISTINCT Age INTO #AgesTable FROM #Output
SELECT DISTINCT DateRegistered INTO #DatesTable FROM #Output
SELECT
a.Age
, d.DateRegistered
, COALESCE(o.Registrations, 0) AS Registrations
FROM #Output o
FULL OUTER JOIN #AgesTable a ON o.Age = a.Age
FULL OUTER JOIN #DatesTable d on o.DateRegistered = d.DateRegistered
WHERE a.Age = '''80 '
.... or have I gone about this the wrong way?
CodePudding user response:
I think you want to cross join a & d then left join o.
;WITH CTE_AGE AS
(
SELECT DISTINCT Age
FROM #Output
)
, CTE_DATES AS
(
SELECT DISTINCT DateRegistered
FROM #Output
)
SELECT a.Age, d.DateRegistered
, COALESCE(o.Registrations, 0) AS Registrations
FROM CTE_AGE a
CROSS JOIN CTE_DATES d
LEFT JOIN #Output o
ON o.Age = a.Age
AND o.DateRegistered = d.DateRegistered
WHERE a.Age LIKE '