February 28, 2017 at 8:22 pm
Hi everyone
I have a very basic knowledge of SQL so apologies if what I am asking is simple to do!
I need to calculate the sales generated for different stock codes during the time periods that they were put on sale.
In one table, the StockandPrice Table I have the Stockcode, date (daily sales data) and number of units sold by day. In a second table, the SalesPeriodTable I have Stockcode, Sales_Startdate and Sales_Enddate for each stock code. In this table there can be multiple periods where an item can go on sale and then return to normal prices and after a while be placed on sale again. I also have items that have recently gone on sale for which the sales_enddate has not been set so the field shows NULL.
So for each stock code I need to calculate the sales generated by day within the periods that they were placed on sale.
I tried the following:
Select * from StockandPrice y
inner join (select * from SalesPeriodTable) x on x.stockcode = y.stockcode and x.Sales_Startdate >= y.date and x.Sales_Enddate <=y.date
Which returned nothing!
Two issues are - multiple sales start and end dates for the same stockcode and null values in Sales_enddate.
I have attached an excel spreadsheet showing the two tables and the expected results.
Your assistance will be highly appreciated!!
March 1, 2017 at 2:06 am
maybe ?
SELECT sp.Stockcode,
sp.Date,
sp.Price,
sp.UnitSold,
sp.Price*sp.UnitSold as SalesAmount
FROM stockandprice AS sp
INNER JOIN salestable AS s ON sp.Stockcode = s.Stockcode
AND sp.Date >= s.Sales_StartDate
AND sp.Date <= ISNULL(s.Sales_EndDate,'01/01/2050') --- enter some future date that meets your need
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 1, 2017 at 2:08 am
Thank you! I will try this as soon as I can and revert.
Appreciate the help!
March 1, 2017 at 5:24 am
Results as I wanted them! Thank you very much!
March 1, 2017 at 10:30 am
It's a bad idea to use a NULL value to represent an unknown future date, because it forces you to use either a function (ISNULL or COALESCE) or an OR in your WHERE clause, both of which can severely affect performance of your query. You should use a far future date to represent unknown future dates. Typical values are 9999-12-30, 9999-01-01, or 9000-01-01. The common practice of using -1 (1899-12-31) to represent an unknown FUTURE date is almost as bad as using a NULL value.
You may not be able to change your process at this point, so this may be moot.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply