Cross Reference Nearest Date Data

I have three table ElecUser, ElecUsage, ElecEmissionFactor ElecUser: UserID UserName 1 Main Building 2 Staff Quarter ElecUsage: UserID Time Amount 1 1/7

Solution 1:

Try something like this..

-- not tested

select T1.id, year(T1.time) asTime, sum(T1.amount*T2.co2emission) as CO2 
from ElecUsage T1 
leftouterjoin ElecEmissionFactor T2 on (year(T1.time) =year(T2.time))
Groupbyyear(T1.time), T1.id

use sub query to get the corresponding factor in this way

select T1.id, 
        year(T1.time) asTime, 
        sum(T1.amount*
                    (
                    select top 1 CO2Emission from ElecEmissionFactor T2 
                    whereyear(T2.time) <=year(T1.time) orderby T2.time desc
                    )
        ) as CO2 
from ElecUsage T1 
Groupbyyear(T1.time), T1.id