How to query the previous record that is in another table?

Question

I have a view that shows something like the following:

View VW

| ID |     DT     | VAL|
|----|------------|----|
|  1 | 2016-09-01 |  7 |
|  2 | 2016-08-01 |  5 |
|  3 | 2016-07-01 |  8 |

I have a table with historical date that has something like:

Table HIST

| ID |     DT     | VAL|
|----|------------|----|
|  1 | 2016-06-27 |  4 |
|  1 | 2016-06-29 |  3 |
|  1 | 2016-07-15 |  0 |
|  1 | 2016-09-12 |  8 |
|  2 | 2016-05-05 |  3 |

What I need is to add another column to my view with a boolean that means "the immediately previous record exist in history and has a related value greater than zero".

The expected output is the following:

| ID |     DT     | VAL| FLAG |
|----|------------|----|------|
|  1 | 2016-09-01 |  7 | false| -- previous is '2016-07-15' and value is zero. '2016-09-12' in hist is greater than '2016-09-01' in view, so it is not the previous
|  2 | 2016-08-01 |  5 | true | -- previous is '2016-05-05' and value is 3
|  3 | 2016-07-01 |  8 | false| -- there is no previous value in HIST table

What have I tried

I've used the query below. It works for small loads, but fails in performance in production because my view is extremely complex and the historical table is too large. Is it possible to query this without using the view multiple times? (if so, the performance should be better and I won't see anymore timeouts)

You can test here http://rextester.com/l/sql_server_online_compiler

create table vw (id int, dt date, val int);
insert into vw values (1, '2016-09-01', 7), (2, '2016-08-01', 5), (3, '2016-07-01', 8);

create table hist (id int, dt date, val int);
insert into hist values (1, '2016-06-27', 4), (1, '2016-06-29', 3), (1, '2016-07-15', 0), (1, '2016-09-12', 8), (2, '2016-05-05', 3);

select vw.id, vw.dt, vw.val, (case when hist_with_flag.flag = 'true' then 'true' else 'false' end)
from vw
left join 
(
  select hist.id, (case when hist.val > 0 then 'true' else 'false' end) flag 
  from
  (
    select hist.id, max(hist.dt) as dt
    from hist
    inner join vw on vw.id = hist.id
    where hist.dt < vw.dt
    group by hist.id
  ) hist_with_max_dt
  inner join hist 
    on hist.id = hist_with_max_dt.id and hist.dt = hist_with_max_dt.dt
) hist_with_flag
on vw.id = hist_with_flag.id

Answer 1

You can use OUTER APPLY in order to get the immediately previous record:

SELECT v.ID, v.DT, v.VAL,  
       IIF(t.VAL IS NULL OR t.VAL = 0, 'false', 'true') AS FLAG
FROM Vw AS v
OUTER APPLY (
   SELECT TOP 1 VAL, DT
   FROM Hist AS h 
   WHERE v.ID = h.ID AND v.DT > h.DT
   ORDER BY h.DT DESC) AS t