Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

I need help combining two queries in SQL Server 2008

Posted on 2014-09-16
3
Medium Priority
?
191 Views
Last Modified: 2014-09-18
Hi experts,
I need your assistance combining the two queries below.
I need to take the sum of query #1 and subtract the sum of query #2 from it.
How can I do this?  I have listed both my queries below.

NOTE: The PONUMBER field in query 1 matches the PONUMBER field in query #2.


--Query 1
SELECT 
  SUM(I.[QTYORDER])
  FROM POPHEADER as I
  inner join POPDETAIL as P
  on I.PONUMBER = P.PONUMBER
  where I.[POLNESTA] not in (4,5,6) and P.[DOCDATE] >= '1/1/2014' and I.[ITEMNMBR] = '209822011'
  and I.POTYPE =1
  
--Query 2
SELECT SUM(QTYRECVD) AS QTY
     FROM INVOICE_TABLE
where [DATERECD] >= '1/1/2014' and RCPTSOLD = 0
and  [PONUMBER] = '07277' and [ITEMNMBR] =  '209822011'

Open in new window



Thanks in advance,
mrotor
0
Comment
Question by:mainrotor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40326841
Well, your second query only contains PONUMBER = '07277', so you just could do this:
SELECT  SUM(I.[QTYORDER])
FROM    POPHEADER AS I
        INNER JOIN POPDETAIL AS P ON I.PONUMBER = P.PONUMBER
WHERE   I.[POLNESTA] NOT IN (4, 5, 6)
        AND P.[DOCDATE] >= '1/1/2014'
        AND I.[ITEMNMBR] = '209822011'
        AND I.POTYPE = 1
	AND i.PONUMBER <> '07277'

Open in new window

But I get the feeling you are looking for something more than that, so perhaps this:
--Query 1
SELECT  SUM(I.[QTYORDER])
FROM    POPHEADER AS I
        INNER JOIN POPDETAIL AS P ON I.PONUMBER = P.PONUMBER
        LEFT JOIN (SELECT   PONUMBER
                   FROM     INVOICE_TABLE
                   WHERE    [DATERECD] >= '1/1/2014'
                            AND RCPTSOLD = 0
                            --AND [PONUMBER] = '07277'
                            AND [ITEMNMBR] = '209822011'
                   GROUP BY PONUMBER
                  ) d ON i.PONUMBER = d.PONUMBER
WHERE   I.[POLNESTA] NOT IN (4, 5, 6)
        AND P.[DOCDATE] >= '1/1/2014'
        AND I.[ITEMNMBR] = '209822011'
        AND I.POTYPE = 1
        AND d.PONUMBER IS NULL

Open in new window

0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40326846
SELECT
      SUM(QTY) AS QTY
FROM (

            --Query 1
            SELECT
                  SUM(I.[QTYORDER]) AS QTY
            FROM POPHEADER AS I
                  INNER JOIN POPDETAIL AS P
                              ON I.PONUMBER = P.PONUMBER
            WHERE I.[POLNESTA] NOT IN (4, 5, 6)
                  AND P.[DOCDATE] >= '1/1/2014'
                  AND I.[ITEMNMBR] = '209822011'
                  AND I.POTYPE = 1
            UNION ALL

                  --Query 2
                  SELECT
                        -SUM(QTYRECVD) AS QTY -- Note the minus
                  FROM INVOICE_TABLE
                  WHERE [DATERECD] >= '1/1/2014'
                        AND RCPTSOLD = 0
                        AND [PONUMBER] = '07277'
                        AND [ITEMNMBR] = '209822011'
      ) AS u

Open in new window

OR: With PONUMBER in the output
SELECT
      PONUMBER
    , SUM(QTY) AS QTY
FROM (

            --Query 1
            SELECT
                  PONUMBER
                , SUM(I.[QTYORDER]) AS QTY
            FROM POPHEADER AS I
                  INNER JOIN POPDETAIL AS P
                              ON I.PONUMBER = P.PONUMBER
            WHERE I.[POLNESTA] NOT IN (4, 5, 6)
                  AND P.[DOCDATE] >= '1/1/2014'
                  AND I.[ITEMNMBR] = '209822011'
                  AND I.POTYPE = 1
            UNION ALL

                  --Query 2
                  SELECT
                        PONUMBER
                      , -SUM(QTYRECVD) AS QTY -- Note the minus
                  FROM INVOICE_TABLE
                  WHERE [DATERECD] >= '1/1/2014'
                        AND RCPTSOLD = 0
                        AND [PONUMBER] = '07277'
                        AND [ITEMNMBR] = '209822011'
      ) AS u
GROUP BY
      PONUMBER
;

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40326849
Ah, never mind, it looks like I misread the question.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question