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

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

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

1
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

0
PortletPaulfreelancerCommented:
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?
0
Jose TorresSenior SQL Server DBACommented:
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.
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

zolfAuthor Commented:
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

1
0
zolfAuthor Commented:
I have also add sample data from those 4 tables
FDOProvince.xls
FDOSales1.xls
ProductAvailable.xls
SupplierProductDetail.xls
0
PortletPaulfreelancerCommented:
>>"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
1
PortletPaulfreelancerCommented:
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
0
zolfAuthor Commented:
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
0
zolfAuthor Commented:
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
0
zolfAuthor Commented:
1
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).
0
zolfAuthor Commented:
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

1
0
PortletPaulfreelancerCommented:
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.
0
Kevin CrossChief Technology OfficerCommented:
zolf,

It will take me a while to catch up on all the comments, but on first read of question - my gut tells me that the table you are adding should be a LEFT JOIN to an aggregated query.  Based on the columns, it seems like this table fills up every time you have a lot (or batch) of new product available; therefore, I would guess there could be zero or many rows where AvailableQty > 0.

Hence, my first stab at this would be the following.

dbo.SupplierProductDetail p
LEFT JOIN (
    SELECT ProductCode
         , AvailableQty = SUM(AvailableQty)
        /* other columns as necessary */
    FROM dbo.ProductAvailable
    /* optionally add WHERE clause. */
    GROUP BY ProductCode
    /* optionally filter out rows without inventory on-hand. */
    --HAVING SUM(AvailableQty) > 0
) a ON a.ProductCode = p.ProductCode


Following this pattern, I would do the same with sales, summing it inside a derived table then JOINing to your product detail table -- this should give you one row for each product with correct totals for the other tables.  If you try to JOIN and do the summation in the outer query, you may get incorrect totals because of the duplication of matched rows.
2

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:
Got it at last,thanks so much,Appreciate your time and patience!!
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
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.