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

zolfAuthor Commented:
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
0
ArgentiCommented:
You just try UNION

[your first SQL query]

UNION

[your second SQL query]
0
Tony303Commented:
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.
0
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 the feedbacks.

by: Tony303
can you please post the query with your correction. as it is not clear to me
0
zolfAuthor Commented:
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.
0
Terry WoodsIT GuruCommented:
One query has:
SELECT
    dbo.Branch.NAME,
    tafsil.id ,
    dbo.Tafsil.description ,
    SUM(dbo.SaleDetail.quantity) AS soldQty ,
    dbo.FinishProduct.brandname ,
    dbo.FinishProduct.genericname ,
    dbo.FinishProduct.ean           AS brandcode ,
    dbo.FinishProduct.universalcode AS genericcode ,
    dbo.Supplier.companyName

Open in new window


and the other has:
SELECT
    dbo.Branch.name        AS Branch,
    dbo.Tafsil.id,
    dbo.Tafsil.description AS Product_Name,
    dbo.FinishProduct.brandName,
    SUM(dbo.WarehouseProductQuantity.actualQuantity -
    dbo.WarehouseProductQuantity.reservedQuantity) AS sum_AvailQty,
    dbo.Supplier.companyNameAlternate

Open in new window


You need to matching the number and order of columns (and their datatype!) to use a union. If you add some fixed values to the latter query for the missing columns (or just add the columns if you can, and want to), it should work:
SELECT
    dbo.Branch.name        AS Branch,
    dbo.Tafsil.id,
    dbo.Tafsil.description AS Product_Name,
0 as soldQty,
    dbo.FinishProduct.brandName,
'' as genericname,
    SUM(dbo.WarehouseProductQuantity.actualQuantity -
    dbo.WarehouseProductQuantity.reservedQuantity) AS sum_AvailQty,
    dbo.Supplier.companyNameAlternate

Open in new window


ps: if you accept my answer, please split the points.
0
zolfAuthor Commented:
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.
0
zolfAuthor Commented:
by: TerryAtOpus

I made the changes as you intructed but I get loads of error. so I think I am dropping this UNION suggestion
0
Tony303Commented:
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;
0
zolfAuthor Commented:
by: Tony303

no it did not help.
0
zolfAuthor Commented:
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

0
zolfAuthor Commented:
here is the screenshot to make it easy to visualise my issue
9-4-2013-11-31-56-AM.gif
0
Tony303Commented:
Here is the union....

Remember these union rules too...
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.


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
0
zolfAuthor Commented:
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
0
zolfAuthor Commented:
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!!
0
Tony303Commented:
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
0
Tony303Commented:
Hey zolf,

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

Tony
0
zolfAuthor Commented:
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.
0
Tony303Commented:
I think it is my Internet Explorer. Grrrr
Looks normal in Chrome.
0
zolfAuthor Commented:
Tony,

no luck in that new query of yours. it still shows in 2 records.
0
Tony303Commented:
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
0
Tony303Commented:
SELECT a.Name,
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 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.CompanyName
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:
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
0
zolfAuthor Commented:
please help!!
0
Tony303Commented:
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
0
zolfAuthor Commented:
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
0
Tony303Commented:
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
0
zolfAuthor Commented:
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
0
zolfAuthor Commented:
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
0
zolfAuthor Commented:
Tony,

my mistake....got it!!

thanks a lot. Appreciate your time and help.
0
Tony303Commented:
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.
0
zolfAuthor Commented:
cheers!!
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.