SQL Deriving A Date Range When Only One Date is Known and Other Date Is In Another Row

Asked Dec 13 '19 at 19:35

Active Dec 13 '19 at 19:47

Viewed 38 times

I have a situation in which I have two tables (examples below):

As you can see, I do not have a Move Out Date. How could I create a view (SQL Server) that gives a range. For example, I will eventually need to use this view to see where each person lived when they made a purchase, but I am trying to work around the database design that would, ideally, have a MoveOutDate.

My desired results would be as follows:

From what I have read, I suspect I will have to use a CROSS APPLY here, but I have no idea how those work. I tried to do a join off of a subquery of the table, but situations where there are more than two addresses create a many to many relationship.

edited Dec 13 '19 at 19:47

asked Dec 13 '19 at 19:35

Tyler H

2

Check out ```LEAD/LAG``` window functions.. – alexherm Dec 13 '19 at 19:38
I have been trying to figure this out for a week, and I think that might work for me! Thanks! @alexherm – Tyler H Dec 13 '19 at 19:46
1

It will certainly work, just need to put some effort. Its good to have understanding of window functions in your back pocket. – alexherm Dec 13 '19 at 19:47

SQL Deriving A Date Range When Only One Date is Known and Other Date Is In Another Row

0 Answers0