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

    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
    Location
    Toronto, Canada
    Posts
    20,002
    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?

    rudy.ca | @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
  •