Combine two SQL query into one result

Hello there,

I have to create a report which has to contain the following columns.
1. ProductCode
2. BranchOffice Code
3. Sales Qty + Discount Qty
4. Discount Qty
5. Sales Returned Qty
6. Discount Returned Qty

Now I managed to get these results but using 2 complex query from my perspective.
 First query returned the
1. ProductCode
2. BranchOffice Code
3. Sales Qty + Discount Qty
4. Discount Qty

Second Query returned the
1. ProductCode
2. BranchOffice Code
3. Sales Returned Qty
4. Discount Returned Qty

Now I want to combine them into one query,so I get my final column. I have read I nee dto use UNION but with the queries I have, I could not comnine them into one. Is there some other way of doing this for e.g. creating a VIEW and then using SSIS or even plan sql query get the desire result.

cheers
Zolf

FIRST QUERY

SELECT 
a.code as  ProductCode,
a.product as ProductName,
a.Branch as BranchOffice,
SUM(a.SalesQtyinclDis) as SalesQtyinclDis,
SUM(a.DisSum) as Discount
FROM 
(SELECT
    dbo.Receipt.transactionNumber,
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + cast (dbo.Tafsil.code as  varchar(50)) AS code,
    dbo.Tafsil.description as product,
    dbo.Branch.name AS Branch,
    SUM(dbo.SaleDetail.quantity)                     AS SalesQtyinclDis,
    (dbo.DeliveryOrderDetail.quantityDiscount       +   dbo.DeliveryOrderDetail.supplierQuantityDiscount) AS  DisSum
FROM
    dbo.SaleDetail
INNER JOIN
    dbo.Sale
ON
    (
        dbo.SaleDetail.saleId = dbo.Sale.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.Batch Batch_alias1
ON
    (
        dbo.SaleDetail.batchId = Batch_alias1.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        Batch_alias1.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.DeliveryOrderDetail
ON
    (
        dbo.SaleDetail.orderDetailId = dbo.DeliveryOrderDetail.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= '2015-06-22'
AND dbo.Receipt.receiptDate < '2015-07-23'
GROUP BY
    dbo.Receipt.transactionNumber,
    dbo.Tafsil.description,
    dbo.Branch.name,
    dbo.DeliveryOrderDetail.quantityDiscount,
    dbo.DeliveryOrderDetail.supplierQuantityDiscount,
    kol.code,
    Mohin.code,
    Tafsil.code) a 
    GROUP BY
    a.code,
    a.Branch,
a.product

Open in new window


SECOND QUERY

SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + cast (dbo.Tafsil.code as  varchar(50)) AS ProductCode,
 dbo.Tafsil.description as ProductName,
    dbo.Branch.name as BranchCode,
    SUM(dbo.SaleReturnDetail.quantity) AS SalesReturnQty,
    (CASE 
     WHEN (dbo.SaleDetail.type = 0 ) THEN 'Sales'
                                            ELSE 'Discount' 
 END) as SaleType
   
FROM
    dbo.Receipt
INNER JOIN
    dbo.SaleReturn
ON
    (
        dbo.Receipt.id = dbo.SaleReturn.invoiceReceiptId)
INNER JOIN
    dbo.SaleReturnDetail
ON
    (
        dbo.SaleReturn.id = dbo.SaleReturnDetail.saleReturnId)
INNER JOIN
    dbo.SaleDetail
ON
    (
        dbo.SaleReturnDetail.saleDetailId = dbo.SaleDetail.id)
INNER JOIN
    dbo.Batch Batch_alias1
ON
    (
        dbo.SaleDetail.batchId = Batch_alias1.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        Batch_alias1.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= '2015-06-22'
AND dbo.Receipt.receiptDate < '2015-07-23'
GROUP BY
    dbo.SaleDetail.type,
    dbo.Tafsil.description,
    dbo.Branch.name,
     kol.code,
    Mohin.code,
    Tafsil.code ;

Open in new window

zolfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
as it's unclear how you want to combine them, I presume you want to "JOIN" them
likely using some syntax like this:
select ...
 from ( SQL 1 from above goes here ) sq1
 join  ( SQL 2 from above goes here ) sq2 
   on sq1.xxxx = sq2.xxxx 

Open in new window


the join condition is unclear and must be specified (you might need to add columns to SQL 1 and SQL 2 to make the join happen correctly

hope this helps
0
zolfAuthor Commented:
Thanks for your feedback, frankly speaking I have no clue as to what I need to do. I am thinking to create 2 VIEWS from these 2 queries and then combine them and get the result using either SQL again or SSIS. Any suggestion would be appreciated,I am stuck!!
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and I have no clue if you want to actually UNION or JOIN.
UNION is to have the results one below the others, and JOIN  is to really combine the rows based on a join condition.
wether to use SQL or SSIS does not matter, first thing is to know WHAT you are doing, and then we can find out HOW to do it.

please show example data of the 2 sql, and the example of the same data as you want to have it in the end result.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

zolfAuthor Commented:
I have attached the screenshot details of my requirements

1
0
PortletPaulfreelancerCommented:
Zolf, it LOOKS like a UNION ALL could be performed. BUT, I warn you that ONLY YOU can inform us if this is correct or not. We do not know your requirements, the business rules nor are we familiar with the data.

union ALL
UNION ALL literally appends ALL of the results together.
UNION by itself will remove lines that are exactly the same.
0
zolfAuthor Commented:
Paul:

Thanks for your feedback. You see I have now created 2 Views from those 2 queries. Now I need to query these 2 views to produce my result columns.
I tried using SSIS but the end result is not correct which is obvious i.e. those 4 columns which contain numbers are different and not same in those 2 tables.

1
0
PortletPaulfreelancerCommented:
Actually the images and your queries are not the same. the second query does not have 2 numeric columns, the 5th column is in bold

SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + cast (dbo.Tafsil.code as  varchar(50)) AS ProductCode,
 dbo.Tafsil.description as ProductName,
    dbo.Branch.name as BranchCode,
   SUM(dbo.SaleReturnDetail.quantity) AS SalesReturnQty,
    (CASE
     WHEN (dbo.SaleDetail.type = 0 ) THEN 'Sales'
                                            ELSE 'Discount'
 END) as SaleType

 
But let's assume that was just a mistake and the images you shared are correct


query 1
ProductCode, ProductName, BranchOffice, SalesQtyinclDis, Discount
123                   blah                   aplace               10                        100


query 2
ProductCode, ProductName, BranchOffice, SalesReturnQty, ???????
123                   blah                   aplace              12                          45

CAN query 1 and query 2 have the SAME first 3 columns?

IF they CAN do this, what DO YOU WANT to do with the numeric columns?

Should the examples end up this way:
ProductCode, ProductName, BranchOffice, SalesQtyinclDis, Discount
123                   blah                   aplace              22                         145

or this way:
ProductCode, ProductName, BranchOffice, SalesQtyinclDis, Discount, SalesReturnQty, ???????
123                   blah                   aplace               10                        100          12                          45
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think you want to UNION ALL and then on top do a GROUP BY...

select productcode, productname, branchoffice, sum(sales), sum(discount)
 from ( select  * from view1 
   union all select * from view2
  )  sq
group by productcode, productname, branchoffice 

Open in new window

0
zolfAuthor Commented:
Thanks guys for your feedbacks!!

Paul Maxwell:

You are correct I made a mistake in my second Query,I forgot to update the query. It should have been like this

with q as (
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + cast (dbo.Tafsil.code as  varchar(50)) AS ProductCode,
 dbo.Tafsil.description as ProductName,
    dbo.Branch.name as BranchCode,
    SUM(dbo.SaleReturnDetail.quantity) AS SalesReturnQty,
    (CASE 
     WHEN (dbo.SaleDetail.type = 0 ) THEN 'Sales'
                                            ELSE 'Discount' 
 END) as SaleType
   
FROM
    dbo.Receipt
INNER JOIN
    dbo.SaleReturn
ON
    (
        dbo.Receipt.id = dbo.SaleReturn.invoiceReceiptId)
INNER JOIN
    dbo.SaleReturnDetail
ON
    (
        dbo.SaleReturn.id = dbo.SaleReturnDetail.saleReturnId)
INNER JOIN
    dbo.SaleDetail
ON
    (
        dbo.SaleReturnDetail.saleDetailId = dbo.SaleDetail.id)
INNER JOIN
    dbo.Batch Batch_alias1
ON
    (
        dbo.SaleDetail.batchId = Batch_alias1.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        Batch_alias1.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= '2015-06-22'
AND dbo.Receipt.receiptDate < '2015-07-23'
GROUP BY
    dbo.SaleDetail.type,
    dbo.Tafsil.description,
    dbo.Branch.name,
     kol.code,
    Mohin.code,
    Tafsil.code)
select q1.ProductCode,
q1.ProductName,
q1.BranchCode,
q1.SalesReturnQty AS sales,
q2.SalesReturnQty AS discount
FROM q as q1 INNER JOIN q as q2
ON q1.ProductCode =  q2.ProductCode
 AND q1.ProductName = q2.ProductName
 AND q1.BranchCode = q2.BranchCode
 AND
q1.SaleType = 'Sales' AND q2.SaleType = 'Discount'

                                          

Open in new window


query 1
ProductCode, ProductName, BranchOffice, SalesQtyinclDis, Discount
123                   blah                   aplace               10                        100


query 2
ProductCode, ProductName, BranchOffice, Sales, Discount
123                   blah                   aplace              12                          45

Should the examples end up this way:

It should end up like this

ProductCode, ProductName, BranchOffice, SalesQtyinclDis, Discount, Sales, Discount
123                   blah                   aplace               10                        100          12                          45


To make things easy and achieve my goal I have created 2 View of those 2 queries. Maybe I am wrong.


Guy Hengel [angelIII / a3]

The Select should also include those other colums which I have mentioned above in my this comments. So can you please tel me how will it be!!
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you want that output, join need to join like Paul indicated, and you can simply add the columns in the output by adding as needed into the main select statment and to use aliases to get the output columns clearly indicating where they come from (and avoid some errors also)
1
zolfAuthor Commented:
Can you please elaborate...Do you mean like so

select productcode, productname, branchoffice, sum(sales), sum(discount),sum(SalesQtyinclDis),sum(Discount)
 from ( select  * from ViewProductSalesNReturnFDO 
   union all select * from ViewProductSalesNDiscountFDO
  )  sq
group by productcode, productname, branchoffice 

Open in new window

0
zolfAuthor Commented:
I have attached the data from the 2 views only I have removed the product name from it but the product code is available and other cols. Please help me to achieve my goal.
ViewProductSalesNDiscountFDO.xlsx
ViewProductSalesNReturnFDO.xls
0
PortletPaulfreelancerCommented:
Zolf, I will (I hope) assist you to combine these queries, but I fear we are producing something that is not efficient.

This is what I suggest, there are 2 options.

Option A: FULL OUTER JOIN
SELECT
      COALESCE(q1.productcode, q2.productcode) productcode
    , COALESCE(q1.productname, q2.productname) productname
    , COALESCE(q1.branchoffice, q2.branchoffice) branchoffice
    , q1.sales
    , q1.discount AS discount1
    , q2.SalesQtyinclDis
    , q2.discount AS discount2
FROM query1 q1
FULL OUTER JOIN query2 q2
      ON q1.productcode = q2.productcode
      AND q1.productname = q2.productname
      AND q1.branchoffice = q2.branchoffice
;

Open in new window



Option B: UNION ALL & GROUP BY
The "trick" with UNION ALL is that BOTH all subqueries MUST produce the same number of columns, and the columns MUST be the SAME type (or compatible types). If these rules are not met it will error
SELECT
      productcode
    , productname
    , branchoffice
    , SUM(sales)
    , SUM(discount1)
    , SUM(SalesQtyinclDis)
    , SUM(Discount2)
FROM (
      SELECT
          productcode
        , productname
        , branchoffice
        , sales    
        , discount as discount1
        , cast(null as int) as SalesQtyinclDis
        , cast(null as decimal(12,3)) as Discount2
      FROM ViewProductSalesNReturnFDO
      UNION ALL
      SELECT
          productcode
        , productname
        , branchoffice
        , null    
        , null
        , SalesQtyinclDis
        , discount as Discount2
      FROM ViewProductSalesNDiscountFDO
) sq
GROUP BY productcode
       , productname
       , branchoffice

Open in new window

also note you should not have a query that uses the same name in more than 1 column (i.e. discount)

{+edit} I did this before the files were made available.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zolfAuthor Commented:
Paul:

Thanks a lot for your help, When I run the first query I get this error

Invalid object name 'query1'.
0
zolfAuthor Commented:
Thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.