Lagging variables

Database

Lagging variables

Hi,

I have the following data;

Account_id   Date           Flag_1

123             012006        ?

123             022006        1

234             012007        1

234             022007        1

I would like to lag flag 1 to see whether it was null or 1 in the previous time period. But I have to do it by account. I ran the following but it doesn't work. Cannot figure out what's wrong with this query.

select account_id,
         date,
          flag_1,
          max(flag_1) over (partition by account_id rows between
        1 preceding and 1 preceding) as flag_2

from data

order by account_id, date;

I appreciate the assistance.

Tags (3)
2 REPLIES
Junior Contributor

Re: Lagging variables

You need an ORDER BY:

select account_id,
date,
flag_1,
max(flag_1)
over (partition by account_id
order by date
rows between 1 preceding and 1 preceding) as flag_2
from data
order by account_id, date;

Re: Lagging variables

Works like a charm! Thank you!