[AccessD] Query Help Needed

[David McAfee]

if the time is stored in the date assigned (using NOW() instead of DATE()
 ), you can use a min:
If you're not storing the time, maybe do a min on the PKID

(Warning Air code)

SELECT B.CallNo, B.DateAssigned, B.Technician
(SELECT CallNo, Min(DateAssigned) AS MinDate FROM YourTable GROUP BY
CallNo) A
INNER JOIN YourTable B ON A.CallNo = B.CallNo And A.MinDate = B.DateAssigned



On Fri, Mar 13, 2015 at 1:37 PM, John Bodin <jbodin at sbor.com> wrote:

> Stuck on this reporting scenario.  I have service calls that are assigned
> to one or more technicians on a particular day.  If the technician(s)
> cannot fix the problem on that day, he/they may get assigned again on a
> following day or another technician may get assigned.  For the particular
> report, I only want to consider those technicians that were assigned to the
> call on the first day and exclude any other days the call may have been
> assigned.  I'm trying to avoid writing out to a temp table and can't figure
> out the query for the report.  Some sample data would be (I sorted by Call
> # in below example):
>
> Call #    Date Assigned    Technician
> 1234       03/01/2015          Jim
> 1234       03/01/2015          Tom
> 1234       03/03/2015          Jim
> 1234       03/03/2015          Steve
> 2222       03/01/2015          Bill
> 3333       03/02/2015          Mike
> 3333       03/04/2015          Ben
>
> Results of query I would like
> 1234       03/01/2015          Jim
> 1234       03/01/2015          Tom
> 2222       03/01/2015          Bill
> 3333       03/02/2015          Mike
>
> Thoughts?  Thx.
>
> John
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>