I have a derived a table with a category, subcategory, date, time, and value columns (from a much larger table).
The dates are all dates with a particular property (sometimes last 5 weeks, sometimes last 5 Sundays, sometimes month to day), as with the times (sometimes midnight to now every hour, sometimes last 30 minutes every 2 minutes).
The category and subcategory are uninteresting, but if they have no entry in the value field for the time parameters they're completely ignored (which is fine).
When there is no value which matches the combination of the other columns, the row is completely ignored.
My problem is that I need the 0's included. Is there some way I can fill in 0's to the rest of the table?
My thought was to do a cross join of the table against itself using only the first 4 columns, then left join with isnull() on the value column. But the cross join kept including the 5th column.
If I understand correctly, you want all combinations of date, time, category, and subcategory that your query returns. If so, you can use cross join to get the combinations and then left outer join to fill in the values or 0:
with t as (
<your query here>
)
select d.date, ti.time, sc.category, sc.subcategory, coalesce(t.value, 0) as value
from (select distinct date from t) d cross join
(select distinct time from t) ti cross join
(select distinct category, subcategory from t) sc left outer join
t
on t.date = d.date and t.time = ti.time and
t.category = sc.category and t.subcategory = sc.subcategory;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments