Clever Geek Handbook

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

By "fixed vector" I mean a static list of values, for example from 1 to 24.

Current request looks like this (simplified)

SELECT Period, Profit FROM Projections

But the data is "sparse" - so there is no row for each period.

What query will give me a row for periods 1-24 each time with zeros (or NULLs) where there is no data?

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

Thank!

0


source to share


2 answers


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

Or for something really quick and dirty, you could join a subquery 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
+1


source


Why not create a Periods lookup table with values ​​1-24 (and any other columns that might be relevant, such as a period description or name), then make a left outer join between the Period lookup table and the prediction table.



+1


source






More articles:


All Articles