Solved

Shop sales Actual vs Forecast

Posted on 2016-11-09
2
33 Views
Last Modified: 2016-11-11
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
0
Comment
Question by:Taras
2 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 41880748
SELECT F.Shop_Number, F.Product_Group, F.Product_Name,
	F.Projected_To_Sale, SUM(A.Sold_Qty) AS Actual_Sale,
	CASE
		WHEN F.Projected_To_Sale <= SUM(A.Sold_Qty) THEN 'Yes'
		ELSE 'No'
	END AS In_Projected_D_Range
FROM tblShopSales_Forecast AS F
LEFT OUTER JOIN tblShopSales_Actual AS A
	ON F.Shop_Number = A.Shop_Number
	AND F.Product_Group = A.Product_Group
	AND F.Product_Name = A.Product_Name
	AND F.Sales_StartDate <= A.Sold_Date
	AND F.Sales_End_Date >= A.Sold_Date
GROUP BY F.Shop_Number, F.Product_Group, F.Product_Name, F.Projected_To_Sale

Open in new window

0
 

Author Closing Comment

by:Taras
ID: 41884292
thank you a lot.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now