LEFT JOIN match. If no match, need to match on most recent date

user_12177598 0
Viewed 687

My current SQL code:

SELECT 
    [Date], [Count]
FROM
    Calendar_Table pdv
LEFT JOIN
    (SELECT 
         COUNT([FILE NAME]) AS [Count], [CLOSE DT]
     FROM 
         Production_Table
     GROUP BY 
         [CLOSE DT]) [Group] ON [pdv].[Date] = [Group].[CLOSE DT]
ORDER BY 
    [Date]

Please see code below. Calendar_Table is a simple table, 1 row for every date. Production_Table gives products sold each day. If the left join produces a NULL, please produce the most recent non-NULL value.

Current output:

Date       | Count
-----------+--------
9/4/2019   | NULL
9/5/2019   | 1
9/6/2019   | 4
9/7/2019   | NULL
9/8/2019   | 7
9/9/2019   | 11
9/10/2019  | NULL
9/11/2019  | 14
9/12/2019  | NULL
9/13/2019  | 19

Desired output:

Date       | Count
-----------+--------
9/4/2019   | 0
9/5/2019   | 1
9/6/2019   | 4
9/7/2019   | 4
9/8/2019   | 7
9/9/2019   | 11
9/10/2019  | 11
9/11/2019  | 14
9/12/2019  | 14
9/13/2019  | 19
lateral-join gaps-and-islands datetime sql-server sql
1 Answers

One option is a lateral join:

select c.date, p.*
from calendar_table c
outer apply (
    select top (1) count(file_name) as cnt, close_dt
    from production_table p
    where p.close_dt <= c.date 
    group by p.close_dt
    order by p.close_dt desc
) p

As an alternative, we can use an equi-join to bring the matching dates, as in your original query, and then fill the gaps with window functions. The basic idea is to build groups that reset everytime a match is met.

select date, coalesce(max(cnt) over(partition by grp), 0) as cnt
from (
    select c.date, p.cnt,
        sum(case when p.close_dt is null then 0 else 1 end) over(order by c.dt) as grp
    from calendar_table c
    left join (
        select close_dt, count(file_name) as cnt
        from production_table p
        group by close_dt
    ) p on p.close_dt = c.date
) t

Depending on your data, one solution or the other may perform better.

user_106767160
Related