How to merge to two temporary tables as one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to merge to two temporary tables as one

Author

Topic

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-08-13 : 18:01:56

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 18:05:15

put the two results onto two # tables and do a join between them on common branchdescription column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources