Solved

Shop sales Actual vs Forecast

Posted on 2016-11-09
2
25 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
Comment Utility
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
Comment Utility
thank you a lot.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

744 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