SQL query Report generation

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

But the 3. Sales Returned Qty and 4. Discount Returned Qty of the second query are returned in separate rows and not in different column. How can I show them in different columns. I have attached a screenshot of what I mean by this. Then my second question is how can I also combine the two into one query. I have read that I will need to use UNION but it is very complex in my case. Is there some other way to manage this issue. But first I would like to solve my first issue.Please help!!

cheers
Zolf

1
zolfAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

chaauCommented:
You need to JOIN your two sub-queries:
SELECT q1.ProductCode, q1.ProductName, q1.BranchCode, q1.Sales, q2.Discount
FROM q1 INNER JOIN q2
ON q1.ProductCode =  q2.ProductCode
 AND q1.ProductName = q2.ProductName
 AND q1.BranchCode = q2.BranchCode

Open in new window

zolfAuthor Commented:
my apologies forgot to attache my second query which returns those results

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

chaauCommented:
That's better. The following is what you are after: You need to join the saleDetail twice, like this:
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(s.quantity) AS SalesReturnQty as Sales,
    SUM(d.quantity) AS SalesReturnQty as Discount   
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 as s
ON
    (
        dbo.SaleReturnDetail.saleDetailId = s.id)
INNER JOIN
    dbo.SaleDetail as d
ON
    (
        dbo.SaleReturnDetail.saleDetailId = d.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'
AND s.type = 0 AND d.type <> 0
GROUP BY
    dbo.Tafsil.description,
    dbo.Branch.name,
     kol.code,
    Mohin.code,
    Tafsil.code ;

                                          

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

zolfAuthor Commented:
Thanks for your feedbacks, but I get this error

The multi-part identifier "dbo.SaleDetail.batchId" could not be bound.
chaauCommented:
Yes, sorry. You need to use the aliases s or d, like this:
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(s.quantity) AS SalesReturnQty as Sales,
    SUM(d.quantity) AS SalesReturnQty as Discount   
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 as s
ON
    (
        dbo.SaleReturnDetail.saleDetailId = s.id)
INNER JOIN
    dbo.SaleDetail as d
ON
    (
        dbo.SaleReturnDetail.saleDetailId = d.id)
INNER JOIN
    dbo.Batch Batch_alias1
ON
    (
        s.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'
AND s.type = 0 AND d.type <> 0
GROUP BY
    dbo.Tafsil.description,
    dbo.Branch.name,
     kol.code,
    Mohin.code,
    Tafsil.code ;

Open in new window

chaauCommented:
Also, just wanted to add this: If you have the orders with only "Sales" or only "Discounts" you need to use the LEFT JOINS for the s and d aliases to make sure you are not missing the orders.
zolfAuthor Commented:
It returns an
empty set
zolfAuthor Commented:
Can you please give me the full query
zolfAuthor Commented:
I changed all the INNER JOIN to LEFT OUTER JOIN but no luck....it returns empty set
chaauCommented:
I think you will need to provide the sample data for the result set you have provided. I would prefer if the sample data is in the text format rather than a picture snapshot
chaauCommented:
If you can't sent your sample data then use this, not ideal, but should work:
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
q1.SaleType = 'Sales' AND q2.SaleType = 'Discount'

Open in new window

zolfAuthor Commented:
Thanks for your feedbacks but I get this error

Incorrect syntax near 'q1'.
Vikas GargAssociate Principal EngineerCommented:
Hope this would help

try 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

zolfAuthor Commented:
Vikas Garg:

Thanks for your feedback, it did work!! What was the issue
Vikas GargAssociate Principal EngineerCommented:
Hi,

You missed to and "AND" before this condition in the Join condition of q1 and q2

q1.SaleType = 'Sales' AND q2.SaleType = 'Discount'

Previously it was like

FROM q as q1 INNER JOIN q as q2
ON q1.ProductCode =  q2.ProductCode
 AND q1.ProductName = q2.ProductName
 AND q1.BranchCode = q2.BranchCode

q1.SaleType = 'Sales' AND q2.SaleType = 'Discount'

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:
Appreciate your help!!
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.