Link to home
Start Free TrialLog in
Avatar of Zolf
ZolfFlag for United Arab Emirates

asked on

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

User generated image
Avatar of chaau
chaau
Flag of Australia image

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

Avatar of Zolf

ASKER

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

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

Avatar of Zolf

ASKER

Thanks for your feedbacks, but I get this error

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

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.
Avatar of Zolf

ASKER

It returns an
empty set
Avatar of Zolf

ASKER

Can you please give me the full query
Avatar of Zolf

ASKER

I changed all the INNER JOIN to LEFT OUTER JOIN but no luck....it returns empty set
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
SOLUTION
Avatar of chaau
chaau
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zolf

ASKER

Thanks for your feedbacks but I get this error

Incorrect syntax near 'q1'.
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

Avatar of Zolf

ASKER

Vikas Garg:

Thanks for your feedback, it did work!! What was the issue
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zolf

ASKER

Appreciate your help!!