Combine 2 queries - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Combine 2 queries

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-11-23 : 15:06:21

Is it possible to combine these 2 queries? One is for open orders, the second is for closed orders for the sals rep. I tried union, but that was not the result i was looking for. There are 10 reps, and im trying to show their combined open and closed orders.

Open orders

SELECT A.slspsn_no, IsNull(B.SalesAmount, 0) AS SalesAmount
FROM ARSLMFIL_SQL A
LEFT OUTER JOIN
(
SELECT SUM(((OEORDLIN_SQL.Qty_Ordered * OEORDLIN_SQL.Unit_Price) * ((100 - OEORDLIN_SQL.Discount_Pct) / 100))) AS salesamount, OEORDHDR_SQL.slspsn_no
FROM OEORDLIN_SQL, OEORDHDR_SQL
WHERE OEORDHDR_SQL.ord_no = OEORDLIN_SQL.ord_no
AND OEORDLIN_SQL.prod_cat IN ('o1')
AND OEORDHDR_SQL.Ord_Dt >= '20041101'
AND OEORDHDR_SQL.Ord_Dt <= '20041130'
AND OEORDHDR_SQL.ord_type = 'O'
GROUP BY OEORDHDR_SQL.slspsn_no
)
AS B
ON A.slspsn_no = B.slspsn_no
WHERE A.slspsn_no IN ('101', '102', '103', '104', '105', '106', '107', '108', '110', '116', '117')

Closed

SELECT A.slspsn_no, IsNull(B.SalesAmount,0) As SalesAmount
FROM ARSLMFIL_SQL A
LEFT OUTER JOIN
(
SELECT SUM(Sls_Amt) AS SalesAmount, slspsn_no
FROM dash_product_sales
WHERE MONTH(Inv_Dt) = MONTH('11/01/2004')
AND YEAR(Inv_Dt) = YEAR('11/01/2004')
AND prod_cat IN ('o1')
GROUP BY slspsn_no
) AS B
ON A.slspsn_no = B.slspsn_no
WHERE A.slspsn_no IN ('101', '102', '103', '104', '105', '106', '107', '108', '110', '116', '117')
ORDER BY salesamount DESC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-23 : 15:08:29

Try UNION ALL.

Tara

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-11-23 : 15:19:31

I tried UNION ALL, but it didnt work, it kept the seperated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-23 : 15:26:01

Please show us what UNION and UNION ALL are doing and show us why it doesn't work for you. We need to see the output.

Tara

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-11-23 : 15:33:32

Union
117 4399.15
107 2749.47
116 2309.56
106 2184.6
108 2149.58
104 1829.66
103 1519.7
110 1049.8
105 964.83
107 614.88
101 609.88
117 164.97
116 109.98
102 59.99
105 54.99
101 49.99
102 25
103 0
104 0
106 0
108 0
110 0
UNION ALL
117 4399.15
107 2749.47
116 2309.56
106 2184.6
108 2149.58
104 1829.66
103 1519.7
110 1049.8
105 964.83
107 614.88
101 609.88
117 164.97
116 109.98
102 59.99
105 54.99
101 49.99
102 25
108 0
110 0
106 0
103 0
104 0

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-23 : 15:35:36

So what doesn't work for you in that output? Show us what exactly your desired output would look like.

Tara

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-11-23 : 15:44:56

Thanks for all your help, the desired output would be...

117 4564.12
107 3364.35
116 2419.54
106 2184.6
108 2149.58
104 1829.66
103 1519.7
110 1049.8
105 964.83
101 659.87
102 84.99
105 54.99

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-23 : 15:46:52

It would help if you explained what your output is doing. Are you just adding the SalesAmount together when slspsn_no matches? If so, then you'd do this:

SELECT slspsn_no, SUM(SalesAmount)
FROM
( Put your UNION ALL query in here
) t
GROUP BY slspsn_no

Tara

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-11-23 : 16:38:53

Here is the query I originally came up with...The C subquery, doesnt return all of the sales reps. Only the reps that have open orders. ARSLMFIL_SQL contains all of the reps.

SELECT A.slspsn_no, A.slspsn_name, B.SalesAmount, C.SalesAmount As OpenSalesAmount
FROM ARSLMFIL_SQL A
LEFT OUTER JOIN
(
SELECT SUM(Sls_Amt) AS SalesAmount, slspsn_no
FROM dash_product_sales
WHERE MONTH(Inv_Dt) = MONTH('11/01/2004')
AND YEAR(Inv_Dt) = YEAR('11/01/2004')
AND prod_cat IN ('A68','A69','A83','A84')
GROUP BY slspsn_no
) AS B
ON A.slspsn_no = B.slspsn_no
LEFT OUTER JOIN
(
SELECT SUM(((OEORDLIN_SQL.Qty_Ordered * OEORDLIN_SQL.Unit_Price) * ((100 - OEORDLIN_SQL.Discount_Pct) / 100))) AS salesamount, OEORDHDR_SQL.slspsn_no
FROM OEORDLIN_SQL, OEORDHDR_SQL
WHERE OEORDHDR_SQL.ord_no = OEORDLIN_SQL.ord_no
AND OEORDLIN_SQL.prod_cat IN ('A68','A69','A83','A84')
AND OEORDHDR_SQL.Ord_Dt >= '20041101'
AND OEORDHDR_SQL.Ord_Dt <= '20041130'
AND OEORDHDR_SQL.ord_type = 'O'
GROUP BY OEORDHDR_SQL.slspsn_no
) AS C
ON B.slspsn_no = C.slspsn_no
WHERE A.slspsn_no IN ('101', '102', '103', '104', '105', '106', '107', '108', '110', '116', '117')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-23 : 17:05:16

You haven't provided enough information for us to help you. Please take a look at my last post. We can't see your environment so you have to thoroughly explain what's going on with sample data and expected result set. Without that, there isn't much we can do.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-23 : 17:18:18

Did you try this:

SELECT slspsn_no, SUM(SalesAmount)
FROM
( Put your UNION ALL query in here
) t
GROUP BY slspsn_no

You just need to copy in the UNION ALL query and run it.

Tara

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-11-23 : 18:00:24

Tara,

Thank you so much! This worked. I left out the "t", because I thought hat was a type. LOL What does that t mean? Thanks for helping me.
SELECT slspsn_no, SUM(SalesAmount)
FROM
( Put your UNION ALL query in here
) t
GROUP BY slspsn_no

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-23 : 18:05:31

You have to alias the derived table for some reason. You can alias to anything. I just use t for table.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources