How do I join to a "fixed vector" in SQL?

Question

By "fixed vector" I mean a static list of values, like 1 through 24.

The current query looks like this (simplified)

SELECT Period, Profit FROM Projections

But the data is "sparse" — so there's not a row for every period.

What query will give me a row for peiods 1-24 every time, with zeros (or NULLs) where there's no data?

I would like to do this with just the query to avoid a mess of client code.

Thanks!

Answer 1

Why not create a 'Periods' lookup table, with values 1 - 24 (and any other columns that might be relevant, like a description of the period, or its name) then do a left outer join between the Periods lookup table and your projections table.

Answer 2

You could make a udf called udfRange(start int,count int) or something like that, and left-join to the output of the function.

Or for something really quick and dirty, you could join to a subselect that looked like

SELECT DATA.Period, P.Profit
FROM (
SELECT 1 AS Period
UNION SELECT 2
...
UNION SELECT 24) AS DATA 
LEFT JOIN Projections P ON DATA.Period = P.Period