asked
Check value exist in previous month in sql
I have two table XYZ and ABC XYZ ID Value Dates 1 10 2017/02/01 2 10 2017/05/01 ABC ID Values Dates ExpectedResult 1 10 2017/01/01 20 1 20 2017/02/01 0 2 4 2017/01/01 0 2 5 2017/04/01 15 i want to check if table ABC having (Month - 1 ) value comparing with table XYZ. Uniqueness of table is identified by ID and Date . For eg-> Table XYZ id-1 is having date 2017/02/01 i.e February data and my ABC table ID-1 is having 2017/01/01 i.e January data then XYZ value + ABC value i.e 20 is the expected result. Could you please tell me how do we compare month in this scenario.
Because the data in each table is already grouped on the ID/Dates basis, you can simply join the tables on the off-by-one month basis. If you would like to have all records from ABC included in the results then you should use LEFT join with ABC as left and XYZ as right table. What is not clear though is why do you want the expected result to be 0 for rows in ABC which do not have the XYZ counterpart? For example, row # 2 in ABC with ID = 1 and Month = February does not have the March counterpart in XYZ. Should you have the ExpectedResult = 4 instead of 0? In other words, always use the sum of values from 2 tables and if there is no match from XYZ then just retain the value from ABC. Here is the script which is based on this assumption: select
a.ID, a.Values, a.Dates, a.Values + isnull(x.[Value], 0) ExpectedResult from ABC a left join XYZ x on
a.ID =
x.ID and a.Dates = dateadd(month, -1, x.Dates); Hope this helps. Oleg
18 People are following this question.
