More complicated use of aggregates...

Greetings all!
I am stuck with something at the moment, and that added to the cold thats got me in its icy grip is really batting my head :(
Basically I have a table of records:
Record_ID (id) | Employee_ID (pin) | Record_type (msgtype) | record_date | record_time (logtime)
What I am looking for is, for each employee_ID on a specific day, the Record_ID of the earliest record of type '5'. I've only got one date in there at the moment so thats not such a problem. My attempt was:

SELECT combined.ID
FROM combined
GROUP BY combined.pin
HAVING min(logtime);
but that didnt work, complaining that combined.id is not in the group by.

Can anyone provide any suggestions?

Thanks!
~Shiv

EDIT: Forgot to mention, I'm using MS Access...Perhaps something like this (or its variations - I'd say that interesting part is the use of a subquery):select c.employee_id, c.record_id
from combined c
where c.logtime = (select min(c1.logtime)
from combined c1
where c1.employee_id = c.employee_id
)
and c.record_type = 'S'
group by c.employee_id, c.record_id;|||Cool, I'm giving that a go so I'll let you know how it works out. It's gotta go through about just under a million records, so its taking a while!!!
~T

Months and Years between Dates

Hi All,
I am stuck with a Date problem which I am trying to execute in a Stored Proc.

I basically get a Start Month and a Start Year And an End month & an End Year from a screen that I build.
Now what I want to do is to traverse all the Months_Year for this period.

ie For example if my start year is Feb 2001 and end Year is July 2004, then I need
Feb 2001
March 2001
April 2001
....
Jan 2004
..
July 2004 in a cursor.

Thanks in anticipation.
Raman.Hmmm what database are you using Oracle or Sybase ?
if its Sybase you can use the datediff to get your dates
if in Oracle you can use the to_date, to_char to manipulate the given dates and return what you want.

Originally posted by ramanjaiya
Hi All,
I am stuck with a Date problem which I am trying to execute in a Stored Proc.

I basically get a Start Month and a Start Year And an End month & an End Year from a screen that I build.
Now what I want to do is to traverse all the Months_Year for this period.

ie For example if my start year is Feb 2001 and end Year is July 2004, then I need
Feb 2001
March 2001
April 2001
....
Jan 2004
..
July 2004 in a cursor.

Thanks in anticipation.
Raman.|||Originally posted by llccoo
Hmmm what database are you using Oracle or Sybase ?
if its Sybase you can use the datediff to get your dates
if in Oracle you can use the to_date, to_char to manipulate the given dates and return what you want.

I am using SQL Server.
Basically What I amtrying now is to create 2 Temp tables. One with all the months, and one with all the years & then looping twice to create my combinations.
I feel it could be done in a better way although!|||please see the articles The integers table (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid569539_tax285649,00.html) and Finding all the dates between two dates (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid474893_tax285649,00.html) (registration may be required, but it's free)

the examples show how to generate a series of dates using an integers table

applied to your example, you would use the integers within a DATEADD() function using the integer as the number of months to add from a starting date