How can I pull previous month data-CodePudding

We have a data warehouse where we have been storing data for the past 14 years. I am using SQL server 2016 version. It is personal information data. This is basically the data regarding the deposits that will contains columns like for example: MemberID, membershipDate, MonthlyDepositdate, DepositAmount.

Until 5 years back the member info was updated yearly into the DW but from the last 5 years the data is pulled monthly and appended to dw regularly.

Now, my problem is that some member who joined before last 5 years back has the monthly deposit date with yearly updated date for example: 2011-01-01; 2012-12-31, 2013-12-31 etc... but from last 5 years we storing data every month so the monthly deposit date will be something like below: 2017-01-31; 2017-02-28; 2017-03-31 etc... and the corresponding deposit amount.

So now I have a requirement to pull the data for the previous month deposit amount along with the current month deposit amount. For this requirement I can simply do it by using EOMONTH(DATEADD,-1, Monthly Deposit Date) but as I said until the past 5 years the data we stored is yearly based. How to achieve this situation please, your help is very much appreciated. Please Note:- you might have observed the data until 2016 is appended every year and from 2017 onwards the data is saved by month.

My original dw table:

MemberID, membershipDate, MonthlyDepositdate, DepositAmount. 
111       2003-02-15      2003-12-31          53.00
111       2003-02-15      2004-12-31          101.00
111       2003-02-15      2005-12-31          162.00
...        ....             ...                ...
111       2003-02-15      2017-01-31          1650.00
111       2003-02-15      2017-02-28          1660.00
111       2003-02-15      2017-03-31          1672.00
222       2014-05-19      2014-12-31          30.00
222       2014-05-19      2015-12-31          72.00
222       2014-05-19      2016-12-31          113.00
222       2014-05-19      2017-01-31          115.00
222       2014-05-19      2017-02-28          120.00
222       2014-05-19      2017-03-31          123.00

I need to produce the result something like below:

MemberID, membershipDate, MonthlyDepositdate, DepositAmount, PreviousMonthDepositDate, PreviousmonthDepositAmt
111       2003-02-15      2003-12-31          53.00           ___                       0.0
111       2003-02-15      2004-12-31          101.00          2003-12-31                53.00
111       2003-02-15      2005-12-31          162.00          2004-12-31                101.00
...        ....             ...                ...             ....                      ...
111       2003-02-15      2017-01-31          1650.00         2016-12-31                1600.00
111       2003-02-15      2017-02-28          1660.00         2017-01-31                1650.00
111       2003-02-15      2017-03-31          1672.00         2017-02-28                1660.00
222       2014-05-19      2014-12-31          30.00           ___                       0.0
222       2014-05-19      2015-12-31          72.00           2014-12-31                30.00
222       2014-05-19      2016-12-31          113.00          2015-12-31                72.00
222       2014-05-19      2017-01-31          115.00          2016-12-31                113.00
222       2014-05-19      2017-02-28          120.00          2017-01-31                115.00
222       2014-05-19      2017-03-31          123.00          2017-02-28                120.00

CodePudding user response：

Going off your sample data something like this should work.

declare @Something table
(
    MemberID int
    , membershipDate date
    , MonthlyDepositdate date
    , DepositAmount decimal(7,2)
)

insert @Something
select 111, '2003-02-15', '2003-12-31', 53.00 union all
select 111, '2003-02-15', '2004-12-31', 101.00 union all
select 111, '2003-02-15', '2005-12-31', 162.00 union all
select 111, '2003-02-15', '2017-01-31', 1650.00 union all
select 111, '2003-02-15', '2017-02-28', 1660.00 union all
select 111, '2003-02-15', '2017-03-31', 1672.00 union all
select 222, '2014-05-19', '2014-12-31', 30.00 union all
select 222, '2014-05-19', '2015-12-31', 72.00 union all
select 222, '2014-05-19', '2016-12-31', 113.00 union all
select 222, '2014-05-19', '2017-01-31', 115.00 union all
select 222, '2014-05-19', '2017-02-28', 120.00 union all
select 222, '2014-05-19', '2017-03-31', 123.00

select MemberID
    , membershipDate
    , MonthlyDepositdate
    , DepositAmount
    , PreviousMonthDepositDate = lag(MonthlyDepositDate, 1) over(partition by MemberID order by MonthlyDepositDate)
    , PreviousmonthDepositAmt = lag(DepositAmount, 1) over(partition by MemberID order by MonthlyDepositDate)
from @Something