Creating a column that'll show previous rates

Question

I'm creating a column that will show an old address. Whenever an address is updated the old address is expired the day before the new one is supposed to take effect and a new row with the same account number will be added with the new address (ex: old address will show: From: 10/01/2020 To: 12/31/2021 and the new address From: 01/01/2022 To: 12-31-9999). I'm trying to show old and new address side by side for each account number in SQL, but I'm getting zeros.

Sample Data

AccountNumber	Address	ValidFrom	ValidTo
4895626	720 Main	10/01/2020	12/31/2021
9794651	158 4th St	09/29/2002	12/19/2020
4895626	5986 9th Ave	01/01/2022	12/31/9999

Desired Results

AccountNumber	Address	ValidFrom	ValidTo	PreviousAddress
4895626	720 Main	10/01/2020	12/31/2021	0
9794651	158 4th St	09/29/2002	12/19/2020	0
4895626	5986 9th Ave	01/01/2022	12/31/9999	720 Main

DECLARE @PreviousValidTo DATE;
SELECT @PreviousValidTo = DATEADD(DAY, -1, Z.ValidFrom)
FROM Address.NewAddress Z
SELECT Address, ValidFrom, ValidTo
CASE
            WHEN @PreviousValidTo = C.ValidFrom
            THEN C.Address
            ELSE 0
            END AS PreviousAddress
FROM Address.NewAddress C

Your first *select* will assign essentially a random value to `PreviousValidTo`, probably that's not your intention? Your second query is not valid, where is `C`? — Stu, Jan 12 '22 at 20:31
Can you add the table definition, sample data and expected results. — Stu, Jan 12 '22 at 20:44
Sorry about that, first timer. I've added some sample data and what I am trying to produce. — supeercod, Jan 12 '22 at 20:58
Does this answer your question? [Is there a way to access the "previous row" value in a SELECT statement?](https://stackoverflow.com/questions/710212/is-there-a-way-to-access-the-previous-row-value-in-a-select-statement) — Charlieface, Jan 13 '22 at 00:09

score 2 · Accepted Answer · answered Jan 12 '22 at 21:07

2

Possibly you could use lag

select *,
  Lag(address,1,'0') over(partition by accountnumber order by validto) PreviousAddress
from address.NewAddress

answered Jan 12 '22 at 21:07

Stu

30,392
6
14
33

1

woooooow, I had tried LAG but didn't get anywhere. I've been stuck on this for far too long. Thank you! – supeercod Jan 12 '22 at 21:14
quick question, I have multiple tables in this query and the LAG function seems to be referring to the table as a whole rather than the ADDRESS table. What could be causing the issue? – supeercod Jan 13 '22 at 21:17

Creating a column that'll show previous rates

1 Answers1