[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Shop sales Actual vs Forecast

Posted on 2016-11-09
2
Medium Priority
?
73 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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.
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

649 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