Link to home
Start Free TrialLog in
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
Avatar of Zolf
Zolf
Flag of United Arab Emirates image

ASKER

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

ASKER

thanks for the feedbacks.

by: Tony303
can you please post the query with your correction. as it is not clear to me
Avatar of Zolf

ASKER

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

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

ASKER

by: TerryAtOpus

I made the changes as you intructed but I get loads of error. so I think I am dropping this UNION suggestion
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;
Avatar of Zolf

ASKER

by: Tony303

no it did not help.
Avatar of Zolf

ASKER

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

Avatar of Zolf

ASKER

here is the screenshot to make it easy to visualise my issue
9-4-2013-11-31-56-AM.gif
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

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

ASKER

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!!
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
Hey zolf,

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

Tony
Avatar of Zolf

ASKER

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.
I think it is my Internet Explorer. Grrrr
Looks normal in Chrome.
Avatar of Zolf

ASKER

Tony,

no luck in that new query of yours. it still shows in 2 records.
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
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

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

ASKER

please help!!
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
Avatar of Zolf

ASKER

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

ASKER

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

ASKER

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

ASKER

Tony,

my mistake....got it!!

thanks a lot. Appreciate your time and help.
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.
Avatar of Zolf

ASKER

cheers!!