I have a table with time series data in it and I need to join the table to itself to get previous month information into a view for a reporting tool.
I am using a date reference table to get the date.
The problem is that even using a full outer join, my query drops rows where the data doesn't exist in both tables. I think this is happening because of using the date table, but I'm not sure how else I would do this for multiple time periods.
A simplified version of my SQL is below - Any help is greatly appreciated!
area, net_bk_amt, prev_m_net_bk_amt
from historytable hst
inner join datetable dt on hst.pd_end_dt = dt.pd_end_dt
full outer join historytable_1 hst1 on
hst.region= hst1.region and hst.area=hst1.area
and hst1.pd_end_dt = dt.prev_month_pd_end_dt
you did not explain what the datetable is for, and it appears you have two different history tables
plus, you say you posted a "simplified" version of your query
so i'm not gonna try to rework what you have, i'll just give you some pseudo-code and you can take it from there
first, you can simulate a full outer join with a UNION, and then you can join the summed-up results of the union to some other table...
, SUM(this_month) AS this_month
, SUM(last_month) AS last_month
FROM ( SELECT keycolumns
, datacolumn AS this_month
, 0 AS last_month
, 0 AS this_month
, datacolumn AS last_month
) AS data
ON someothertable.columns = data.keycolumns