Taras
asked on
Shop sales Actual vs Forecast
I need to track shop sales actual vs forecast.
I have two tables :
tbleShopSales_Forecast
tblShop_Sales_Actual
I need scrip that will give me third table tblShop_Sales_Utilization as example that I presented below.
Script has to do next : Qty in tblShop_Sales_Actual for particular shop for particular product group and particular product has to be summarized in particular date range that is defined in tblshopSales_Forecast and compared against qty in forecast table.
ble tlbShopSales_Forecast
Shop_Number Product_Group Product_Name Sales_Start_Date Sales_End_Date Projected_To_Sale
112233 House_Item table 01-01-2016 01-25-2016 2
112233 House_Item Chair 01-01-2016 01-25-2016 10
112233 House_Item Glass 01-01-2016 01-25-2016 30
112233 Garden_Item shovel 01-01-2016 01-25-2016 12
112233 Garden_Item Axes 01-01-2016 01-25-2016 5
223344 House_Item table 02-05-2016 03-15-2016 6
223344 Building_Item Switches 02-05-2016 03-15-2016 20
223344 Building_Item Folding Door 02-05-2016 03-15-2016 8
Table tblShop_Sales_Actual
Shop_Number Product_Group Product_Name Sold_Date Sold_Qty
112233 House_Item table 01-05-2016 2
112233 House_Item Chair 01-02-2016 2
112233 House_Item Chair 01-04-2016 3
112233 House_Item Chair 01-28-2016 5
112233 Garden_Item Shovels 01-08-2016 7
112233 Gardne_Item Shovels 01-20-2016 5
112233 Garden_Item Axes 01-05-2016 3
112233 Garden_Item Axes 01-08-2016 2
223344 House_Item table 02-10-2016 2
223344 House_Item table 02-13-2016 4
223344 Builing_Item Switches 02-06-2016 8
223344 Building_Item Folding Door 02-08-2016 4
223344 Building_Item Folding Door 03-05-2016 2
Table tblShop_Sales_Utilization
Shop_Number Product_Group Product_Name Projected_To_Sale Actual_Sale In_Projested_D_Range
112233 House_Item table 2 2 Yes
112233 House_item Chair 10 5 No
112233 House_Item Glass 30 0 No
112233 Garden_Item Shovel 12 12 Yes
112233 Garden_Item Axes 5 5 Yes
223344 House_Item table 6 6 Yes
223344 Building_Item Switches 20 8 No
223344 Building_Item Folding Door 8 6 No
I have two tables :
tbleShopSales_Forecast
tblShop_Sales_Actual
I need scrip that will give me third table tblShop_Sales_Utilization as example that I presented below.
Script has to do next : Qty in tblShop_Sales_Actual for particular shop for particular product group and particular product has to be summarized in particular date range that is defined in tblshopSales_Forecast and compared against qty in forecast table.
ble tlbShopSales_Forecast
Shop_Number Product_Group Product_Name Sales_Start_Date Sales_End_Date Projected_To_Sale
112233 House_Item table 01-01-2016 01-25-2016 2
112233 House_Item Chair 01-01-2016 01-25-2016 10
112233 House_Item Glass 01-01-2016 01-25-2016 30
112233 Garden_Item shovel 01-01-2016 01-25-2016 12
112233 Garden_Item Axes 01-01-2016 01-25-2016 5
223344 House_Item table 02-05-2016 03-15-2016 6
223344 Building_Item Switches 02-05-2016 03-15-2016 20
223344 Building_Item Folding Door 02-05-2016 03-15-2016 8
Table tblShop_Sales_Actual
Shop_Number Product_Group Product_Name Sold_Date Sold_Qty
112233 House_Item table 01-05-2016 2
112233 House_Item Chair 01-02-2016 2
112233 House_Item Chair 01-04-2016 3
112233 House_Item Chair 01-28-2016 5
112233 Garden_Item Shovels 01-08-2016 7
112233 Gardne_Item Shovels 01-20-2016 5
112233 Garden_Item Axes 01-05-2016 3
112233 Garden_Item Axes 01-08-2016 2
223344 House_Item table 02-10-2016 2
223344 House_Item table 02-13-2016 4
223344 Builing_Item Switches 02-06-2016 8
223344 Building_Item Folding Door 02-08-2016 4
223344 Building_Item Folding Door 03-05-2016 2
Table tblShop_Sales_Utilization
Shop_Number Product_Group Product_Name Projected_To_Sale Actual_Sale In_Projested_D_Range
112233 House_Item table 2 2 Yes
112233 House_item Chair 10 5 No
112233 House_Item Glass 30 0 No
112233 Garden_Item Shovel 12 12 Yes
112233 Garden_Item Axes 5 5 Yes
223344 House_Item table 6 6 Yes
223344 Building_Item Switches 20 8 No
223344 Building_Item Folding Door 8 6 No
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER