In a query, if a row is missing data I want to select the data from the previous row

Question

I have two tables: Table1 has an account number and a description, and is static and contains all existing accounts.

Table2 has an account number, a period id, a beginning and an ending balance.

I have joined the tables on the account number. I will use account number 0000-1234-000 for my example. It has data for periods 1,2,3 and 5.

My query is simple:

SELECT t2.periodid, t1.acctnmbr, t1.description, t2.bgnblnc, t2.endblnc
FROM table1 T1 
LEFT JOIN Table2 T2 on t1.acctnmbr = t2.acctnmbr
WHERE t1.acctnmbr = 0000-1234-000

This returns the following:

1  0000-1234-000   Disbursement   256,786.00   165,784.00  
2  0000-1234-000   Disbursement   165,784.00   184,574.00  
3  0000-1234-000   Disbursement   184,574.00   376,144.00  
5  0000-1234-000   Disbursement   376,144.00   165,784.00

You can see period 4 is missing. No data was entered for this account in Table2 for period 4. I would have thought that with the left join I would at leas have returned the account number with "nulls" for values but that is a question for another day. What I want is to insert period 4 in my query with the results of period 3.

So the logic is "if not exists then previous period balance". How can I do this?

This is a classic island and gaps problem. You have to devise another query that contains all of your periods, while not participating in derived values, a scaffolding if you will. Generally, the "stream" query is the main table in the query and the left joins are the data that are useful. If Time permits, I will provide an example. — Ross Bush, Sep 26 '20 at 03:14
Also, it would be useful for others if you would edit your post and point out the database system you are targeting in regard to this question. — Ross Bush, Sep 26 '20 at 03:25
Left join it to your `periods` table where that `periodic` is the primary key. Or left join it to `generate_series(1,5) periods (period_id)` if `periods` table doesn't exist. If it's Postgres see this post [How do I efficiently select the previous non-null value?](https://stackoverflow.com/questions/18987791/how-do-i-efficiently-select-the-previous-non-null-value) — Dmitry, Sep 26 '20 at 04:00

score 0 · Answer 1 · answered Sep 26 '20 at 03:33

In standard SQL, you can construct the periods you want, use left join to generate the rows and then use lag(ignore nulls) to bring in the data you want. Not all databases support this standard functionality, but the query looks like:

with t as (
      SELECT t2.periodid, t1.acctnmbr, t1.description, t2.bgnblnc, t2.endblnc
      FROM table1 T1 LEFT JOIN
           Table2 T2 
           ON t1.acctnmbr = t2.acctnmbr
      WHERE t1.acctnmbr = '0000-1234-000'
     )
select v.periodid, '0000-1234-000' as acctnmbr,
       coalesce(description, lag(description ignore nulls) over (order by v.periodid) as description,
       coalesce(bgnblnc, lag(bgnblnc ignore nulls) over (order by v.periodid) as bgnblnc,
       coalesce(endblnc, lag(endblnc ignore nulls) over (order by v.periodid) as endblnc
from (values (1), (2), (3), (4), (5)) v(periodid) left join
      t
      on t.periodid = v.periodid

In a query, if a row is missing data I want to select the data from the previous row

1 Answers1