Shop sales Actual vs Forecast

Posted on 2016-11-09
Last Modified: 2016-11-11
I need to track shop sales actual vs forecast.
I have two tables :
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
Question by:Taras
LVL 34

Accepted Solution

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


Author Closing Comment

ID: 41884292
thank you a lot.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

856 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