Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010

    Unanswered: Multiple tables/joins

    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

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    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...
    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
      JOIN someothertable
        ON someothertable.columns = data.keycolumns
    helps? | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts