Help to join two tables become one table

  • Hi guys,

    I'm creating two temporary table from a query:

    SELECT

    branch_description

    ,qty_order

    ,current_open_orders

    FROM

    (

    SELECT

    p21_view_branch. branch_description as branch_description

    ,SUM (CASE WHEN oe_report.order_date BETWEEN CAST (CONVERT(CHAR(50),DATEADD(mm, DATEDIFF(mm,31,getdate()), 0),120)AS DATETIME)AND CAST (getdate () AS DATETIME)

    THEN (oe_line.qty_ordered )

    ELSE 0

    END ) qty_order

    ,SUM (CASE WHEN oe_report.order_date BETWEEN CAST (CONVERT(CHAR(50),DATEADD(mm, DATEDIFF(mm,31,getdate()), 0),120)AS DATETIME)AND CAST (getdate () AS DATETIME)

    THEN (oe_line.extended_price )

    ELSE 0

    END ) current_open_orders

    FROM

    [P21].[dbo].[p21_view_oe_hdr] oe_report

    LEFT OUTER JOIN p21_view_oe_line oe_line ON oe_line.order_no = oe_report.order_no

    LEFT OUTER JOIN p21_view_branch ON p21_view_branch. branch_id = oe_report. location_id

    WHERE

    oe_report.delete_flag = 'N'

    AND oe_report.cancel_flag = 'N'

    AND [send_partial_order_flag] = 'N'

    AND oe_report.completed = 'N'

    AND oe_line.complete = 'N'

    GROUP BY

    p21_view_branch. branch_description

    ) as open_order

    SELECT

    *

    FROM

    (

    SELECT

    p21_sales_history_report_view.branch_description as branch_description

    ,SUM (CASE WHEN p21_sales_history_report_view.year_and_period = CAST (CONVERT(CHAR(6),DATEADD (MM, -1, getdate()),112 )AS INT)

    THEN p21_sales_history_report_view.detail_price

    ELSE 0

    END) MTD_sales

    ,SUM (CASE WHEN p21_sales_history_report_view.year_and_period = CAST (CONVERT(CHAR(6),DATEADD (MM, -1, getdate()),112 )AS INT)

    THEN p21_sales_history_report_view.detail_price - p21_sales_history_report_view.detail_cogs

    ELSE 0

    END) MTD_gm$

    FROM

    P21_sales_history_report_view

    WHERE

    parent_oe_line_uid = 0 AND

    (invoice_adjustment_type='C' AND [p21_sales_history_report_view].source_type_cd = 2638 OR NOT (invoice_adjustment_type = 'C' OR invoice_adjustment_type = 'D'))

    --AND [p21_sales_history_report_view].vendor_consigned = 'N' AND projected_order = 'N' AND (detail_type IS NULL OR detail_type = 0)

    --AND (progress_bill_flag = 'N' OR progress_bill_flag IS NULL )

    GROUP BY

    p21_sales_history_report_view.branch_description

    ) sales_order

    These 2 tables has the same column as branch description. Is there a way that I can join sales_order table and open_order table as one: For example, Table sales_order looks like

    branch description MTD sales MTD gms

    Houston 1200 300

    Odessa 1200 400

    Table current_open_order

    branch description qty_order open_order

    Houston 72.00 600

    Odessa 100 1500

    Fort Worth 50 100

    I want to merge these two tables become one as:

    branch description MTD sales MTD gm$ qty order Opeen order

    Houston 1200 300 72 600

    Odessa 1200 400 100 1500

    Fort Worth 0 0 50 100

    If you know a code or function to do this, please help me.

  • You have to use a LEFT JOIN here with the branch column. However, a join is usually made on a Primary KEY which would be an integer column which is not there in this case.

    select a.branch,ISNULL(b.MTD_Sales,0) MTD_Sales,ISNULL(b.MTD_gms,0) MTD_gms,a.qty_order,a.open_order from

    #current_open_order a

    Left Join #sales_order b on a.branch = b.branch

  • tantcu (8/13/2012)


    Hi guys,

    I'm creating two temporary table from a query:

    SELECT

    branch_description

    ,qty_order

    ,current_open_orders

    .....

    These 2 tables has the same column as branch description. Is there a way that I can join sales_order table and open_order table as one: For example, Table sales_order looks like

    branch description MTD sales MTD gms

    Houston 1200 300

    Odessa 1200 400

    Table current_open_order

    branch description qty_order open_order

    Houston 72.00 600

    Odessa 100 1500

    Fort Worth 50 100

    I want to merge these two tables become one as:

    branch description MTD sales MTD gm$ qty order Opeen order

    Houston 1200 300 72 600

    Odessa 1200 400 100 1500

    Fort Worth 0 0 50 100

    If you know a code or function to do this, please help me.

    No problem. A little tidy-up to make the queries more readable (and perhaps more efficient) first. Here's the top query reformatted:

    SELECT

    b.branch_description, -- as branch_description

    qty_order = SUM(CASE

    WHEN h.order_date BETWEEN CAST(CONVERT(CHAR(50),DATEADD(mm, DATEDIFF(mm,31,getdate()), 0),120) AS DATETIME) -- first day of last month

    AND CAST(getdate() AS DATETIME)

    THEN l.qty_ordered

    ELSE 0 END),

    current_open_orders = SUM(CASE

    WHEN h.order_date BETWEEN CAST(CONVERT(CHAR(50),DATEADD(mm, DATEDIFF(mm,31,getdate()), 0),120) AS DATETIME)

    AND CAST(getdate() AS DATETIME)

    THEN l.extended_price

    ELSE 0 END)

    FROM [P21].[dbo].[p21_view_oe_hdr] h --oe_report

    LEFT OUTER JOIN p21_view_oe_line l --oe_line

    ON l.order_no = h.order_no

    LEFT OUTER JOIN p21_view_branch b

    ON b.branch_id = h.location_id

    WHERE h.delete_flag = 'N'

    AND h.cancel_flag = 'N'

    AND [send_partial_order_flag] = 'N'

    AND h.completed = 'N'

    AND l.complete = 'N'

    GROUP BY b.branch_description

    With the code nicely formatted, you can see that the CASE constructs are redundant - they can be dropped down to the WHERE clause:

    SELECT

    b.branch_description,

    qty_order = SUM(l.qty_ordered),

    current_open_orders = SUM(l.extended_price)

    FROM [P21].[dbo].[p21_view_oe_hdr] h

    LEFT OUTER JOIN p21_view_oe_line l

    ON l.order_no = h.order_no

    LEFT OUTER JOIN p21_view_branch b

    ON b.branch_id = h.location_id

    WHERE h.delete_flag = 'N'

    AND h.cancel_flag = 'N'

    AND [send_partial_order_flag] = 'N'

    AND h.completed = 'N'

    AND l.complete = 'N'

    AND h.order_date BETWEEN CAST(CONVERT(CHAR(50),DATEADD(mm, DATEDIFF(mm,31,getdate()), 0),120) AS DATETIME) -- first day of last month

    AND CAST(getdate() AS DATETIME)

    GROUP BY b.branch_description

    Check that this modification of the top query generates the same results as the original.

    Reformatted and adjusted for the redundant CASE construct, the second query looks like this:

    SELECT

    h.branch_description,

    MTD_sales = SUM(h.detail_price),

    MTD_gm$ = SUM(h.detail_price - h.detail_cogs)

    FROM P21_sales_history_report_view h

    WHERE parent_oe_line_uid = 0

    AND (

    invoice_adjustment_type='C' AND h.source_type_cd = 2638

    OR NOT (invoice_adjustment_type = 'C' OR invoice_adjustment_type = 'D')

    )

    AND 1 = 1

    AND h.year_and_period = CAST (CONVERT(CHAR(6),DATEADD (MM, -1, getdate()),112 )AS INT)

    GROUP BY h.branch_description

    Again, check that this generates the same results as the original.

    Joining the results together is trivial, but first a question – which query, if either, outputs all of the branches you are expecting to see? This is crucial as it determines the type (and order, i.e. which table appears after FROM) of join between the two result sets. If neither result set includes all of the branches you are expecting to see, then a small mod will be required.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply