Hi there

I have two tables, T and U, that look something like this:

Table T

EmpNo EmpName
---------- ---------------
1 Larry
2 Lawrence
3 Laurence
4 Laurent
5 Laurel
....

Table U

EmpNo PreviousExp
---------- -----------------
1 SA Cargo
1 SA Beverages
2 US Diamonds
2 US Gold
2 US Tanzanite
....

I would like to add the first three PreviousExp entries in Table U to Table
T, so that Table T looks something like this:

EmpNo EmpName PreviousExp1 PreviousExp2 PreviousExp3
---------- --------------- ------------------- -------------------
-------------------
1 Larry SA Cargo SA Beverages
<NULL>
2 Lawrence US Diamonds US Gold US
Tanzanite
3 Laurence ....
4 Laurent
5 Laurel
....

Do you know how I could achieve this?

Best regards
Loane

Sounds like you want pivoting and ranking. Here's a code snippet using the
Northwind DB. You can adapt to suit:

select

x.CustomerID

, min (case when x.Position = 0 then x.OrderDate end)

, min (case when x.Position = 1 then x.OrderDate end)

, min (case when x.Position = 2 then x.OrderDate end)

from

(

select

c.CustomerID

, o.OrderDate

, (select count (*) from Orders o2

where o2.CustomerID = c.CustomerID

and (o2.OrderDate > o.OrderDate

or (o2.OrderDate = o.OrderDate

and o2.OrderID > o.OrderID))

) as Position

from

Customers c

left

join Orders o on o.CustomerID = c.CustomerID

) as x

where

x.Position < 3

group by

x.CustomerID

order by

x.CustomerID


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
Hi there

I have two tables, T and U, that look something like this:

Table T

EmpNo EmpName
---------- ---------------
1 Larry
2 Lawrence
3 Laurence
4 Laurent
5 Laurel
....

Table U

EmpNo PreviousExp
---------- -----------------
1 SA Cargo
1 SA Beverages
2 US Diamonds
2 US Gold
2 US Tanzanite
....

I would like to add the first three PreviousExp entries in Table U to Table
T, so that Table T looks something like this:

EmpNo EmpName PreviousExp1 PreviousExp2 PreviousExp3
---------- --------------- ------------------- -------------------
-------------------
1 Larry SA Cargo SA Beverages
<NULL>
2 Lawrence US Diamonds US Gold US
Tanzanite
3 Laurence ....
4 Laurent
5 Laurel
....

Do you know how I could achieve this?

Best regards
Loane

In future, could you please post DDL with INSERT statements of your sample
data? This would save us a lot of time in producing your solution:

create table t
(
EmpNo int not null
, PreviousExp varchar (20) not null
, Salary money not null
, primary key (EmpNo, PreviousExp, Salary)
)
go



insert t values (1, 'SA Cargo', $5000)
insert t values (1, 'SA Cargo', $15000)
insert t values (1, 'SA Beverages', $10000)
insert t values (1, 'SA Beverages', $15000)
insert t values (1, 'SA Beverages', $20000)
insert t values (2, 'US Diamonds', $25000)
insert t values (2, 'US Diamonds', $20000)
insert t values (2, 'US Gold', $35000)
insert t values (2, 'US Tanzanite', $45000)
go

create view v
as
select
EmpNo
, PreviousExp
, max (Salary) Salary
from
t
group by
EmpNo
, PreviousExp
go

select
v1.*
, (select count (*)
from v v2
where v2.EmpNo = v1.EmpNo
and v2.Salary <= v1.Salary) Position
from
v v1
order by
EmpNo
, Position

go

drop view v
drop table t



--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
Hi there
Say my query contains an aggregate (ie. in addition to those given in the
examples below), for instance MAX(Salary).

[quoted text, click to view]

Can I use your Position suggestion in this case? Specifically I'm looking
for a table as follows:

EmpNo PreviousExp Max(Salary) Position
-------------- ----------------- ------------------ -----------
[quoted text, click to view]



Best regards
Loane

Here's an alternative solution:

select
v1.EmpNo
, v1.PreviousExp
, v1.Salary
, count (*) Position
from
v v1
join v v2 on v2.EmpNo = v1.EmpNo
and v2.Salary <= v1.Salary
group by
v1.EmpNo
, v1.PreviousExp
, v1.Salary
order by
v1.EmpNo
, Position


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
In future, could you please post DDL with INSERT statements of your sample
data? This would save us a lot of time in producing your solution:

create table t
(
EmpNo int not null
, PreviousExp varchar (20) not null
, Salary money not null
, primary key (EmpNo, PreviousExp, Salary)
)
go



insert t values (1, 'SA Cargo', $5000)
insert t values (1, 'SA Cargo', $15000)
insert t values (1, 'SA Beverages', $10000)
insert t values (1, 'SA Beverages', $15000)
insert t values (1, 'SA Beverages', $20000)
insert t values (2, 'US Diamonds', $25000)
insert t values (2, 'US Diamonds', $20000)
insert t values (2, 'US Gold', $35000)
insert t values (2, 'US Tanzanite', $45000)
go

create view v
as
select
EmpNo
, PreviousExp
, max (Salary) Salary
from
t
group by
EmpNo
, PreviousExp
go

select
v1.*
, (select count (*)
from v v2
where v2.EmpNo = v1.EmpNo
and v2.Salary <= v1.Salary) Position
from
v v1
order by
EmpNo
, Position

go

drop view v
drop table t



--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
Hi there
Say my query contains an aggregate (ie. in addition to those given in the
examples below), for instance MAX(Salary).

[quoted text, click to view]

Can I use your Position suggestion in this case? Specifically I'm looking
for a table as follows:

EmpNo PreviousExp Max(Salary) Position
-------------- ----------------- ------------------ -----------
[quoted text, click to view]



Best regards
Loane

Thanks Tom, that kept me busy for a bit, but it worked perfectly.

Hi there
Say my query contains an aggregate (ie. in addition to those given in the
examples below), for instance MAX(Salary).

[quoted text, click to view]

Can I use your Position suggestion in this case? Specifically I'm looking
for a table as follows:

EmpNo PreviousExp Max(Salary) Position
-------------- ----------------- ------------------ -----------
[quoted text, click to view]



Best regards
Loane

Hi there

(Please find INSERT statements to reproduce behaviour at bottom)

The following code ...

USE candidate

SELECT t1.admref, t1.company, t1.wedate2, count (*) Position

FROM timesheets t1

JOIN timesheets t2 ON t2.admref = t1.admref
AND t2.wedate2 <= t1.wedate2
AND t1.admref < 500

GROUP BY t1.admref, t1.company, t1.wedate2
ORDER BY t1.admref, Position

.... returns the following results ...

(In order, AdmRef, Company, WEDate, Position)

153 Speed Services Couriers 19990530 1
153 Speed Services Couriers 19990606 2
153 Speed Services Couriers 19990613 3
153 Speed Services Couriers 19990620 4
153 Speed Services Couriers 19990627 5
153 Speed Services Couriers 19990704 6
153 Speed Services Couriers 19990711 1
211 Speed Services Couriers 19990530 2
211 Speed Services Couriers 19990606 3
211 Speed Services Couriers 19990613 4
211 Industrial Urethanes (Pty Ltd 19990730 5
211 Industrial Urethanes (Pty Ltd 19990808 6
211 Industrial Urethanes (Pty Ltd 19990829 7
211 Industrial Urethanes (Pty Ltd 19991017 8
211 Industrial Urethanes (Pty Ltd 19991024 9
315 Alcatel Altech Telecoms (Pty Ltd 19990530 1
315 Alcatel Altech Telecoms (Pty Ltd 19990606 2
315 Alcatel Altech Telecoms (Pty Ltd 19990613 3
315 Alcatel Altech Telecoms (Pty Ltd 19990620 4
315 Alcatel Altech Telecoms (Pty Ltd 19990627 5
315 Alcatel Altech Telecoms (Pty Ltd 19990704 6
315 Alcatel Altech Telecoms (Pty Ltd 19990725 7
315 Alcatel Altech Telecoms (Pty Ltd 19990801 8
315 Alcatel Altech Telecoms (Pty Ltd 19990808 9

Instead, I would like the results to have the following form (ie. the last
date worked for each company in the employment history):

153 Speed Services Couriers 19990711 7
211 Speed Services Couriers 19990613 3
211 Industrial Urethanes (Pty) Ltd 19991024 8
315 Alcatel Altech Telecoms (Pty) Ltd 19990808 11

The INSERT statements to reproduce the behaviour are as follows:

create table timesheets
(
admref int not null
, company varchar (40) not null
, wedate datetime not null
, primary key (admref, company, datetime)
)
go


insert timesheets values (153, 'Speed Services Couriers', 19990530)
insert timesheets values (153, 'Speed Services Couriers', 19990606)
insert timesheets values (153, 'Speed Services Couriers', 19990613)
insert timesheets values (153, 'Speed Services Couriers', 19990620)
insert timesheets values (153, 'Speed Services Couriers', 19990627)
insert timesheets values (153, 'Speed Services Couriers', 19990704)
insert timesheets values (153, 'Speed Services Couriers', 19990711)
insert timesheets values (211, 'Speed Services Couriers', 19990530)
insert timesheets values (211, 'Speed Services Couriers', 19990606)
insert timesheets values (211, 'Speed Services Couriers', 19990613)
insert timesheets values (211, 'Industrial Urethanes (Pty) Ltd', 19990730)
insert timesheets values (211, 'Industrial Urethanes (Pty) Ltd', 19990808)
insert timesheets values (211, 'Industrial Urethanes (Pty) Ltd', 19990829)
insert timesheets values (211, 'Industrial Urethanes (Pty) Ltd', 19991017)
insert timesheets values (211, 'Industrial Urethanes (Pty) Ltd', 19991024)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990530)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990606)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990613)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990620)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990627)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990704)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990725)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990801)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990808)
go

Best regards
Loane


[quoted text, click to view]

Thanks very much, will do.

Try:

SELECT t1.admref, t1.company, count (*) Position

FROM timesheets t1

GROUP BY t1.admref, t1.company
ORDER BY t1.admref, Position


However, using your exact data, I get:
admref company Position
153 Speed Services Couriers 7
211 Speed Services Couriers 3
211 Industrial Urethanes (Pty) Ltd 5
315 Alcatel Altech Telecoms (Pty) Ltd 9


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
Hi there

(Please find INSERT statements to reproduce behaviour at bottom)

The following code ...

USE candidate

SELECT t1.admref, t1.company, t1.wedate2, count (*) Position

FROM timesheets t1

JOIN timesheets t2 ON t2.admref = t1.admref
AND t2.wedate2 <= t1.wedate2
AND t1.admref < 500

GROUP BY t1.admref, t1.company, t1.wedate2
ORDER BY t1.admref, Position

.... returns the following results ...

(In order, AdmRef, Company, WEDate, Position)

153 Speed Services Couriers 19990530 1
153 Speed Services Couriers 19990606 2
153 Speed Services Couriers 19990613 3
153 Speed Services Couriers 19990620 4
153 Speed Services Couriers 19990627 5
153 Speed Services Couriers 19990704 6
153 Speed Services Couriers 19990711 1
211 Speed Services Couriers 19990530 2
211 Speed Services Couriers 19990606 3
211 Speed Services Couriers 19990613 4
211 Industrial Urethanes (Pty Ltd 19990730 5
211 Industrial Urethanes (Pty Ltd 19990808 6
211 Industrial Urethanes (Pty Ltd 19990829 7
211 Industrial Urethanes (Pty Ltd 19991017 8
211 Industrial Urethanes (Pty Ltd 19991024 9
315 Alcatel Altech Telecoms (Pty Ltd 19990530 1
315 Alcatel Altech Telecoms (Pty Ltd 19990606 2
315 Alcatel Altech Telecoms (Pty Ltd 19990613 3
315 Alcatel Altech Telecoms (Pty Ltd 19990620 4
315 Alcatel Altech Telecoms (Pty Ltd 19990627 5
315 Alcatel Altech Telecoms (Pty Ltd 19990704 6
315 Alcatel Altech Telecoms (Pty Ltd 19990725 7
315 Alcatel Altech Telecoms (Pty Ltd 19990801 8
315 Alcatel Altech Telecoms (Pty Ltd 19990808 9

Instead, I would like the results to have the following form (ie. the last
date worked for each company in the employment history):

153 Speed Services Couriers 19990711 7
211 Speed Services Couriers 19990613 3
211 Industrial Urethanes (Pty) Ltd 19991024 8
315 Alcatel Altech Telecoms (Pty) Ltd 19990808 11

The INSERT statements to reproduce the behaviour are as follows:

create table timesheets
(
admref int not null
, company varchar (40) not null
, wedate datetime not null
, primary key (admref, company, datetime)
)
go


insert timesheets values (153, 'Speed Services Couriers', 19990530)
insert timesheets values (153, 'Speed Services Couriers', 19990606)
insert timesheets values (153, 'Speed Services Couriers', 19990613)
insert timesheets values (153, 'Speed Services Couriers', 19990620)
insert timesheets values (153, 'Speed Services Couriers', 19990627)
insert timesheets values (153, 'Speed Services Couriers', 19990704)
insert timesheets values (153, 'Speed Services Couriers', 19990711)
insert timesheets values (211, 'Speed Services Couriers', 19990530)
insert timesheets values (211, 'Speed Services Couriers', 19990606)
insert timesheets values (211, 'Speed Services Couriers', 19990613)
insert timesheets values (211, 'Industrial Urethanes (Pty) Ltd', 19990730)
insert timesheets values (211, 'Industrial Urethanes (Pty) Ltd', 19990808)
insert timesheets values (211, 'Industrial Urethanes (Pty) Ltd', 19990829)
insert timesheets values (211, 'Industrial Urethanes (Pty) Ltd', 19991017)
insert timesheets values (211, 'Industrial Urethanes (Pty) Ltd', 19991024)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990530)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990606)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990613)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990620)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990627)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990704)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990725)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990801)
insert timesheets values (315, 'Alcatel Altech Telecoms (Pty) Ltd',
19990808)
go

Best regards
Loane


[quoted text, click to view]

Here ya go:

SELECT distinct
t1.admref
, t1.company
, (select count (distinct t2.company)
from timesheets t2
where t2.admref = t1.admref
and t2.company <= t1.company
) Position
FROM timesheets t1
ORDER BY t1.admref, Position


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
Thanks a lot Tom. One last one then I'll quit bugging you ...

Let's say I wanted to get the results (using the same data as before) in the
following form:

[quoted text, click to view]

i.e. A sequence of numbers (1,2,3...) representing the assignment number in
each person's work history.

Best regards
Loane

[quoted text, click to view]

Thanks a lot Tom. One last one then I'll quit bugging you ...

Let's say I wanted to get the results (using the same data as before) in the
following form:

[quoted text, click to view]

i.e. A sequence of numbers (1,2,3...) representing the assignment number in
each person's work history.

Best regards
Loane

[quoted text, click to view]

Hi Tom

I followed your great advice, and things are working swimmingly. However
I've come unstuck with a particular SQL statement.

Just to remind you, I have a large table of staff timesheet records (3
million or so). Each record represents a single timesheet (captured weekly),
and the fields record the various characteristics of the timesheet: e.g.
employee name, employee number, division worked for, week-ending date,
number of hours worked during the week, etc.

EmpName EmpNo Division WeekEndDt
---------------------------------------------------------------
Joe Soap 12345 Banking Dec 14, 2002
Joe Soap 12345 Banking Dec 21, 2002
Joe Soap 12345 Banking Dec 28, 2002
Joe Soap 12345 Banking Jan 1, 2003
Joe Soap 12345 Acting Apr 17, 2004
Joe Soap 12345 Acting Apr 24, 2004
Joe Soap 12345 Acting May 1, 2004
Joe Soap 12345 Acting May 8, 2004
Joe Soap 12345 Acting May 15, 2004
Joe Soap 12345 Baking Sep 12, 2005
Pete Soak 34567 Bobbing Feb 14, 1976
Ann Cope 67890 Bragging Sep 19, 1972
Ann Cope 67890 Bragging Sep 26, 1972
Ann Cope 67890 Bragging Oct 3, 1972

I would like to have the results in the following format:

EmpName EmpNo Division AssnmtEndDt AssnmtNo
-----------------------------------------------------------------------------------
Joe Soap 12345 Banking Jan 1, 2003 1
Joe Soap 12345 Acting May 15, 2004 2
Joe Soap 12345 Baking Sep 12, 2005 3
Pete Soak 34567 Bobbing Feb 14, 1976 1
Ann Cope 67890 Bragging Oct 3, 1972 1

The SQL statement that I'm currently using works very well, as, for
instance, in a table representing the employee's various skills, query given
below.

USE candidate
UPDATE candtemp
SET origskills = y.skill
FROM
(
SELECT z.admref 'admref',
SUBSTRING (COALESCE (', ' + z.skill1, '') + COALESCE(', ' + z.skill2, '')
+ COALESCE(', ' + z.skill3, '') + COALESCE(', ' + z.skill4, ''), 3, 255)
'skill'
FROM
(SELECT x.admref 'admref', MIN(CASE WHEN x.position = 0 THEN x.skills END)
'skill1',
MIN(CASE WHEN x.position = 1 THEN x.skills END) 'skill2',
MIN(CASE WHEN x.position = 2 THEN x.skills END) 'skill3',
MIN(CASE WHEN x.position = 3 THEN x.skills END) 'skill4'
FROM
(SELECT c.admref, s1.skills,
(SELECT COUNT(*)
FROM F01Skills s2
WHERE s2.admref = c.admref
AND (s2.admref > s1.admref
OR (s2.admref = s1.admref
AND s2.skills < s1.skills))) AS position
FROM F01Skills s1
INNER JOIN F01Candidate c ON c.admref = s1.admref
WHERE c.admref <= s1.admref) AS x
INNER JOIN F01Candidate c ON (c.admref = x.admref)
INNER JOIN candtemp p ON (p.admref = c.admref)
WHERE x.position < 4
AND x.skills IS NOT NULL
GROUP BY x.admref) AS z
) AS y
WHERE candidate.dbo.candtemp.[admref] = y.admref
GO

However, I'm trying to retrieve the division worked for on the assignment
end date (something like SELECT company FROM timesheets WHERE wedate =
MAX(wedate)). Unlike the skills statement given above, where each employee
can have multiple skills (records) recorded in a single field called
"skills", the problem that arises in the timesheet example is that an
employee can work for a single division for many consecutive weeks.

Please help! (Some records given below)

create table timesheets
(
EmpName nvarchar(255) not null,
EmpNo int not null,
Division nvarchar(50) not null,
WeekEndDt datetime not null)
)

insert timesheets values ('Joe Soap', 12345, 'Banking', Dec
14, 2002)
insert timesheets values ('Joe Soap', 12345, 'Banking', Dec
21, 2002)
insert timesheets values ('Joe Soap', 12345, 'Banking', Dec
28, 2002)
insert timesheets values ('Joe Soap', 12345, 'Banking', Jan 1,
2003)
insert timesheets values ('Joe Soap', 12345, 'Acting', Apr
17, 2004)
insert timesheets values ('Joe Soap', 12345, 'Acting', Apr
24, 2004)
insert timesheets values ('Joe Soap', 12345, 'Acting', May
1, 2004)
insert timesheets values ('Joe Soap', 12345, 'Acting', May
8, 2004)
insert timesheets values ('Joe Soap', 12345, 'Acting', May
15, 2004)
insert timesheets values ('Joe Soap', 12345, 'Baking', Sep
12, 2005)
insert timesheets values ('Pete Soak', 34567, 'Bobbing', Feb 14,
1976)
insert timesheets values ('Ann Cope', 67890, 'Bragging', Sep 19,
1972)
insert timesheets values ('Ann Cope', 67890, 'Bragging', Sep 26,
1972)
insert timesheets values ('Ann Cope', 67890, 'Bragging', Oct 3,
1972)

Here are two ways to get the final assignment and the corresponding data:

select
o.*
from
timesheets o
where
o.WeekEndDt =
(
select
max (i.WeekEndDt)
from
timesheets i
where
i.EmpNo = o.EmpNo
)
go

select
o.EmpName
, o.EmpNo
, o.Division
, o.WeekEndDt
from
timesheets o
join timesheets i on i.EmpNo = o.EmpNo

group by
o.EmpName
, o.EmpNo
, o.Division
, o.WeekEndDt
having
o.WeekEndDt = max (i.WeekEndDt)
go


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
Hi Tom

I followed your great advice, and things are working swimmingly. However
I've come unstuck with a particular SQL statement.

Just to remind you, I have a large table of staff timesheet records (3
million or so). Each record represents a single timesheet (captured weekly),
and the fields record the various characteristics of the timesheet: e.g.
employee name, employee number, division worked for, week-ending date,
number of hours worked during the week, etc.

EmpName EmpNo Division WeekEndDt
---------------------------------------------------------------
Joe Soap 12345 Banking Dec 14, 2002
Joe Soap 12345 Banking Dec 21, 2002
Joe Soap 12345 Banking Dec 28, 2002
Joe Soap 12345 Banking Jan 1, 2003
Joe Soap 12345 Acting Apr 17, 2004
Joe Soap 12345 Acting Apr 24, 2004
Joe Soap 12345 Acting May 1, 2004
Joe Soap 12345 Acting May 8, 2004
Joe Soap 12345 Acting May 15, 2004
Joe Soap 12345 Baking Sep 12, 2005
Pete Soak 34567 Bobbing Feb 14, 1976
Ann Cope 67890 Bragging Sep 19, 1972
Ann Cope 67890 Bragging Sep 26, 1972
Ann Cope 67890 Bragging Oct 3, 1972

I would like to have the results in the following format:

EmpName EmpNo Division AssnmtEndDt AssnmtNo
-----------------------------------------------------------------------------------
Joe Soap 12345 Banking Jan 1, 2003 1
Joe Soap 12345 Acting May 15, 2004 2
Joe Soap 12345 Baking Sep 12, 2005 3
Pete Soak 34567 Bobbing Feb 14, 1976 1
Ann Cope 67890 Bragging Oct 3, 1972 1

The SQL statement that I'm currently using works very well, as, for
instance, in a table representing the employee's various skills, query given
below.

USE candidate
UPDATE candtemp
SET origskills = y.skill
FROM
(
SELECT z.admref 'admref',
SUBSTRING (COALESCE (', ' + z.skill1, '') + COALESCE(', ' + z.skill2, '')
+ COALESCE(', ' + z.skill3, '') + COALESCE(', ' + z.skill4, ''), 3, 255)
'skill'
FROM
(SELECT x.admref 'admref', MIN(CASE WHEN x.position = 0 THEN x.skills END)
'skill1',
MIN(CASE WHEN x.position = 1 THEN x.skills END) 'skill2',
MIN(CASE WHEN x.position = 2 THEN x.skills END) 'skill3',
MIN(CASE WHEN x.position = 3 THEN x.skills END) 'skill4'
FROM
(SELECT c.admref, s1.skills,
(SELECT COUNT(*)
FROM F01Skills s2
WHERE s2.admref = c.admref
AND (s2.admref > s1.admref
OR (s2.admref = s1.admref
AND s2.skills < s1.skills))) AS position
FROM F01Skills s1
INNER JOIN F01Candidate c ON c.admref = s1.admref
WHERE c.admref <= s1.admref) AS x
INNER JOIN F01Candidate c ON (c.admref = x.admref)
INNER JOIN candtemp p ON (p.admref = c.admref)
WHERE x.position < 4
AND x.skills IS NOT NULL
GROUP BY x.admref) AS z
) AS y
WHERE candidate.dbo.candtemp.[admref] = y.admref
GO

However, I'm trying to retrieve the division worked for on the assignment
end date (something like SELECT company FROM timesheets WHERE wedate =
MAX(wedate)). Unlike the skills statement given above, where each employee
can have multiple skills (records) recorded in a single field called
"skills", the problem that arises in the timesheet example is that an
employee can work for a single division for many consecutive weeks.

Please help! (Some records given below)

create table timesheets
(
EmpName nvarchar(255) not null,
EmpNo int not null,
Division nvarchar(50) not null,
WeekEndDt datetime not null)
)

insert timesheets values ('Joe Soap', 12345, 'Banking', Dec
14, 2002)
insert timesheets values ('Joe Soap', 12345, 'Banking', Dec
21, 2002)
insert timesheets values ('Joe Soap', 12345, 'Banking', Dec
28, 2002)
insert timesheets values ('Joe Soap', 12345, 'Banking', Jan 1,
2003)
insert timesheets values ('Joe Soap', 12345, 'Acting', Apr
17, 2004)
insert timesheets values ('Joe Soap', 12345, 'Acting', Apr
24, 2004)
insert timesheets values ('Joe Soap', 12345, 'Acting', May
1, 2004)
insert timesheets values ('Joe Soap', 12345, 'Acting', May
8, 2004)
insert timesheets values ('Joe Soap', 12345, 'Acting', May
15, 2004)
insert timesheets values ('Joe Soap', 12345, 'Baking', Sep
12, 2005)
insert timesheets values ('Pete Soak', 34567, 'Bobbing', Feb 14,
1976)
insert timesheets values ('Ann Cope', 67890, 'Bragging', Sep 19,
1972)
insert timesheets values ('Ann Cope', 67890, 'Bragging', Sep 26,
1972)
insert timesheets values ('Ann Cope', 67890, 'Bragging', Oct 3,
1972)

Hi Tom
You're brilliant and a real lifesaver.
Best regards
Loane

[quoted text, click to view]

I knew that... ;-)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
Hi Tom
You're brilliant and a real lifesaver.
Best regards
Loane

[quoted text, click to view]