Write SQL Query to fill the missing months with previous months salary

Write SQL Query to fill the missing months with previous month’s salary?

Video

To achieve the desired output, we can use a combination of WITH clause, Connect By and LEAD function to fill the missing months.

Here how we do it.

Example data

-- ORACLE

WITH data AS (
select '1' ename,'Jan-19' month,5000 salary from dual union all
select '1' ename,'Aug-19' month,10000 salary from dual union all
select '1' ename,'Dec-19' month,15000 salary from dual
),
next_month as (
select ename,month,salary,to_date(month,'MON-YY') as D,
lead(to_date(month,'MON-YY'),1) over(partition by ename order by to_date(month,'Mon-YY')) next_inc_month
from data
),
replicate as (
select ename,month,salary,D,next_inc_month,months_between(next_inc_month,d) tt,
nvl(months_between(next_inc_month,d),1) tt1
from next_month
)
select ename,to_char(add_months(d,r),'MON-YY') month ,salary from replicate,
lateral (select rownum-1 r from dual connect by level<=tt1);

--------------------------Result

-- POSTGRESQL
WITH data AS (
    SELECT '1' AS ename, 'May-19' AS month, 5000 AS salary
    UNION ALL
    SELECT '1' AS ename, 'Aug-19' AS month, 10000 AS salary
    UNION ALL
    SELECT '1' AS ename, 'Dec-19' AS month, 15000 AS salary
),
next_month AS (
    SELECT ename, month, salary,
           to_date(month, 'Mon-YY') AS D,
           lead(to_date(month, 'Mon-YY'), 1) OVER (PARTITION BY ename ORDER BY to_date(month, 'Mon-YY')) AS next_inc_month
    FROM data
),
replicate AS (
    SELECT ename, month, salary, D, next_inc_month,
           EXTRACT(MONTH FROM age(next_inc_month, D)) AS tt,
           COALESCE(EXTRACT(MONTH FROM age(next_inc_month, D)), 1) AS tt1
    FROM next_month
)
SELECT ename, to_char(D + (r * interval '1 month'), 'Mon-YY') AS month, salary
FROM replicate,
LATERAL generate_series(0, tt1 - 1) AS gs(r);

--------------------------Result

-- SQL SERVER

WITH data AS (
    SELECT '1' AS ename, 'May-19' AS month, 5000 AS salary
    UNION ALL
    SELECT '1' AS ename, 'Aug-19' AS month, 10000 AS salary
    UNION ALL
    SELECT '1' AS ename, 'Dec-19' AS month, 15000 AS salary
),
next_month AS (
    SELECT ename, month, salary,
           CAST(CONVERT(DATE, '01-' + month, 106) AS DATE) AS D,
           LEAD(CAST(CONVERT(DATE, '01-' + month, 106) AS DATE), 1) OVER (PARTITION BY ename ORDER BY CAST(CONVERT(DATE, '01-' + month, 106) AS DATE)) AS next_inc_month
    FROM data
),
replicate AS (
    SELECT ename, month, salary, D, next_inc_month,
           DATEDIFF(MONTH, D, next_inc_month) AS tt,
           ISNULL(DATEDIFF(MONTH, D, next_inc_month), 1) AS tt1
    FROM next_month
)
SELECT ename, FORMAT(DATEADD(MONTH, r, D), 'MMM-yy') AS month, salary
FROM replicate
CROSS APPLY (SELECT TOP (tt1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS r FROM master..spt_values) AS x;

--------------------------Result