asked
Calculate value using previous and current month
Stock Table ID GlobalStock Date Country 1 10 2017/01/01 India 1 20 2017/01/01 India 2 5 2017/02/01 Africa 3 6 2017/08/01 Japan 4 7 2017/04/01 Japan 5 89 2017/08/01 Japan 2 10 2017/03/01 Japan 5 8 2017/03/01 Japan 1 20 2017/02/01 India ShipFile ID GlobalStock Date Country 2 10 2017/03/01 Africa 3 60 2017/08/01 India 11 70 2017/08/01 India 1 8 2017/02/01 India 1 9 2017/02/01 India 2 4 2017/03/01 Japan 2 5 2017/04/01 Japan 5 3 2017/03/01 Japan 3 8 2017/08/01 Japan SalesFiles ID GlobalStock Date Country 2 10 2017/03/01 India 2 20 2017/03/01 Africa 3 30 2017/08/01 Japan 7 5 2017/02/01 Japan 8 8 2018/01/01 Japan 1 9 2017/02/01 India 1 70 2017/02/01 Africa 13 10 2017/08/01 Japan 10 60 2017/11/01 Japan I want to calculate -> StockTable(Month - 1) + ShipFile (Month) - Sales (Month) For example For ID 1 suppose we are considering Jan (GlobalStock -> 10 + 20) data then in other tables we must take Feb values and country should be same for all tables. So calculation would be (10 + 20) + (8 + 9) - (9) = 38
@GayatriPanigrahi Are the date values in your table always the first of the month? If not then how do you want to match the records? One option would be to calculate the sums of stock, sales and shipped figures grouped by month prior to matching and then match. Is this the case? Please clarify. Also, I might be wrong, but this appears to be a homework question. If it is then please show what you have tried so far. Thank you.
Hi, For me difficult situation in this case is how can i calculate a value Logic -> (Month - 1) + Month - Month eg -> Jan + Feb - Feb OR Feb + March - March OR March + April - April Prob -> (Month -1) is could be jan, feb, march,......, Dec If my (month-1) is jan (Stock table) then other table(Ship and sales) data should be of Feb If my (month - 1) is feb then other files(ship and sales) data should be of March And so on.. And want to match the record by ID and country. I tried to achive above case using Dense_Rank and Group by But unable to do so...
Hi Did this clarification make sense ? If you need more clarification please me let me know.
@GayatriPanigrahi No, it did not make sense yet because you have not clarified the question about the dates. Here is this question again: Are the date values in your table always the first of the month? Please answer it because it will help with solution. In case if the real data may have the dates for any day of the month, please clarify how to handle it.
Hi, As of now my stock table consist of first of the month. But it would be great if we select transaction value of the month end. We need to select last day OR value remained end of the month from stock table, and yes for other two table we should consider year and month for calculation.
Hi Yes, you are very correct now My intention of using SUM is, there are multiple duplicate record for eg. ID GlobalStock Date Country 1 10 2017/02/01 India 1 20 2017/01/30 India 1 30 2017/01/30 India 1 10 2017/01/30 Japan For id-1 January end of the month globalstock value is 30 for country India And yes, last day of month is not Zero, it must having values.
@GayatriPanigrahi I cannot believe that the simple question about whether the stock table has records for the last day of the month still remains unanswered. From the sample in your post above, the 2nd and 3rd records are for Jan 30 2017, both records are for the same ID = 1 and country = India. This means that the global stock for this date is 50, not 30 (30 + 20 = 50). This date is not the last day of January though, January 31st is, but there are no records for that date. I will post the solution to the main question, based on the assumption that the records for the last day of the month do exist and these are the records which determine the final end-of month stock numbers.
Hi Its not about the last day of the month it could be any date OR you can simply say latest date of the month. For eg -> Jan month having transaction on 1,2,3,10,25, 26 Then i must consider 26th Jan value for my calculation. It is not necessary it must be of 31st of Jan or 28th of Feb or 31st of March. And yes above above calculation was typo, you are correct ID-1 india having Globalstock value as 50
@GayatriPanigrahi Well, it is too late for that clarification, I already posted a solution which is based on the assumption that the records for last day of the month do exist. If they don't then the top part of the query is going to have to be modified to accommodate. It is a very simple change though, let me know if you want me to adjust my answer.
I will assume that the stock table does forsooth have the records for the last day of any respective month. As discussed, the stock data is usually semi-additive in its nature, so the "last non-empty" rule is used to figure the stock values. If there are records for the last day of the month then those may be used to figure end-of-the-month numbers. It is possible that there are multiple records for the same ID, Date, Country combination. This means that the stock data must be filtered and summarized (using group by clause) before joining to other tables (or using [apply][1] as in the solution below). The logic of the solution is as follows: the records from StockTable are filtered to include only those records which have the last day of the month as their Date value. This data is grouped and selected along with the "start of next month" values which are simply the date value plus one day, which always makes it a beginning of next month. For example, if the last day of the month is Feb 28 2017 then the start of next month is Mar 1 2017. Once the stock data is selected, the apply is used to calculate the shipping and sales quantities matching by ID, Country and the whole next month range. Here is the script which should produce desired results based on the assumption that the stock table has records for the last day of the month: select stock.*, isnull(ship.ShippedQty, 0) ShippedQty, isnull(sales.SalesQty, 0) SalesQty, stock.GlobalStock + isnull(ship.ShippedQty, 0) - isnull(sales.SalesQty, 0) Balance from ( select ID, [Date], Country, dateadd(day, 1, [Date]) StartOfNextMonth, sum(GlobalStock) GlobalStock from StockTable -- select only those records which have the last day of respective month as the date value where [Date] = dateadd(day, -1, dateadd(month, datediff(month, 0, [Date]) + 1, 0)) group by ID, [Date], Country ) stock cross apply ( select sum(GlobalStock) ShippedQty from ShipFile where -- just add up all matching records which fall within any day of the next month ID =
stock.ID and Country = stock.Country and [Date] >= stock.StartOfNextMonth and [Date] < dateadd(month, 1, stock.StartOfNextMonth) ) ship cross apply ( select sum(GlobalStock) SalesQty from SalesFiles where -- same logic, add up all matching records which fall within any day of the next month ID =
stock.ID and Country = stock.Country and [Date] >= stock.StartOfNextMonth and [Date] < dateadd(month, 1, stock.StartOfNextMonth) ) sales; ***Edit to accommodate the clarified requirements*** If the stock data is not necessarily available for the last day of every month then the last available date per month should be used for every ID/Country combination. These last dates might or might not be different for different products. This means that the original query in this answer needs to be modified slightly (just the sub-select aliased as stock needs to be modified, nothing else). Please note that I opted to use the correlated subquery in order to derive the latest date for every month for every ID/Country combination. This method might work just fine but might cause some performance issues. If it does then let me know, there are always multiple ways to get the same result. Here is the updated query which should return desired results: select stock.*, isnull(ship.ShippedQty, 0) ShippedQty, isnull(sales.SalesQty, 0) SalesQty, stock.GlobalStock + isnull(ship.ShippedQty, 0) - isnull(sales.SalesQty, 0) Balance from ( select ID, [Date], Country, dateadd(month, datediff(month, 0, t.[Date]) + 1, 0) StartOfNextMonth, sum(GlobalStock) GlobalStock from StockTable t -- Include records only for the latest available transaction date of the month for -- every ID/Country/Month combinations (thus the check to the end of current month) where [Date] = ( select max([Date]) from StockTable where ID =
t.ID and Country = t.Country -- last transaction date of the month (less than start of next month) and [Date] < dateadd(month, datediff(month, 0, t.[Date]) + 1, 0) ) group by ID, [Date], Country ) stock cross apply ( select sum(GlobalStock) ShippedQty from ShipFile where -- just add up all matching records which fall within any day of the next month ID =
stock.ID and Country = stock.Country and [Date] >= stock.StartOfNextMonth and [Date] < dateadd(month, 1, stock.StartOfNextMonth) ) ship cross apply ( select sum(GlobalStock) SalesQty from SalesFiles where -- same logic, add up all matching records which fall within any day of the next month ID =
stock.ID and Country = stock.Country and [Date] >= stock.StartOfNextMonth and [Date] < dateadd(month, 1, stock.StartOfNextMonth) ) sales; Hope this helps. Oleg [1]:
https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx
18 People are following this question.
