Problem in finding First and Last value

[Tiger955]

Hi!

I have following problem, which I cannot solve. My hopes are on you specialists.

I have a table with assignments if employees to teams.

PositionsID(autowert),EmployeeID, timefrom, timeto, TeamNo

3, 123, 13:30, 16:45, 4
4, 123, 17:00, 19:00, 7
5, 432, 17:00, 20:00, 8
6, 987, 17:00, 19:00, 9
7, 987, 13:30, 16:45, 5

....

Some of the employees can be assigned to more than one team, the assignement is not chronicaly, meaning an earlier timefrom can be assigned later (see Employee 987)

As a resultlist I need following:

123, 13:30, 19:00, 4
432, 17:00, 20:00, 8
987, 13:30, 19:00, 5

I can get out MIN(timefrom) and MAX(timeto) per EmployeeID but not the first teamNo, which is the first team he starts his duty!!

Pleas help me on that!

Thanks
Michael

 

[Brianwarnock]

Is it purely coincidental that the team number you require in your sample is the min per employee?

Brian

 

[CJ_London]

Try the following (change names to suit):

SELECT DISTINCT 
EmployeeID, 
(SELECT Min(TimeFrom) FROM tblAssignments AS Tmp1 WHERE EmployeeID=tblAssignments.EmployeeID) AS Started,
(SELECT Max(TimeTo) FROM tblAssignments AS Tmp2 WHERE EmployeeID=tblAssignments.EmployeeID) AS Finished,
(SELECT TOP 1 TeamNo FROM tblAssignments AS Tmp3 WHERE EmployeeID=tblAssignments.EmployeeID and TimeFrom=(SELECT Min(TimeFrom) FROM tblAssignments AS Tmp4 WHERE EmployeeID=tblAssignments.EmployeeID)) AS StartTeam
FROM tblAssignments

 

[Tiger955]

Hi Brian,
Yes it is.
As I wrote, the assignment to a team is not chronological on the timefrom.

It may happen, that you define a timefrom 16:00 until 19:00 at team 5 and later the assigner give this employee another duty from 13:30 to 16:00 at team 7. This record will have a higher ID but includes the "first" teamnumber and the earliest timefrom.

Thanks
Michael

 

[Tiger955]

Hi CJ,
Thanks your reply , I will try it tommorow. Seems to be a "subsubsubquery", but looks good.

Thanks
Michael

 

[CoffeeGuru]

This worked for me

SELECT Table1.EmployeeID, First(Table1.TimeFrom) AS FirstOfTimeFrom, Last(Table1.TimeTo) AS LastOfTimeTo, Table1.EmployeeID
FROM Table1
GROUP BY Table1.EmployeeID, Table1.EmployeeID;

[Change names to suit]

 

[Tiger955]

Hi CJ!

Thanks a lot, it worked!!

BR
Michael

 

[Tiger955]

Hi CoffeGuru!
That does not work, as the FirstTimeFrom is not mandatory the earliest TimeFrom!

That was my Problem. The only solution seems to be the one from CJ.

Michael

 

[CJ_London]

No problem - bit dispappointed you thanked cofeeguru for his efforts and not me

 

[Tiger955]

Hi CJ!

YES I did !!

Please have a look 2 postings before!!

But I missed a certain button for Thanks! Maybe it was on another Forum with the button...

Again, YOUR posting was MY solution!

Michael

 

[Brianwarnock]

No problem - bit dispappointed you thanked cofeeguru for his efforts and not me

I was also puzzled, but I also find it demeaning to beg for thanks , I just want to know that a solution worked or not.

Brian

 

[CJ_London]

@Brian

find it demeaning to beg for thanks

hence the .