Avatar of Zolf
ZolfFlag for United Arab Emirates asked on

Integrate two queries

Hello there,

I have this 2 queries which I want to integrate together. But I tried a lot but I don't get the same result or it takes lot and timeout's. So I am thinking to run the query separately and then integrate them together. can somebody help me achieve this. I am using mssql 2008.

I want to show the sum_AvailQty col in the productsale query result

My 2 queries I have attached.
9-3-2013-12-10-53-PM.gif
productqty.txt
productsales.txt
Microsoft SQL ServerMicrosoft SQL Server 2008SSISSQL

Avatar of undefined
Last Comment
Zolf

8/22/2022 - Mon
ASKER
Zolf

I tried to integrate this 2 queries like this, I get close to my result by the problem is it shows other qty from other branches also in the result . please see attached shot and the updated query.
9-3-2013-1-10-51-PM.gif
11.txt
SOLUTION
Argenti

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Tony303

Is the reference to Warehouse in there twice the issue?

Remark out

      INNER JOIN dbo.Warehouse w
ON (dbo.WarehouseProductQuantity.warehouseId = w.id)

and see what output comes.
ASKER
Zolf

thanks for the feedbacks.

by: Tony303
can you please post the query with your correction. as it is not clear to me
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
Zolf

by: Argenti

your comment did not work.i get this error


All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Zolf

by: TerryAtOpus

thanks for your feedbacks. I will try it. But can you provide me with some good suggestion or solution how can I manage to create a report from these two queries to show as 1 report. the unique cols are the soldqty and available qty of the products in each branch office. do I run these two queries indivially and then run another query to combine these into one. for e.g create 2 views and then run 3rd query on these two views. I just cant get it. I can also use SSIS if you think it will solve my issue.
ASKER
Zolf

by: TerryAtOpus

I made the changes as you intructed but I get loads of error. so I think I am dropping this UNION suggestion
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Tony303

Try this zolf.
To remark code out, put "--" in front of the line you want not to run.

SELECT
    dbo.Branch.NAME,
    tafsil.id,
    dbo.Tafsil.description,
    SUM(dbo.SaleDetail.quantity) AS soldQty,
    dbo.FinishProduct.brandname,
    dbo.Supplier.companyName,
    dbo.WarehouseProductQuantity.actualQuantity
FROM
    dbo.Batch
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.SaleDetail
ON
    (
        dbo.Batch.id = dbo.SaleDetail.batchId)
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.Warehouse
ON
    (
        dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
    dbo.FinishProduct
ON
    (
        dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
    dbo.Supplier
ON
    (
        dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
    dbo.WarehouseProductQuantity
ON
    (
        dbo.Tafsil.id = dbo.WarehouseProductQuantity.tafsilId)
INNER JOIN
    dbo.Warehouse w
ON
    (
        dbo.WarehouseProductQuantity.warehouseId = w.id)
WHERE
    dbo.Receipt.receiptDate >= '2013-03-21'
AND dbo.Receipt.receiptDate < CONVERT(DATE, GETDATE())
GROUP BY
    dbo.Branch.NAME,
    dbo.Tafsil.description,
    dbo.FinishProduct.brandname,
    dbo.FinishProduct.genericname,
    dbo.FinishProduct.ean,
    dbo.FinishProduct.universalcode,
    dbo.Supplier.companyName ,
    dbo.WarehouseProductQuantity.actualQuantity,
    tafsil.id;

SELECT
    dbo.Branch.NAME,
    tafsil.id,
    dbo.Tafsil.description,
    SUM(dbo.SaleDetail.quantity) AS soldQty,
    dbo.FinishProduct.brandname,
    dbo.Supplier.companyName,
    dbo.WarehouseProductQuantity.actualQuantity
FROM
    dbo.Batch
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.SaleDetail
ON
    (
        dbo.Batch.id = dbo.SaleDetail.batchId)
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.Warehouse
ON
    (
        dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
    dbo.FinishProduct
ON
    (
        dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
    dbo.Supplier
ON
    (
        dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
    dbo.WarehouseProductQuantity
ON
    (
        dbo.Tafsil.id = dbo.WarehouseProductQuantity.tafsilId)
--INNER JOIN
   -- dbo.Warehouse w
--ON
   -- (
      --  dbo.WarehouseProductQuantity.warehouseId = w.id)
WHERE
    dbo.Receipt.receiptDate >= '2013-03-21'
AND dbo.Receipt.receiptDate < CONVERT(DATE, GETDATE())
GROUP BY
    dbo.Branch.NAME,
    dbo.Tafsil.description,
    dbo.FinishProduct.brandname,
    dbo.FinishProduct.genericname,
    dbo.FinishProduct.ean,
    dbo.FinishProduct.universalcode,
    dbo.Supplier.companyName ,
    dbo.WarehouseProductQuantity.actualQuantity,
    tafsil.id;
ASKER
Zolf

by: Tony303

no it did not help.
ASKER
Zolf

by: TerryAtOpus

I managed to run the UNION query and I get close to my result bu the issue is I get 2 records for the product one with 0 soldqty and correct available qty and another record with 0 available qty and correct soldqty. I need to show both of this in one record.below is my working query but not quite my requirement


SELECT
    dbo.Branch.name        AS Branch,
    dbo.Tafsil.description AS Product_Name,
    FinishProduct.brandName,
    SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity
    ) AS sum_AvailQty,
    dbo.Supplier.companyNameAlternate,
    0 AS soldQty
FROM
    dbo.WarehouseSubType
INNER JOIN
    dbo.WarehouseType
ON
    (
        dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN
    dbo.WarehouseSubType1
ON
    (
        dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN
    dbo.Warehouse
ON
    (
        dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
    dbo.WarehouseProductQuantity
ON
    (
        dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
    dbo.FinishProduct
ON
    (
        dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
    dbo.Supplier
ON
    (
        dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE
    dbo.WarehouseSubType1.warehouseSubTypeId IN (1,
                                                 4)
GROUP BY
    dbo.Branch.name,
    dbo.Tafsil.description,
    dbo.Supplier.companyNameAlternate,
    FinishProduct.brandName

UNION

SELECT 
dbo.Branch.name        AS Branch,
    dbo.Tafsil.description AS Product_Name,
    FinishProduct.brandName,
    0 AS sum_AvailQty,
    '' as companyNameAlternate,
     sum(dbo.SaleDetail.quantity) AS soldQty

FROM dbo.Batch
INNER JOIN dbo.Tafsil ON (dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN dbo.SaleDetail ON (dbo.Batch.id = dbo.SaleDetail.batchId)
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.Warehouse ON (dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN dbo.Branch ON (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        dbo.Tafsil.id = dbo.TafsilLink.sourceId)
        INNER JOIN
    dbo.FinishProduct
ON
    (
        dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
        INNER JOIN
    dbo.Supplier
ON
    (
        dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)

WHERE dbo.Receipt.receiptDate >= '2013-03-21'
    AND dbo.Receipt.receiptDate < CONVERT(DATE, GETDATE())

GROUP BY

dbo.Branch.name ,
    dbo.Tafsil.description,
    FinishProduct.brandName

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
Zolf

here is the screenshot to make it easy to visualise my issue
9-4-2013-11-31-56-AM.gif
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Zolf

by: Tony303

the query works good but the issue I have is that I want the products available qty and sold qty in one record. now it is showing in different records. please see the screenshot to understand my issue with the present union query. I am very close to my solution only that we need to combine the 2 records into 1
9-4-2013-12-06-02-PM.gif
ASKER
Zolf

can we do this....suppose I create a view of this union query and then run another query to get this 2 records in one record..is it possible!!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Tony303

SELECT a.Name,
a.ID,
a.ProductName,
SUM(a.soldQty) AS soldQty,
a.BrandName,
a.GenericName,
a.BrandName,
a.GenericName,
SUM(a.AvailQty) as AvailQty,
a.CompanyName

FROM
(SELECT
    dbo.Branch.NAME                              AS Name,
    tafsil.id                                    AS ID,
    dbo.Tafsil.description                  AS ProductName,
    SUM(dbo.SaleDetail.quantity)      AS soldQty,
    dbo.FinishProduct.brandname            AS BrandName,
    dbo.FinishProduct.genericname      AS GenericName,
    dbo.FinishProduct.ean           AS BrandCode,
    dbo.FinishProduct.universalcode AS GenericCode,
    0                                                AS AvailQty,
    dbo.Supplier.companyName            AS CompanyName
FROM dbo.Batch INNER JOIN dbo.Tafsil
ON      (dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN dbo.SaleDetail
ON      (dbo.Batch.id = dbo.SaleDetail.batchId)
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.Warehouse
ON      (dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN dbo.Branch
ON      (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.TafsilLink
ON      (dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN      dbo.FinishProduct
ON      (dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN dbo.Supplier
ON      (dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE
    dbo.Receipt.receiptDate >= '2013-03-21' AND dbo.Receipt.receiptDate < CONVERT(DATE, GETDATE())
GROUP BY
    dbo.Branch.NAME,
    tafsil.id ,
    dbo.Tafsil.description ,
    dbo.FinishProduct.brandname ,
    dbo.FinishProduct.genericname ,
    dbo.Supplier.companyName ,
    dbo.FinishProduct.ean ,
    dbo.FinishProduct.universalcode

UNION

SELECT
    dbo.Branch.name                                          AS Branch,
    dbo.Tafsil.id                                          AS ID,
    dbo.Tafsil.description                              AS ProductName,
    0                                                            AS soldQty,
    dbo.FinishProduct.brandName                        AS BrandName,
    NULL                                                      AS GenericName,
    NULL                                                      AS BrandCode,
    NULL                                                      AS GenericCode,
    SUM(dbo.WarehouseProductQuantity.actualQuantity -
    dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
    dbo.Supplier.companyNameAlternate            AS CompanyName

FROM dbo.WarehouseSubType INNER JOIN dbo.WarehouseType
ON (dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN dbo.WarehouseSubType1
ON (dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN dbo.Warehouse
ON (dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN dbo.Branch
ON (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.WarehouseProductQuantity
ON (dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN dbo.Tafsil
ON (dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN dbo.TafsilLink
ON (dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN dbo.FinishProduct
ON (dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN dbo.Supplier
ON (dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
GROUP BY
    dbo.Branch.name,
    dbo.Tafsil.description,
    dbo.FinishProduct.brandName,
    dbo.Supplier.companyNameAlternate,
    dbo.Tafsil.id
   
    ) a
   
    GROUP BY
a.Name,
a.ID,
a.ProductName,
a.BrandName,
a.GenericName,
a.BrandName,
a.GenericName,
a.CompanyName
Tony303

Hey zolf,

Have all these answers now shown up in Experts-Exchange as BOLD and ITALIC?
Or it is just my computer?

Tony
ASKER
Zolf

no it does not show as BOLD and ITALIC. by the way I also have this issue i.e. when I am typing in the post it shows as BOLD.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Tony303

I think it is my Internet Explorer. Grrrr
Looks normal in Chrome.
ASKER
Zolf

Tony,

no luck in that new query of yours. it still shows in 2 records.
Tony303

OK.

I have replaced the NULL with blank.
See if that is OK.
If not I'll send you another query

SELECT a.Name,
a.ID,
a.ProductName,
SUM(a.soldQty) AS soldQty,
a.BrandName,
a.GenericName,
a.BrandName,
a.GenericName,
SUM(a.AvailQty) as AvailQty,
a.CompanyName

FROM
(SELECT
    dbo.Branch.NAME                              AS Name,
    tafsil.id                                    AS ID,
    dbo.Tafsil.description                  AS ProductName,
    SUM(dbo.SaleDetail.quantity)      AS soldQty,
    dbo.FinishProduct.brandname            AS BrandName,
    dbo.FinishProduct.genericname      AS GenericName,
    dbo.FinishProduct.ean           AS BrandCode,
    dbo.FinishProduct.universalcode AS GenericCode,
    0                                                AS AvailQty,
    dbo.Supplier.companyName            AS CompanyName
FROM dbo.Batch INNER JOIN dbo.Tafsil
ON      (dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN dbo.SaleDetail
ON      (dbo.Batch.id = dbo.SaleDetail.batchId)
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.Warehouse
ON      (dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN dbo.Branch
ON      (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.TafsilLink
ON      (dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN      dbo.FinishProduct
ON      (dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN dbo.Supplier
ON      (dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE
    dbo.Receipt.receiptDate >= '2013-03-21' AND dbo.Receipt.receiptDate < CONVERT(DATE, GETDATE())
GROUP BY
    dbo.Branch.NAME,
    tafsil.id ,
    dbo.Tafsil.description ,
    dbo.FinishProduct.brandname ,
    dbo.FinishProduct.genericname ,
    dbo.Supplier.companyName ,
    dbo.FinishProduct.ean ,
    dbo.FinishProduct.universalcode

UNION

SELECT
    dbo.Branch.name                                          AS Branch,
    dbo.Tafsil.id                                          AS ID,
    dbo.Tafsil.description                              AS ProductName,
    0                                                            AS soldQty,
    dbo.FinishProduct.brandName                        AS BrandName,
    ''                                                      AS GenericName,
    ''                                                      AS BrandCode,
    ''                                                      AS GenericCode,
    SUM(dbo.WarehouseProductQuantity.actualQuantity -
    dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
    dbo.Supplier.companyNameAlternate            AS CompanyName

FROM dbo.WarehouseSubType INNER JOIN dbo.WarehouseType
ON (dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN dbo.WarehouseSubType1
ON (dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN dbo.Warehouse
ON (dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN dbo.Branch
ON (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.WarehouseProductQuantity
ON (dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN dbo.Tafsil
ON (dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN dbo.TafsilLink
ON (dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN dbo.FinishProduct
ON (dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN dbo.Supplier
ON (dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
GROUP BY
    dbo.Branch.name,
    dbo.Tafsil.description,
    dbo.FinishProduct.brandName,
    dbo.Supplier.companyNameAlternate,
    dbo.Tafsil.id
   
    ) a
   
    GROUP BY
a.Name,
a.ID,
a.ProductName,
a.BrandName,
a.GenericName,
a.BrandName,
a.GenericName,
a.CompanyName
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Zolf

no luck the branch/product are still showing in 2 records. we need to combine the record so that  the soldqty and availableqty are shown I one record
ASKER
Zolf

please help!!
Tony303

I understand the problem.
I can't see the answer yet.

A small code change here...
Try it...

SELECT a.Branch,
a.ID,
a.ProductName,
SUM(a.soldQty) AS soldQty,
a.BrandName,
MAX(a.GenericName) AS GenericName,
MAX(a.BrandName) AS BrandName,
MAX(a.GenericCode) AS GenericCode,
SUM(a.AvailQty) as AvailQty,
a.CompanyName

FROM
(SELECT
    dbo.Branch.NAME                              AS Branch,
    tafsil.id                                    AS ID,
    dbo.Tafsil.description                  AS ProductName,
    SUM(dbo.SaleDetail.quantity)      AS soldQty,
    dbo.FinishProduct.brandname            AS BrandName,
    dbo.FinishProduct.genericname      AS GenericName,
    dbo.FinishProduct.ean           AS BrandCode,
    dbo.FinishProduct.universalcode AS GenericCode,
    0                                                AS AvailQty,
    dbo.Supplier.companyName            AS CompanyName
FROM dbo.Batch INNER JOIN dbo.Tafsil
ON      (dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN dbo.SaleDetail
ON      (dbo.Batch.id = dbo.SaleDetail.batchId)
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.Warehouse
ON      (dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN dbo.Branch
ON      (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.TafsilLink
ON      (dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN      dbo.FinishProduct
ON      (dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN dbo.Supplier
ON      (dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE
    dbo.Receipt.receiptDate >= '2013-03-21' AND dbo.Receipt.receiptDate < CONVERT(DATE, GETDATE())
GROUP BY
    dbo.Branch.NAME,
    tafsil.id ,
    dbo.Tafsil.description ,
    dbo.FinishProduct.brandname ,
    dbo.FinishProduct.genericname ,
    dbo.Supplier.companyName ,
    dbo.FinishProduct.ean ,
    dbo.FinishProduct.universalcode

UNION

SELECT
    dbo.Branch.name                                          AS Branch,
    dbo.Tafsil.id                                          AS ID,
    dbo.Tafsil.description                              AS ProductName,
    0                                                            AS soldQty,
    dbo.FinishProduct.brandName                        AS BrandName,
    NULL                                                      AS GenericName,
    NULL                                                     AS BrandCode,
    NULL                                                      AS GenericCode,
    SUM(dbo.WarehouseProductQuantity.actualQuantity -
    dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
    dbo.Supplier.companyNameAlternate            AS CompanyName

FROM dbo.WarehouseSubType INNER JOIN dbo.WarehouseType
ON (dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN dbo.WarehouseSubType1
ON (dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN dbo.Warehouse
ON (dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN dbo.Branch
ON (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.WarehouseProductQuantity
ON (dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN dbo.Tafsil
ON (dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN dbo.TafsilLink
ON (dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN dbo.FinishProduct
ON (dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN dbo.Supplier
ON (dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
GROUP BY
    dbo.Branch.name,
    dbo.Tafsil.description,
    dbo.FinishProduct.brandName,
    dbo.Supplier.companyNameAlternate,
    dbo.Tafsil.id
   
    ) a
   
    GROUP BY
a.Branch,
a.ID,
a.ProductName,
a.BrandName,
a.CompanyName
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
Zolf

no that did not work. but I made a small edit to your query and got the result as I was expecting but the query collapses when I try to add other cols

SELECT 
a.Name,
a.ID,
MAX(a.soldQty) AS soldQty,
MAX(a.AvailQty) as AvailQty

FROM 
(SELECT
    dbo.Branch.NAME                              AS Name,
    tafsil.id                                    AS ID,
    dbo.Tafsil.description                  AS ProductName,
    SUM(dbo.SaleDetail.quantity)      AS soldQty,
    dbo.FinishProduct.brandname            AS BrandName,
    dbo.FinishProduct.genericname      AS GenericName,
    dbo.FinishProduct.ean           AS BrandCode,
    dbo.FinishProduct.universalcode AS GenericCode,
    0                                                AS AvailQty,
    dbo.Supplier.companyName            AS CompanyName
FROM dbo.Batch INNER JOIN dbo.Tafsil
ON      (dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN dbo.SaleDetail
ON      (dbo.Batch.id = dbo.SaleDetail.batchId)
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.Warehouse
ON      (dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN dbo.Branch
ON      (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.TafsilLink
ON      (dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN      dbo.FinishProduct
ON      (dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN dbo.Supplier
ON      (dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE
    dbo.Receipt.receiptDate >= '2013-03-21' AND dbo.Receipt.receiptDate < CONVERT(DATE, GETDATE())
GROUP BY
    dbo.Branch.NAME,
    tafsil.id ,
    dbo.Tafsil.description ,
    dbo.FinishProduct.brandname ,
    dbo.FinishProduct.genericname ,
    dbo.Supplier.companyName ,
    dbo.FinishProduct.ean ,
    dbo.FinishProduct.universalcode

UNION

SELECT
    dbo.Branch.name                                          AS Branch,
    dbo.Tafsil.id                                          AS ID,
    dbo.Tafsil.description                              AS ProductName,
    0                                                            AS soldQty,
    dbo.FinishProduct.brandName                        AS BrandName,
    NULL                                                      AS GenericName,
    NULL                                                     AS BrandCode,
    NULL                                                      AS GenericCode,
    SUM(dbo.WarehouseProductQuantity.actualQuantity -
    dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
    dbo.Supplier.companyNameAlternate            AS CompanyName

FROM dbo.WarehouseSubType INNER JOIN dbo.WarehouseType
ON (dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN dbo.WarehouseSubType1
ON (dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN dbo.Warehouse
ON (dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN dbo.Branch
ON (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.WarehouseProductQuantity
ON (dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN dbo.Tafsil
ON (dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN dbo.TafsilLink
ON (dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN dbo.FinishProduct
ON (dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN dbo.Supplier
ON (dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
GROUP BY
    dbo.Branch.name,
    dbo.Tafsil.description,
    dbo.FinishProduct.brandName,
    dbo.Supplier.companyNameAlternate,
    dbo.Tafsil.id 
    
    ) a
    
    GROUP BY
a.Name,
a.ID

Open in new window

9-4-2013-1-11-02-PM.gif
Tony303

I had a wee mistake.
This is your cut down version with my corrected mistake.
add fields to select and group by to expand as you were doing above.



SELECT
a.Branch,
a.ID,
MAX(a.soldQty) AS soldQty,
MAX(a.AvailQty) as AvailQty

FROM
(SELECT
    dbo.Branch.NAME                              AS Branch,
    tafsil.id                                    AS ID,
    dbo.Tafsil.description                  AS ProductName,
    SUM(dbo.SaleDetail.quantity)      AS soldQty,
    dbo.FinishProduct.brandname            AS BrandName,
    dbo.FinishProduct.genericname      AS GenericName,
    dbo.FinishProduct.ean           AS BrandCode,
    dbo.FinishProduct.universalcode AS GenericCode,
    0                                                AS AvailQty,
    dbo.Supplier.companyName            AS CompanyName
FROM dbo.Batch INNER JOIN dbo.Tafsil
ON      (dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN dbo.SaleDetail
ON      (dbo.Batch.id = dbo.SaleDetail.batchId)
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.Warehouse
ON      (dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN dbo.Branch
ON      (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.TafsilLink
ON      (dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN      dbo.FinishProduct
ON      (dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN dbo.Supplier
ON      (dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE
    dbo.Receipt.receiptDate >= '2013-03-21' AND dbo.Receipt.receiptDate < CONVERT(DATE, GETDATE())
GROUP BY
    dbo.Branch.NAME,
    tafsil.id ,
    dbo.Tafsil.description ,
    dbo.FinishProduct.brandname ,
    dbo.FinishProduct.genericname ,
    dbo.Supplier.companyName ,
    dbo.FinishProduct.ean ,
    dbo.FinishProduct.universalcode

UNION

SELECT
    dbo.Branch.name                                          AS Branch,
    dbo.Tafsil.id                                          AS ID,
    dbo.Tafsil.description                              AS ProductName,
    0                                                            AS soldQty,
    dbo.FinishProduct.brandName                        AS BrandName,
    NULL                                                      AS GenericName,
    NULL                                                     AS BrandCode,
    NULL                                                      AS GenericCode,
    SUM(dbo.WarehouseProductQuantity.actualQuantity -
    dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
    dbo.Supplier.companyNameAlternate            AS CompanyName

FROM dbo.WarehouseSubType INNER JOIN dbo.WarehouseType
ON (dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN dbo.WarehouseSubType1
ON (dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN dbo.Warehouse
ON (dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN dbo.Branch
ON (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.WarehouseProductQuantity
ON (dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN dbo.Tafsil
ON (dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN dbo.TafsilLink
ON (dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN dbo.FinishProduct
ON (dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN dbo.Supplier
ON (dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
GROUP BY
    dbo.Branch.name,
    dbo.Tafsil.description,
    dbo.FinishProduct.brandName,
    dbo.Supplier.companyNameAlternate,
    dbo.Tafsil.id
   
    ) a
   
    GROUP BY
a.Branch,
a.ID
ASKER
Zolf

ok I am adding 1 col at a time and these 2 col I added to the above query but still got the CORRECT result.But not the others

a.productname,
a.BrandName
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Zolf

what was the edit you did to your query,so I understand...let me try those cols which I get wrong result to your query and see the result
ASKER
Zolf

Tony,

my mistake....got it!!

thanks a lot. Appreciate your time and help.
Tony303

My early scripts had the alias of the first column as a different value.

SELECT
    dbo.Branch.name                                          AS Branch,

Early on I had the first union SELECT statement as

SELECT
    dbo.Branch.name                                          AS Name.

So the result has the same "Company" as a Branch and a Name, 2 entries as we saw.

Sorry this took so long, glad we got there in the end.

Bye.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Zolf

cheers!!