Multiple tables/joins

jenspark · #1 (**permalink**) 10-12-10, 21:10

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!

select region,
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

r937 · #2 (**permalink**) 10-12-10, 21:38

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...

Code:

SELECT keycolumns
     , SUM(this_month) AS this_month
     , SUM(last_month) AS last_month
  FROM ( SELECT keycolumns
              , datacolumn AS this_month
              , 0          AS last_month
           FROM daTable
          WHERE conditions_for_this_month
         UNION ALL
         SELECT keycolumns
              , 0          AS this_month 
              , datacolumn AS last_month 
           FROM daTable
          WHERE conditions_for_last_month
       ) AS data
INNER
  JOIN someothertable
    ON someothertable.columns = data.keycolumns

helps?

Internet Services

Web Development

Digital Marketing

Consumer Tech