asked on

SQL Query Help

Hello there,

I have this query which I am using in my SSRS to generate reports,but now I have an additional requirement to show another column in the report and have got into problem.

SELECT
    dbo.SupplierProductDetail.companyName as companyName,
    dbo.SupplierProductDetail.companyNameAlternate as companyNameAlternate,
    dbo.FDOSales.ProductCode as ProductCode,
    dbo.FDOSales.ProductNameEng as ProductNameEng,
    dbo.FDOSales.ProductNamePer as ProductNamePer,
    dbo.FDOSales.SalesPrice as SalesPrice ,
    dbo.FDOSales.PurchasePrice as PurchasePrice ,
    SUM(dbo.FDOSales.SalesQtyinclDis) - SUM(dbo.FDOSales.SalesDiscount) AS SalesQty,
    SUM(dbo.FDOSales.SalesDiscount)   AS SalesDiscount,
    FDOSales.SalesPrice * (SUM(dbo.FDOSales.SalesQtyinclDis) - SUM(dbo.FDOSales.SalesDiscount)) AS TotalSalesPrice,
    FDOSales.PurchasePrice * (SUM(dbo.FDOSales.SalesQtyinclDis) - SUM(dbo.FDOSales.SalesDiscount)) AS TotalPurchasePrice
FROM
    dbo.FDOSales
INNER JOIN
    dbo.SupplierProductDetail
ON
    (
        dbo.FDOSales.ProductCode = dbo.SupplierProductDetail.ProductCode)
INNER JOIN
    dbo.FDOProvince
ON
    (
        dbo.FDOSales.ProvinceCode = dbo.FDOProvince.ProvinceCode)
WHERE
     SalesQtyinclDis &gt; 0 AND 
    dbo.FDOSales.SaleDate &gt;= '20151001'
AND dbo.FDOSales.SaleDate &lt;= '20151031'
GROUP BY
    dbo.SupplierProductDetail.companyName,
    dbo.SupplierProductDetail.companyNameAlternate,
    dbo.FDOSales.ProductCode,
    dbo.FDOSales.ProductNameEng,
    dbo.FDOSales.ProductNamePer,
    dbo.FDOSales.SalesPrice,
    dbo.FDOSales.PurchasePrice
ORDER BY
    dbo.SupplierProductDetail.companyName,    
    dbo.SupplierProductDetail.companyNameAlternate ASC,
    dbo.FDOSales.ProductNameEng,
    dbo.FDOSales.ProductNamePer ASC

Open in new window

and below is its relationship diagram for the above query

Now I have to get the available qty of the product from another table called ProductAvailable where I have the productcode col which I can use to create a link to the FDOSale table. Can somebody please help me to add this availableQty col also to the above query.

I tried to create a new dataset in SSRS and then try to add this to the group for the product but it seems I cannot mix different dataset in SSRS. So the only way out is to have one dataset with all these cols in one query.Please help.

cheers
Zolf

Zolf

ASKER

I tried to use UNION ALL, but still I have not got what I want. I get the result in different row i.e. one row has the sales qty and the other row its available Qty. I need to somehow integrate those 2 into one row.

SELECT 
companyName,
    companyNameAlternate,
    ProductCode,
    ProductNameEng,
   ProductNamePer,
    SUM(AvailableQty) as AvailableQty ,
    SUM(SalesPrice) as SalesPrice ,
   SUM( PurchasePrice ) as PurchasePrice,
    SUM(SalesQty) as Sales,
    SUM(SalesDiscount) as SalesDiscount,
    SUM(TotalSalesPrice) as TotalSalesPrice,
    SUM(TotalPurchasePrice) as TotalPurchasePrice
 FROM
(
 SELECT
    dbo.SupplierProductDetail.companyName as companyName,
    dbo.SupplierProductDetail.companyNameAlternate as companyNameAlternate,
    dbo.FDOSales.ProductCode as ProductCode,
    dbo.FDOSales.ProductNameEng as ProductNameEng,
    dbo.FDOSales.ProductNamePer as ProductNamePer,
    cast(null as int) as AvailableQty,
    dbo.FDOSales.SalesPrice as SalesPrice ,
    dbo.FDOSales.PurchasePrice as PurchasePrice ,
    SUM(dbo.FDOSales.SalesQtyinclDis) - SUM(dbo.FDOSales.SalesDiscount) AS SalesQty,
    SUM(dbo.FDOSales.SalesDiscount)   AS SalesDiscount,
    FDOSales.SalesPrice * (SUM(dbo.FDOSales.SalesQtyinclDis) - SUM(dbo.FDOSales.SalesDiscount)) AS TotalSalesPrice,
    FDOSales.PurchasePrice * (SUM(dbo.FDOSales.SalesQtyinclDis) - SUM(dbo.FDOSales.SalesDiscount)) AS TotalPurchasePrice
FROM
    dbo.FDOSales
LEFT OUTER JOIN
    dbo.SupplierProductDetail
ON
    (
        dbo.FDOSales.ProductCode = dbo.SupplierProductDetail.ProductCode)
LEFT OUTER JOIN
    dbo.FDOProvince
ON
    (
        dbo.FDOSales.ProvinceCode = dbo.FDOProvince.ProvinceCode)
       LEFT OUTER JOIN
   dbo.ProductAvailable
ON
    (
        dbo.FDOSales.ProductCode = dbo.ProductAvailable.ProductCode)
WHERE
     --SalesQtyinclDis > 0 AND 
    dbo.FDOSales.SaleDate >= '20151001'
AND dbo.FDOSales.SaleDate <= '20151031'
GROUP BY
    dbo.SupplierProductDetail.companyName,
    dbo.SupplierProductDetail.companyNameAlternate,
    dbo.FDOSales.ProductCode,
    dbo.FDOSales.ProductNameEng,
    dbo.FDOSales.ProductNamePer,
    dbo.FDOSales.SalesPrice,
    dbo.FDOSales.PurchasePrice
 UNION ALL
 SELECT
 cast(null as nvarchar) as companyName,
 cast(null as nvarchar) as companyNameAlternate,
    dbo.ProductAvailable.ProductCode as ProductCode,
    dbo.ProductAvailable.ProductNameEng as ProductNameEng,
    dbo.ProductAvailable.ProductNamePer as ProductNamePer,
    dbo.ProductAvailable.AvailableQty as AvailableQty,
     cast(null as int) as SalesPrice,
    cast(null as int) as PurchasePrice,
     cast(null as int) as SalesQty,
     cast(null as int) as SalesDiscount,
    cast(null as int) as TotalSalesPrice,
     cast(null as int) as TotalPurchasePrice    
FROM
    dbo.ProductAvailable 
) z
GROUP BY
companyName,
    companyNameAlternate,
    ProductCode,
    ProductNameEng,
   ProductNamePer

Open in new window

PortletPaul

why not simply join the extra table?

SELECT
      dbo.SupplierProductDetail.companyName AS companyName
    , dbo.SupplierProductDetail.companyNameAlternate AS companyNameAlternate
    , dbo.FDOSales.ProductCode AS ProductCode
    , pa.AvailableQty
    , dbo.FDOSales.ProductNameEng AS ProductNameEng
    , dbo.FDOSales.ProductNamePer AS ProductNamePer
    , dbo.FDOSales.SalesPrice AS SalesPrice
    , dbo.FDOSales.PurchasePrice AS PurchasePrice
    , SUM(dbo.FDOSales.SalesQtyinclDis) - SUM(dbo.FDOSales.SalesDiscount) AS SalesQty
    , SUM(dbo.FDOSales.SalesDiscount) AS SalesDiscount
    , FDOSales.SalesPrice * (SUM(dbo.FDOSales.SalesQtyinclDis) - SUM(dbo.FDOSales.SalesDiscount)) AS TotalSalesPrice
    , FDOSales.PurchasePrice * (SUM(dbo.FDOSales.SalesQtyinclDis) - SUM(dbo.FDOSales.SalesDiscount)) AS TotalPurchasePrice
FROM dbo.FDOSales
      INNER JOIN dbo.SupplierProductDetail ON dbo.FDOSales.ProductCode = dbo.SupplierProductDetail.ProductCode
      INNER JOIN dbo.FDOProvince ON dbo.FDOSales.ProvinceCode = dbo.FDOProvince.ProvinceCode
      INNER JOIN dbo.ProductAvailable AS pa ON dbo.FDOSales.ProductCode = pa.ProductCode
WHERE SalesQtyinclDis > 0
      AND dbo.FDOSales.SaleDate >= '20151001'
      AND dbo.FDOSales.SaleDate <= '20151031'
GROUP BY
      dbo.SupplierProductDetail.companyName
    , dbo.SupplierProductDetail.companyNameAlternate
    , dbo.FDOSales.ProductCode
    , pa.AvailableQty
    , dbo.FDOSales.ProductNameEng
    , dbo.FDOSales.ProductNamePer
    , dbo.FDOSales.SalesPrice
    , dbo.FDOSales.PurchasePrice
ORDER BY
      dbo.SupplierProductDetail.companyName
    , dbo.SupplierProductDetail.companyNameAlternate ASC
    , dbo.FDOSales.ProductNameEng
    , dbo.FDOSales.ProductNamePer                    ASC

Open in new window

When preparing questions like this we need to know about the DATA in the extra table, in particular: Is there only ONE ROW for each productcode? or, if there is MORE THAN ONE ROW which row should we use?

Jose Torres

Paul is correct on adding JOINS for the other tables.
Also when possible it is best to have SSRS call a stored procedure than to embed the SQL in SSRS.
This allows easier control of the code and allows the stored procedure to be cached.

Zolf

ASKER

Thanks for the feedbacks.

Paul,

I tried your query it is working to some extend but some of the availableqty is not showing in the result. for e.g. the available qty which have same values. wonder why the query is skipping these repeated values.Please see below the screenshot

Zolf

ASKER

I have also add sample data from those 4 tables
FDOProvince.xls
FDOSales1.xls
ProductAvailable.xls
SupplierProductDetail.xls

PortletPaul

>>"wonder why the query is skipping these repeated values"

Because the query is using GROUP BY so you will only get one row for each unique combination of these

GROUP BY
dbo.SupplierProductDetail.companyName
, dbo.SupplierProductDetail.companyNameAlternate
, dbo.FDOSales.ProductCode
, pa.AvailableQty
, dbo.FDOSales.ProductNameEng
, dbo.FDOSales.ProductNamePer
, dbo.FDOSales.SalesPrice
, dbo.FDOSales.PurchasePrice

PortletPaul

Thanks, while having data is nice, without an "expected result" we have no clear target to hit

Also: data samples should be small. Sorry but I don't have time to study 1.5Mb+ of data

Zolf

ASKER

Also: data samples should be small. Sorry but I don't have time to study 1.5Mb+ of data

sure,i understand,no worries!!

what do you suggest, these tables which i have is the result of refining the tables from my production db using SSIS. DO you think I need to break these tables further??. But I am sure I can get those result from these tables but dont know how to write the correct query

Zolf

ASKER

By the way can i have that availableqty outside the group by..any suggestion.the reason is when i remove the availableqty outside the group by then the result is very bad

Zolf

ASKER

Here i have filtered the data for one product which has productCode 1110185003.

Now I want to get the total sum of the product sales(from FDOSales table) for a given month with its availableQty(from table ProductAvailable).

Zolf

ASKER

NOw i started again going step by step. I did a simple JOIN of these 2 tables,like so,

SELECT
    dbo.FDOSales_.ProductNameEng,
    dbo.FDOSales_.SalesQtyinclDis,
    dbo.FDOSales_.SalesDiscount,
    dbo.ProductAvailable_.AvailableQty,
    dbo.FDOSales_.ProductCode
FROM
    dbo.FDOSales_
INNER JOIN
    dbo.ProductAvailable_
ON
    (
        dbo.FDOSales_.ProductCode = dbo.ProductAvailable_.ProductCode)
WHERE
    dbo.FDOSales_.ProductCode = '1110185003' ;

Open in new window

and the result i get is for every available qty i get these 2 cols repeated

PortletPaul

There is a a very good method for preparing a question that can be answered accurately. It is called "a Short, Self Contained, Correct (Compilable), Example"

In brief requires 2 things:

1. "sample data", and
2. the "expected result"

without the "expected result" providing sample data isn't helpful, or in the opposite, having the "expected result" without data isn't useful either.

At the moment we have some data, but no "expected result". So, I do not know how to help you further at the moment.

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zolf

ASKER

Got it at last,thanks so much,Appreciate your time and patience!!