按最近日期过滤查询结果 [英] Filter query result by most recent date

问题描述

此代码:

SELECT  
    O896IA_VEMPPRSA.REG_NR & O896IA_VEMPPRSA.DIS_NR AS RRDD,  
    Mgmt_Lvl.MGMT_LVL,  
    Count(O896IA_VEMPPRSA.SYS_EMP_ID_NR) AS Total,  
    Sum(IIf(emp_rlc_ir="Y",1,0)) AS Relocate,  
    Sum(IIf(emp_int_asn_ir="Y",1,0)) AS International_Assignment,  
    Sum(IIf(emp_tvl_ir="y",1,0)) AS Travel,  
    Sum(IIf(emp_spc_asn_ir="y",1,0)) AS Special_Assignment,  
    Sum(IIf([emp_rlc_ir]="Y",1,0))/[Total] AS Relocate_Percent,  
    Sum(IIf([emp_int_asn_ir]="Y",1,0))/[Total] AS International_Assignment_Percent,  
    Sum(IIf([emp_tvl_ir]="y",1,0))/[Total] AS Travel_Percent,  
    Sum(IIf([emp_spc_asn_ir]="y",1,0))/[Total] AS Special_Assignment_Percent

FROM (O867IA_VPJOBCO INNER JOIN Mgmt_Lvl  
    ON O867IA_VPJOBCO.JOB_GRP_CD = Mgmt_Lvl.JOB_GRP_CD)  
INNER JOIN (O896IA_VEMPPRSA INNER JOIN O867IA_VCAREER  
    ON O896IA_VEMPPRSA.SYS_EMP_ID_NR = O867IA_VCAREER.SYS_EMP_ID_NR)  
    ON O867IA_VPJOBCO.JOB_CLS_CD = O896IA_VEMPPRSA.JOB_CLS_CD

WHERE (((O896IA_VEMPPRSA.EMP_EMT_STS_CD)<>"T"))

GROUP BY  
    O896IA_VEMPPRSA.REG_NR & O896IA_VEMPPRSA.DIS_NR, 
    Mgmt_Lvl.MGMT_LVL,  
    Mgmt_Lvl.Ranking

ORDER BY Mgmt_Lvl.Ranking DESC;

为我提供了针对员工的调查答案，问题是该调查每年进行一次，我需要最近几年的答案.

gives me survey answers for employees, the problem is this survey is taken every year and I need the most recent years answers.

我尝试将MAX用于REC_EFF_STT_DT，但是对于某些员工，他们的答案每年都在变化，因此由于分组依据(我需要)，它仍会返回多年的答案.

I tried using the MAX for the REC_EFF_STT_DT but for some employees their answers changed each year so due to the group by (which I need) it still returns multiple years answers.

有没有一种方法可以提取最新数据?如果某个员工在2008年，2009年和2010年进行了调查，并不一定要在2013年，但在2011年，2012年或2013年却没有，那么我仍然希望获得2010年的数据，这是他们最近进行的调查我需要.

Is there a solution where I can just pull out the most recent data? It doesn't necessarily have to be 2013 if an employee took the survey in 2008, 2009, and 2010 but not in 2011, 2012, or 2013 then I still want that 2010 data, which ever is the most recent survey they took is what I need.

推荐答案

假设您有一个名为[Survey]的表，其中包含以下数据

Say you have a table named [Survey] with the following data

RespondentID    SurveyDate  Answer1                         Answer2
------------    ----------  ----------------------------    ----------------------------
1               2013-01-01  answer 1, respondent 1, 2013    answer 2, respondent 1, 2013
1               2012-01-01  answer 1, respondent 1, 2012    answer 2, respondent 1, 2012
2               2012-01-01  answer 1, respondent 2, 2012    answer 2, respondent 2, 2012

您可以使用以下形式的查询...

You can use a query of the form...

SELECT Survey.*
FROM
    Survey
    INNER JOIN
    (
        SELECT RespondentID, MAX(SurveyDate) AS MaxOfSurveyDate
        FROM Survey
        GROUP BY RespondentID
    ) AS MaxDate
        ON Survey.RespondentID = MaxDate.RespondentID
            AND Survey.SurveyDate = MaxDate.MaxOfSurveyDate

...以返回每个受访者的最新行，在这种情况下为:

...to return the most recent row for each respondent, which in this case would be:

RespondentID    SurveyDate  Answer1                         Answer2
------------    ----------  ----------------------------    ----------------------------
1               2013-01-01  answer 1, respondent 1, 2013    answer 2, respondent 1, 2013
2               2012-01-01  answer 1, respondent 2, 2012    answer 2, respondent 2, 2012

这篇关于按最近日期过滤查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！