SQL View displaying current status of a "unit" for every date that something changes with the "unit"

Question

In a project I'm working on, there is a table of units and then there are several tables that pertain to the units' locations at a given point in time as well as other factors for a given point in time.

For example, Unit1 might be at LocationA on 1/1/2009 and have a second location entry that has Unit1 at LocationB on 3/1/2010. Likewise, Unit1 might be Size1 on 2/20/2009 and Size2 on 1/12/2010.

Table Structures

Table: Unit  
int ID (identity, PK)  
varchar Name  

Table: Unit_Location  
int OrgID (FK Unit.ID)  
datetime eff_date  
int LocationID (FK Location.ID)  

Table: Unit_Size  
int OrgID (FK Unit.ID)  
datetime eff_date  
int UnitSizeID (FK Size.ID) 

I want to have a view that takes all of the changes from the various property change tables and has row for every date that the unit is different and have the current values for all the properties in that row. So for the example listed above, the view would like:

1/1/2009  - Unit1 - LocationA - Null  
2/20/2009 - Unit1 - LocationA - Size1  
1/12/2010 - Unit1 - LocationA - Size2  
3/1/2010  - Unit1 - LocationB - Size2  

If I do a full outer join based on the effective dates, I believe I can easily get to:

1/1/2009  - Unit1 - LocationA - Null  
2/20/2009 - Unit1 - Null      - Size1  
1/12/2010 - Unit1 - Null      - Size2  
3/1/2010  - Unit1 - LocationB - Null  

But that doesn't quite get me to what I want. What should my SQL look like to get a view with the full status of every Unit for every date that something with the unit changes? enter code here

Answer 1

give this a try:

--I added more test data :-)
DECLARE @Unit table (ID int identity(1,1) primary key  ,Name  varchar(10))
INSERT INTO @Unit (Name) VALUES ('Unit 1')
INSERT INTO @Unit (Name) VALUES ('Unit 2')

DECLARE @Unit_Location table (OrgID int,eff_date datetime,Location varchar(10))
INSERT INTO @Unit_Location VALUES (1,'01/01/2009','LocationA')
INSERT INTO @Unit_Location VALUES (1,'03/01/2010','LocationB')
INSERT INTO @Unit_Location VALUES (2,'03/03/2010','Locationz')

DECLARE @Unit_Size table (OrgID int, eff_date datetime, UnitSize varchar(10))
INSERT INTO @Unit_Size VALUES (1,'02/20/2009','Size1')
INSERT INTO @Unit_Size VALUES (1,'01/12/2010','Size2')
INSERT INTO @Unit_Size VALUES (2,'01/02/2010','Size9')


;WITH AllDates AS
(
    SELECT OrgID,eff_date FROM @Unit_Location
    UNION 
    SELECT OrgID,eff_date FROM @Unit_Size

), AllPKs AS
(
SELECT 
    a.OrgID,a.eff_date
    ,(SELECT MAX(l.eff_date) FROM @Unit_Location l WHERE l.eff_date<=a.eff_date AND a.OrgID=l.OrgID) AS LocationPK
    ,(SELECT MAX(s.eff_date) FROM @Unit_Size     s WHERE s.eff_date<=a.eff_date AND a.OrgID=s.OrgID) AS SizePK
    FROM AllDates a
)
SELECT
    a.eff_date, u.Name, l.Location, s.UnitSize
    FROM AllPKs                        a
        LEFT OUTER JOIN @Unit          u ON a.OrgID=u.ID
        LEFT OUTER JOIN @Unit_Location l ON a.LocationPK=l.eff_date AND a.OrgID=l.OrgID
        LEFT OUTER JOIN @Unit_Size     s ON a.SizePK=s.eff_date AND a.OrgID=s.OrgID
    ORDER BY u.Name,a.eff_date

OUTPUT:

eff_date                Name       Location   UnitSize
----------------------- ---------- ---------- ----------
2009-01-01 00:00:00.000 Unit 1     LocationA  NULL
2009-02-20 00:00:00.000 Unit 1     LocationA  Size1
2010-01-12 00:00:00.000 Unit 1     LocationA  Size2
2010-03-01 00:00:00.000 Unit 1     LocationB  Size2
2010-01-02 00:00:00.000 Unit 2     NULL       Size9
2010-03-03 00:00:00.000 Unit 2     Locationz  Size9

(6 row(s) affected)