How do I get the current records based on it's Effective Date?

Question

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

Do you have any unique key in your table? That will make it more easily. — Jade, Feb 07 '14 at 02:26

Zec · Accepted Answer · 2014-02-07T03:03:15.537

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)

score 0 · Answer 2 · answered Feb 07 '14 at 03:11

0

You may also try this using CTE

;WITH tmpWithUID As
(
    SELECT  Row_Number()  OVER(ORDER BY ResourceID, EffectiveDate, OrganizationID) AS rowNumber,
            ResourceID, OrganizationID, EffectiveDate
    FROM    dbo.ResourceOrganization    
    GROUP BY ResourceID, EffectiveDate, OrganizationID
)
SELECT  ResourceID, OrganizationID, EffectiveDate
FROM    tmpWithUID
WHERE   tmpWithUID.rowNumber IN (
            SELECT  max(rowNumber) rowNumber
            FROM    tmpWithUID
            GROUP BY ResourceID
        )
        AND OrganizationID = 'SMESM1HTOVEOVE'

answered Feb 07 '14 at 03:11

Jade

2,972
1
11
9

I also compared to Zec sample, when i look in the execution plan my script run with a 10% difference. But I'm not sure to other SQL Server environment. – Jade Feb 07 '14 at 07:29
Ok Jade let me run some series of tests. Thank you for helping me with this – TheBA.BI.BO Feb 09 '14 at 22:36

score 0 · Answer 3 · answered Feb 07 '14 at 03:19

You could try it using a cursor. This example just outputs to the screen but you could insert the results into a temporary table and then return the results if you want to execute it via a SQLDataAdapter or the like.

SET NOCOUNT ON;

DECLARE tmpCursor CURSOR FOR
SELECT DISTINCT ResourceID
  FROM ResourceOrganization 
ORDER BY 1

OPEN tmpCursor

DECLARE @resID VARCHAR(12)
DECLARE @orgID VARCHAR(12)
DECLARE @effDate INT

FETCH NEXT FROM tmpCursor INTO @resID

PRINT '-----------  --------------  ------------------'
PRINT 'Resource ID  OrganizationID  Effective Date'
PRINT '-----------  --------------  ------------------'
WHILE @@FETCH_STATUS = 0 BEGIN
  SELECT @effDate = MAX(EffectiveDate) 
    FROM ResourceOrganization 
   WHERE ResourceID = @resID

  SELECT TOP 1 @orgID = OrganizationID 
    FROM ResourceOrganization 
   WHERE ResourceID = @resID AND EffectiveDate = @effDate

  PRINT @resID + '  ' + @orgID + '  ' + CONVERT(VARCHAR(24), @effDate, 109) 

  FETCH NEXT FROM tmpCursor INTO @resID
END
PRINT '-----------  --------------  ------------------'

CLOSE tmpCursor
DEALLOCATE tmpCursor
GO

How do I get the current records based on it's Effective Date?

3 Answers3

Linked