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

Question

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

please share the table definitions and some sample data.. – Deepshikha Dec 22 '20 at 22:46 — Deepshikha, Dec 22 '20 at 22:46

GMB · Answer 1 · 2020-12-23T01:48:37.083

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.

I believe Itzik Ben-Gan showed a more efficient method that does not involve self-join: [The Last non NULL Puzzle](https://www.itprotoday.com/sql-server/last-non-null-puzzle) — Vladimir Baranov, Dec 23 '20 at 02:02
@VladimirBaranov: that's pretty much the method of the second query in the answer. Note that there is no self-join in either queries (we have two different tables). — GMB, Dec 23 '20 at 02:05

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

1 Answers1