;WITH Days([Day]) AS
( SELECT 'Mon' UNION ALL SELECT 'Tue'),

Groups([Group]) AS
( SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c' UNION ALL SELECT 'd' UNION ALL SELECT 'e')

SELECT
	d.*,
	g.*
	--, other columns here
FROM
	(Days d
	CROSS JOIN Groups g) 
	LEFT JOIN YourOtherTables y ON ....

quote:

---

Originally posted by lappin

I have a function which returns grouped totals by day and group.
e.g
Day Group Total
Mon a 123
Mon b 55
Mon e 45
Tue b 33
Tue c 99

I need to fill missing for each day Mon to Friday and groups a to e.
So the above should look like this:
Day Group Total
Mon a 123
Mon b 55
Mon c 0
Mon d 0
Mon e 45
Tue a 33
Tue b 33
Tue c 99
Tue d 0
Tue e 0

I've looked at CTE recursion but any examples I found do not have grouping. I also have a filler table with Mon to Fri and groups a to e for each day.

---