SQL. How to extend a table using date?

I use SQL 2008.

I have a logical entity (table) say Data with attributes (columns) (key, date, a,b,c,d).

the problem is a and b change values every day and that c and d change values at random dates and very slow. And also they change for each key individually. Say c,d for key = 1 will change today and next month and for key=2 tomorrow and a week later.

So my idea is to split on 2. MainData (key,date,a,b) and TemporalData (key, changeDate, c,d)

However I failed to write an efficient query - how to consume this.

I was thinking of somewhat a 'parametrized' view (not sure if it is possible at all). Say select * from vwData where date = 'xxxxx' and it will return me a,b for that particular day and also c,d which were effective for a particular key and that date.

I need a simple and clear way to select data from this construct. I also need it to be efficient as data will be huge in a year time

开发者_运维技巧

(Note: there will be only select where date = @singleDate never date between)

Example:

MainData             TemporalData
1 20101001 1.0 2.0   1 20101001 2.0 3.0
1 20101002 2.0 3.0   1 20101003 8.0 9.0
1 20101003 4.0 5.0   
1 20101004 6.0 3.0   

 select * from vwData where date = '20101002'

 1 20101002 2.0 3.0 2.0 3.0

 select * from vwData where date = '20101003'

 1 20101003 4.0 5.0 8.0 9.0

 select * from vwData where date = '20101004'

 1 20101004 6.0 3.0 8.0 9.0

---

First, splitting the data into a 1:1 relationship with more static data in one table and data that changes often in another table is not a bad approach. Second, it is not clear whether TemporalData.ChangeDate should match MainDate.[Date]. Assuming that is the case, then you can do something like so:

Select MainData.Key, MainData.[Date], MainData.a, MainData.b
    , TemporalData.c, TemporalData.d
From MainData
    Left Join TemporalData
        On TemporalData.Key = MainData.Key
            And TemporalData.ChangeDate = MainDate.[Date]
Where MainData.[Date] = @SomeDate

This assumes you will always have MainData values but might not have TemporalData values. You could put this in a view however you could not specify a parameter. A view is a virtual table and as such represents an unordered set of rows. Thus, your view would look like:

Create View vwMain 
As
Select MainData.Key, MainData.[Date], MainData.a, MainData.b
    , TemporalData.c, TemporalData.d
From MainData
    Left Join TemporalData
        On TemporalData.Key = MainData.Key
            And TemporalData.ChangeDate = MainDate.[Date]

You would then use the view like so:

Select ...
From vwMain
Where [Date] = @SomeDate

EDIT

It would appear that what you want is to always have a TemporalData row representing the the most recent date. This is not clear from your description although we can deduce it from your comments and sample output. So, given that, here's a solution. Obviously, the final result would not include the common-table expressions for the sample source data:

With MainData As
    (
    Select 1 As [Key], '20101001' As [Date], 1.0 As A, 2.0 As B
    Union All Select 1 As [Key], '20101002', 2.0, 3.0
    Union All Select 1 As [Key], '20101003', 4.0, 5.0
    Union All Select 1 As [Key], '20101004', 6.0, 3.0
    )
    , TemporalData As
    (
    Select 1 As [Key], '20101001' As [ChangeDate], 2.0 As C, 3.0 As D
    Union All Select 1 As [Key], '20101003', 8.0, 9.0
    )
    , RankedResults As
    (
    Select MainData.[Key], MainData.[Date], MainData.a, MainData.b
        , TemporalData.c, TemporalData.d, TemporalData.ChangeDate
        , Row_Number() Over ( Partition By MainData.[Key], MainData.[Date]
                          Order By TemporalData.ChangeDate Desc ) As Num
    From MainData
        Join TemporalData
            On TemporalData.Key = MainData.Key
               And TemporalData.ChangeDate <= MainData.[Date]
    )
Select *
From RankedResults
Where Num = 1
Order By [Date]