How can I get the current records based on their effective date?

advertisements

How do I get the current records based on it's Effective Date? Should I use a subquery? Is there anything I could use aside from MAX?

I have these table examples.

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

My goal is to get all of the ResourceID who currently belongs to the OrganizationUnit that the user puts in. For example, If the User puts SMESM1HTOVEOVE in the OrganizationID parameter then it pull out all ReourceID that is currently under SMESM1HTOVEOVE. So far my MAX query below does not work.

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

Below are the results of my short MAX query above. This is wrong because ResourceID VC1976 currently belongs to APCAP1HTOVEOVE effective on 2012-07-09 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

Can someone help provide their input please? Because I will use this for my stored proc below. I'll also include my proc for you own perusal.

Thank you!

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


I suppose there's other ways to do this, but I think this works:

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)

Here's a SQLFiddle to play with.

EDIT: Actually, that's probably over-complicated. Try this one:

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)