asked on

Table Design help

Hello there,

I am using SSIS and SSRS to generate reports from my production database. Now I am trying to impove the ETL process and need some help on the design. I have some products sold in different states in a country by the branch offices in that state. Some branch offices sell to more then one state. Now I am trying to group them by Branch then its State and the data which I show is the available qty in that branch and the sales in each states it does.

Now I have 3 tables called

FDOSale - This contains the product sales in each State,

ProductAvailable - This contains the available stock In each Branch and

FDOProvince - This contains the states code

The product code in each of these 2 tables FDOSale and ProductAvailable table is the primaryKey that I link them together.

How can I create a relation so that I can achieve this grouping - Branch and its State with the related data. Do I need to create a new column in the FDOProvince with the BranchNames or ??. At present I am not able to group that.

At present I am trying something like this

SELECT
    dbo.SupplierProductDetail.companyName,
    dbo.SupplierProductDetail.description,
    dbo.ProductAvailable.BranchNameEng,
    dbo.FDOSales.ProvinceEng,
    SUM(dbo.FDOSales.SalesQtyinclDis)      AS SalesQtyInclDis,
    SUM(dbo.FDOSales.SalesDiscount)        AS SalesDiscountQty,
    SUM(dbo.ProductAvailable.AvailableQty) AS AvQty
FROM
    dbo.SupplierProductDetail
LEFT OUTER JOIN
    dbo.ProductAvailable
ON
    (
        dbo.SupplierProductDetail.ProductCode = dbo.ProductAvailable.ProductCode)
LEFT OUTER JOIN
    dbo.FDOSales
ON
    (
        dbo.SupplierProductDetail.ProductCode = dbo.FDOSales.ProductCode)
WHERE
    dbo.FDOSales.SaleDate >= '20151001'
AND dbo.FDOSales.SaleDate <= '20151031'
GROUP BY
    dbo.SupplierProductDetail.companyName,
    dbo.SupplierProductDetail.description,
    dbo.ProductAvailable.BranchNameEng,
    dbo.FDOSales.ProvinceEng
ORDER BY
    dbo.SupplierProductDetail.companyName ASC,
    dbo.ProductAvailable.BranchNameEng ASC,
    dbo.SupplierProductDetail.description ASC ;

Open in new window

cheers
Zolf

Brian Crowe

It seems to me that there should be a branch/store reference in FDOSales. Is it there and I'm just not seeing it?

Miguel Oz

You are missing a left join to FDOProvince with this condition (dbo.FDOSales.ProvinceCode = dbo.FDOProvince.ProvinceCode) as shown below:

SELECT
    dbo.SupplierProductDetail.companyName,
    dbo.SupplierProductDetail.description,
    dbo.ProductAvailable.BranchNameEng,
    dbo.FDOProvince.ProvinceEng,
    SUM(dbo.FDOSales.SalesQtyinclDis)      AS SalesQtyInclDis,
    SUM(dbo.FDOSales.SalesDiscount)        AS SalesDiscountQty,
    SUM(dbo.ProductAvailable.AvailableQty) AS AvQty
FROM
    dbo.SupplierProductDetail
LEFT OUTER JOIN
    dbo.ProductAvailable
ON
    (dbo.SupplierProductDetail.ProductCode = dbo.ProductAvailable.ProductCode)
LEFT OUTER JOIN
    dbo.FDOSales
ON
    (dbo.ProductAvailable.ProductCode = dbo.FDOSales.ProductCode)
LEFT OUTER JOIN
    dbo.FDOProvince
ON
    (dbo.FDOSales.ProvinceCode = dbo.FDOProvince.ProvinceCode)
WHERE
    dbo.FDOSales.SaleDate >= '20151001'
AND dbo.FDOSales.SaleDate <= '20151031'
GROUP BY
    dbo.SupplierProductDetail.companyName,
    dbo.SupplierProductDetail.description,
    dbo.ProductAvailable.BranchNameEng,
    dbo.FDOProvince.ProvinceEng
ORDER BY
    dbo.SupplierProductDetail.companyName ASC,
    dbo.ProductAvailable.BranchNameEng ASC,
    dbo.SupplierProductDetail.description ASC ;

Open in new window

Zolf

ASKER

Thanks for all the feedbacks.

It seems to me that there should be a branch/store reference in FDOSales. Is it there and I'm just not seeing it?

No I dont have a reference in that table but I have that reference in the ProductAvailable table called BranchCode.

Do I add a new column in the FDOProvince called BranchAssociated and associate those States to the Branch. Is this a good design??

Miguel Oz
Your query did not give the correct result. I am more after the table design

Zolf

ASKER

I added some additional col the the ProvinceCode table to add association between the (Province)State and its Branch Offices. But when I run the query It is getting the branch of the FDOSales not the ProductAvailable.

ASKER CERTIFIED SOLUTION

Brian Crowe

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

cheers