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?
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

0
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

0
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

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:
Thanks for your feedbacks, but I get this error

The multi-part identifier "dbo.SaleDetail.batchId" could not be bound.
0
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

0
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.
0
zolfAuthor Commented:
It returns an
empty set
0
zolfAuthor Commented:
Can you please give me the full query
0
zolfAuthor Commented:
I changed all the INNER JOIN to LEFT OUTER JOIN but no luck....it returns empty set
0
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
0
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

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

Incorrect syntax near 'q1'.
0
Vikas GargBusiness Intelligence DeveloperCommented:
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

0
zolfAuthor Commented:
Vikas Garg:

Thanks for your feedback, it did work!! What was the issue
0
Vikas GargBusiness Intelligence DeveloperCommented:
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'
0

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