交叉引用最近日期数据 [英] cross reference nearest date data

问题描述

我有三个表ElecUser，ElecUsage，ElecEmissionFactor
ElecUser: UserID UserName 1 Main Building 2 Staff Quarter

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

ElecUsage: UserID Time Amount 1 1/7/2010 23230 1 8/10/2011 34340 1 8/1/2011 34300 1 2/3/2012 43430 1 4/2/2013 43560 1 3/2/2014 44540 2 3/6/2014 44000

ElecUsage: UserID Time Amount 1 1/7/2010 23230 1 8/10/2011 34340 1 8/1/2011 34300 1 2/3/2012 43430 1 4/2/2013 43560 1 3/2/2014 44540 2 3/6/2014 44000

ElecEmissionFactor: Time CO2Emission 1/1/2010 0.5 1/1/2011 0.55 1/1/2012 0.56 1/1/2013 0.57

ElecEmissionFactor: Time CO2Emission 1/1/2010 0.5 1/1/2011 0.55 1/1/2012 0.56 1/1/2013 0.57

预期结果:
UserName Time CO2 1 2010 11615 1 2011 37752 (34340*0.55 + 34300*0.55) 1 2012 24320.8 1 2013 24829.2 1 2014 25387.8 2 2014 25080

And intended outcome:
UserName Time CO2 1 2010 11615 1 2011 37752 (34340*0.55 + 34300*0.55) 1 2012 24320.8 1 2013 24829.2 1 2014 25387.8 2 2014 25080

逻辑是ElecUsage.Amount * ElecEmissionFactor. 如果是同一用户和同一年，则将它们加起来作为该年的记录. 我的查询是:
SELECT ElecUser.UserName, Year([ElecUsage].[Time]), SUM((ElecEmissionFactor.CO2Emission*ElecUsage.Amount)) As CO2 FROM ElecEmissionFactor, ElecUser INNER JOIN ElecUsage ON ElecUser.UserID = ElecUsage.UserID WHERE (((Year([ElecUsage].[Time]))>=Year([ElecEmissionFactor].[Time]))) GROUP BY ElecUser.UserName, Year([ElecUsage].[Time]) HAVING Year([ElecUsage].[Time]) = Max(Year(ElecEmissionFactor.Time));

The logic is ElecUsage.Amount * ElecEmissionFactor. If same user and same year, add them up for the record of that year. My query is:
SELECT ElecUser.UserName, Year([ElecUsage].[Time]), SUM((ElecEmissionFactor.CO2Emission*ElecUsage.Amount)) As CO2 FROM ElecEmissionFactor, ElecUser INNER JOIN ElecUsage ON ElecUser.UserID = ElecUsage.UserID WHERE (((Year([ElecUsage].[Time]))>=Year([ElecEmissionFactor].[Time]))) GROUP BY ElecUser.UserName, Year([ElecUsage].[Time]) HAVING Year([ElecUsage].[Time]) = Max(Year(ElecEmissionFactor.Time));

但是，这仅显示具有排放因子的年份.
面临的挑战是将没有排放因子的年份引用到有排放因子的最新年份.
子查询可能是解决方案之一，但我没有这样做.
我被卡住了一段时间.希望看到您的答复.
谢谢

However, this only shows the year with emission factor.
The challenge is to reference the year without emission factor to the latest year with emission factor.
Sub-query may be one of the solutions but i fail to do so.
I got stuck for a while. Hope to see your reply.
Thanks

推荐答案

尝试类似的方法.

-未测试

select T1.id, year(T1.time) as Time, sum(T1.amount*T2.co2emission) as CO2 
from ElecUsage T1 
left outer join ElecEmissionFactor T2 on (year(T1.time) = year(T2.time))
Group by year(T1.time), T1.id

使用子查询以这种方式获取相应的因子

use sub query to get the corresponding factor in this way

    select T1.id, 
        year(T1.time) as Time, 
        sum(T1.amount*
                    (
                    select top 1 CO2Emission from ElecEmissionFactor T2 
                    where year(T2.time) <= year(T1.time) order by T2.time desc
                    )
        ) as CO2 
from ElecUsage T1 
Group by year(T1.time), T1.id

这篇关于交叉引用最近日期数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！