# question

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

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
@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.
0 Likes 0 ·
·
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...
0 Likes 0 ·
·
Hi Did this clarification make sense ? If you need more clarification please me let me know.
0 Likes 0 ·
·
@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.
0 Likes 0 ·
·
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.
0 Likes 0 ·

·
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.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
@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.
0 Likes 0 ·
·
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
0 Likes 0 ·
·
@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.
0 Likes 0 ·
·
@GayatriPanigrahi I updated my answer to accommodate the scenario you describe. Please use the second query, it should return desired results and let me know if it works as expected. Thank you.
0 Likes 0 ·