sql server - Trouble trying to join two tables in SQL -

this first post on forum apologies if unclear or question not formatted correctly.

i have following table, table_months:

|   date    | year_part | month_part |  31-01-2016     2016           1  29-02-2016     2016           2  31-03-2016     2016           3  30-04-2016     2016           4     ...         ...           ... 

this table goes through whole year of 2016, first column last day of each month.

i have table, table_savings:

|   date    | year_part | month_part | cumulative_saved |  03-01-2016     2016           1              50  07-03-2016     2016           3             150  25-03-2016     2016           3             275   14-07-2016     2016           7             400     ...         ...           ... 

this table shows each date have put money savings bank account , amount put away.

what after join these 2 tables, such resulting table looks this:

|   date    | year_part | month_part | cumulative_saved |  31-01-2016     2016           1              50  29-02-2016     2016           2              50  31-03-2016     2016           3             275   30-04-2016     2016           4             275  31-05-2016     2016           5             275  30-06-2016     2016           6             275  31-07-2016     2016           7             400     ...         ...           ... 

essentially table which, instead of having exact dates of when deposited money savings account, gives me month-by-month recording of how in savings account.

my problem struggling figure out logic join of these 2 tables result like.

after many attempts @ trying work, 'best' have managed come is,

select     a.*,     max(b.cumulative_saved) on         (partition b.month_part order b.month_part asc)     table_months left join     table_savings b     on a.[date] <= b.[date] 

but join earlier dates table_months onto later dates table_savings end multiple dates multiple rows each date table_months.

i realise work if cumulative_saved field ever increases (which, in data, not) otherwise not pick row has data closest end of month date first table.

i struggling these , appreciate answer, can't see way work.

please let me know if of unclear , try clarify mean.

i have access netezza , ssms answer either best.

thank in advance answers.

this easier in sql server, using lateral join:

select tm.*, ts.cumulative_saved table_months tm outer apply      (select top 1 tm       table_saving ts       ts.date <= tm.date       order ds.date desc      ) ts; 

you can in netezza using lag() ignore nulls:

select tm.*,        coalesce(ts.cumulative_saved,                 lag(ts.cumulative_saved ignore nulls) on (order tm.date)                ) cumulative_saved table_months tm left join      (select ts.*,              row_number() on (partition date_trunc('month', ts.date) order ts.date desc) seqnum       table_saving ts      ) ts      on date_trunc('month', ts.date) = date_trunc('month', tm.date) ,         seqnum = 1;