如何根据生效日期获取当前记录？

如何根据生效日期获取当前记录？我应该使用子查询吗？除了 MAX 之外还有什么可以使用的吗？

我有这些表格示例。

ResourceID  is the ID number of the Resource.
OrganizationId is the current Organization or Department of the Resource.
Effective Date is the start date or the first day of the Resource in an Organization.


ResourceID  OrganizationID  EffectiveDate   
VC1976      INTIN1HTHWYAMM  2009-12-23 00:00:00.000 
VC1976      INTIN1LGAMMAMS  2011-07-01 00:00:00.000 
VC1976      SMESM1HTOVEOVE  2012-07-01 00:00:00.000    
VC1976      APCAP1HTOVEOVE  2012-07-09 10:17:56.000

ResourceID  OrganizationID  EffectiveDate   
JV2579      VNMVN1HTHWYCMM  2009-07-01 00:00:00.000 
JV2579      INTIN1HTHWYCMM  2011-07-02 00:00:00.000 
JV2579      SMESM1HTOVEOVE  2012-07-01 00:00:00.000 

ResourceID  OrganizationID  EffectiveDate   
RJ1939      INTIN1HTOVEOVE  1995-01-30 00:00:00.000 
RJ1939      INTIN1HTOVEOVE  2007-07-25 00:00:00.000 
RJ1939      SMESM1HTOVEOVE  2012-07-01 00:00:00.000 

ResourceID  OrganizationID  EffectiveDate   
PJ8828      AREAR1HTHWYRHD  2012-04-01 00:00:00.000 
PJ8828      SMESM1HTOVEOVE  2012-07-01 00:00:00.000 

ResourceID  OrganizationID  EffectiveDate   
RS1220      INTIN1HTHWYCMM  1981-01-06 00:00:00.000 
RS1220      SMESM1HTOVEOVE  2012-07-01 00:00:00.000 

我的目标是获取所有的 ResourceID现在属于用户输入的 OrganizationUnit。例如，如果用户输入中小企业在 OrganizationID 参数中，然后它会提取当前下的所有 ReourceID中小企业。到目前为止，我下面的 MAX 查询不起作用。

select OrganizationID, ResourceID, MAX(EffectiveDate) as EffectiveDate from ResourceOrganization
where OrganizationID = 'SMESM1HTOVEOVE'
group by OrganizationID, ResourceID, EffectiveDate

下面是我上面简短的 MAX 查询的结果。这是错误的，因为 ResourceIDVC1976目前属于 APCAP1HTOVEOVE，于 2012 年 7 月 9 日 10:17:56.000 生效。

OrganizationID  ResourceID  EffectiveDate

SMESM1HTOVEOVE  JV2579     2012-07-01 00:00:00.000
SMESM1HTOVEOVE  PJ8828     2012-07-01 00:00:00.000
SMESM1HTOVEOVE  RJ1939     2012-07-01 00:00:00.000
SMESM1HTOVEOVE  RS1220     2012-07-01 00:00:00.000
SMESM1HTOVEOVE  VC1976     2012-07-01 00:00:00.000

有人可以帮忙提供意见吗？因为我将在下面将其用于我的存储过程。我还将包括我的过程供您自己细读。

谢谢你！

create table #Resources
(
ResourceID nvarchar(30),
OrganizationID nvarchar(15),
EffectiveDate datetime,
TimeEntryDate datetime
)

if @ResourceID <> ''
 begin
 insert into #Resources (ResourceID,OrganizationID,EffectiveDate)

    select ro.ResourceID, ro.OrganizationID, ro.EffectiveDate from ResourceOrganization ro,
    (select ResourceID, MAX(EffectiveDate) as maxEffectivedate from dbo.ResourceOrganization
    where ResourceID = @ResourceID
    group by ResourceID) as maxresults
    where ro.ResourceID = maxresults.ResourceID
    and ro.EffectiveDate = maxresults.maxEffectivedate
    end

else if @OrgUnit <> ''
 begin
 insert into #Resources (ResourceID,OrganizationID,EffectiveDate)
    select ro.ResourceID, ro.OrganizationID, ro.EffectiveDate from ResourceOrganization ro,
    (select ResourceID, MAX(EffectiveDate) as maxEffectivedate from dbo.ResourceOrganization
    where OrganizationID like '' + @OrgUnit + '%'
    group by ResourceID) as maxresults
    where ro.ResourceID = maxresults.ResourceID
    and ro.EffectiveDate = maxresults.maxEffectivedate


else if @ResourceID <> '' and @OrgUnit <> ''

begin
 insert into #Resources (ResourceID,OrganizationID,EffectiveDate)

    select ro.ResourceID, ro.OrganizationID, ro.EffectiveDate 
    from ResourceOrganization ro,
    (select ResourceID, MAX(EffectiveDate) as maxEffectivedate from dbo.ResourceOrganization
    where ResourceID = @ResourceID
    group by ResourceID) as maxresults
    where ro.ResourceID = maxresults.ResourceID
    and ro.EffectiveDate = maxresults.maxEffectivedate
    end

我想还有其他方法可以做到这一点，但我认为这是有效的：

DECLARE 
  @OrganizationID varchar(40)

SET @OrganizationID = 'SMESM1HTOVEOVE'

SELECT
  ro.ResourceID,
  ro.OrganizationID,
  max(ro.EffectiveDate)
FROM
  ResourceOrganization ro
WHERE
  ro.OrganizationID = @OrganizationID
GROUP BY
  ro.ResourceID,
  ro.OrganizationID
HAVING
  max(ro.EffectiveDate) = (
    SELECT 
      max(EffectiveDate)
    FROM 
      ResourceOrganization
    WHERE 
      ResourceID = ro.ResourceID)

这是一个SQLFiddle和玩。

编辑：实际上，这可能过于复杂。试试这个：

DECLARE 
  @OrganizationID varchar(40)

SET @OrganizationID = 'SMESM1HTOVEOVE'

SELECT
  ro.ResourceID,
  ro.OrganizationID,
  ro.EffectiveDate
FROM
  ResourceOrganization ro
WHERE
  ro.OrganizationID = @OrganizationID
  AND ro.EffectiveDate = (
    SELECT 
      max(EffectiveDate)
    FROM 
      ResourceOrganization
    WHERE 
      ResourceID = ro.ResourceID)