August 13, 2012 at 4:02 pm
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.
August 14, 2012 at 2:41 am
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
August 14, 2012 at 3:49 am
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.
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