[AccessD] select most recent

Hi Eric

Returning to your original quote:

> Given a date, I need to generate a list of territories and the most
> recently assigned manager. I can select the manager that started
> before the date.

You also need to rule out those territories where the manager has been
assigned another territory without another manager being assigned the
territory he left; such a territory is unassigned.
This means you'll have to pick the last date a territory was assigned
a manager AND the last date that manager was assigned; these dates
must match.

Also, to pick up the discussion on keys, you should append an
autonumber ID field to your table to be able to easily identify any
record as several territories could change manager on the same date.

Thus, your query could now read:

<SQL>

PARAMETERS DateSelect DateTime;
SELECT
  TerritoryID,
  ManagerID,
  DateAssigned
FROM
  tblManagerAssignments
WHERE
  (tblManagerAssignments.ID IN
    (SELECT TOP 1
      ID
    FROM
      tblManagerAssignments AS aliT
    WHERE
      tblManagerAssignments.TerritoryID = aliT.TerritoryID
      AND
      aliT.DateAssigned <= [DateSelect]
    ORDER BY
      aliT.DateAssigned DESC))
  AND
  (tblManagerAssignments.ID IN
    (SELECT TOP 1
      ID
    FROM
      tblManagerAssignments AS aliT
    WHERE
      tblManagerAssignments.ManagerID = aliT.ManagerID
      AND
      aliT.DateAssigned <= [DateSelect]
    ORDER BY
      aliT.DateAssigned DESC));
      
</SQL>

Those territories not listed by this query are unassigned at the
selected date.
You should test this on an extended dataset.

/gustav


>> Thank you for taking up my question. That comes tantalizingly close. The
>> trouble is that with the [ManagerID] in the GROUP BY, I end up with most
>> of the managers that have been assigned to the territory prior to
>> [DateSelect] instead of just the most recent one. If a manager had been
>> in the territory more than once, only the most recent assignment is
>> returned. So I do get some filtering.

> Well, you end up with all the managers, sorry.
> You could feed it to another query to filter that out but I would
> rather suggest to follow the "TOP 1" route as suggested by Drew.

> By the way, no trouble with Null values; they simply represent "not
> known" or "undefined" which exactly would be the case here.

> /gustav


>> My sample data looks like this:

>> ManagerID  Territory ID  DateAssigned
>> 1                  1                11/1/2002
>> 2                  1                12/1/2002
>> 1                  1                  1/1/2003
>> 3                  2                  1/1/2003
>> 4                  2                  2/1/2003

>> For a report as of 1/31/2003, I am trying to get:

>> ManagerID  Territory ID  DateAssigned
>> 1                  1                  1/1/2003
>> 3                  2                  1/1/2003

>> I use this:

>> <SQL>

>> SELECT 
>>   tblManagerAssignments.TerritoryID,
>>   tblManagerAssignments.MangerID,
>>   Max(tblManagerAssignments.DateAssigned) AS MaxOfDateAssigned
>> FROM tblManagerAssignments
>> WHERE (((tblManagerAssignments.DateAssigned)<=#1/31/2003#))
>> GROUP BY
>>   tblManagerAssignments.TerritoryID,
>>   tblManagerAssignments.MangerID;

>> </SQL>

>> I end up with this:

>> ManagerID  Territory ID  DateAssigned
>> 2                  1                12/1/2002
>> 1                  1                  1/1/2003
>> 3                  2                  1/1/2003

>> I could add a [DateCancelled] field, but I just don't feel right about a
>> design that incorporates NULL fields. Maybe I could calculate the
>> [DateCancelled] field. Got any more ideas?


>> -----Original Message-----
>> From: Gustav Brock [mailto:gustav at cactus.dk] 
>> Sent: Monday, February 17, 2003 2:16 AM
>> To: Eric Goetz
>> Subject: Re: [AccessD] select most recent

>> That could be something like:

>> <SQL> 

>> PARAMETERS 
>>   DateSelect DateTime; 
>> SELECT 
>>   TerritoryID, 
>>   ManagerID, 
>>   Max(DateAssigned) AS DateAssigned 
>> FROM 
>>   tblManagerAssignments 
>> WHERE 
>>   (DateAssigned <= [DateSelect]) 
>> GROUP BY 
>>   TerritoryID, 
>>   ManagerID; 

>> </SQL> 

>> This, of course, assumes that a territory is assigned to a specific 
>> manager until assigned to another. If assignment can be cancelled 
>> without reassignment, you'll need to add a new field, DateCancelled, 
>> and add to the Where statement: 

>>   AND 
>>   (DateCancelled Is Null OR DateCancelled > [DateSelect]) 

>> If you wish to list territories not assigned a manager, create a query 
>> with all territories and an outer join to the query above; those not 
>> assigned will have a Null for ManagerID. Vice versa for managers 
>> without a territory. 

>> Please note that ManagerID and TerritoryID will both be foreign keys. 
>> And, as you note later, strip the name fields etc. from this table.