get latest record from transaction tbl

[Pallavijyo]

i have 2 tables TaskMaster and TaskDetails....
Taskdetails has multiple records for a particular task....

I need to select all the records from taskmaster and corresponding record which is the latest from taskdetails....
for eg if TaskMaster has following records

TaskNumber SysCode TaskDescription DateRecieved ClientContact
2456 HA Task4 4/20/2005 Doreen
4057 HA Task1 3/3/2002 Steve

and TaskDetails has


TaskNumber CurrentDate Efforts Per Comments
Spent Completed
2456 1/1/2004 6 56 On-Hold Coding try1
4057 1/1/2003 56 56 On-Hold Analys
4057 1/1/2004 34 34 Active Coding not
4057 1/1/2005 45 45 Active Analysi
4057 1/1/2006 67 67 Active Coding try5
2456 5/20/2005 8 50 Active Coding trial
4057 5/20/2005 55 45 Completed

I need only the latest record for task num 4057 and 2456...i.e Max(CurrentDate) plus all the columns from TaskMaster also

[MAK]

create table taskmaster
(TaskNumber int, SysCode varchar(2),TaskDescription varchar(10),DateRecieved datetime,ClientContact varchar(100))

insert into taskmaster select 2456, 'HA', 'Task4', '4/20/2005', 'Doreen'
insert into taskmaster select 4057, 'HA', 'Task1', '3/3/2002', 'Steve'

create table taskdetails
(TaskNumber int, CurrentDate datetime, Efforts int,Per int,Comments varchar(100))
insert into taskdetails select 2456, '1/1/2004', 6 ,56, 'On-Hold Coding try1'
insert into taskdetails select 4057, '1/1/2003', 56 ,56, 'On-Hold Analys'
insert into taskdetails select 4057, '1/1/2004', 34 ,34, 'Active Coding not '
insert into taskdetails select 4057, '1/1/2005', 45, 45, 'Active Analysi'
insert into taskdetails select 4057, '1/1/2006',67 ,67, 'Active Coding try5'
insert into taskdetails select 2456, '5/20/2005', 8 ,50, 'Active Coding trial '
insert into taskdetails select 4057, '5/20/2005', 55, 45, 'Completed'


Select a.TaskNumber,
a.SysCode,
a.TaskDescription,
DateRecieved,
a.ClientContact,
B.date,
c.Efforts,
c.per,
c.comments from taskmaster a,
(select tasknumber,max(currentdate) as date from taskdetails
group by tasknumber) as b,
taskdetails c
where b.date=c.currentdate and b.tasknumber=c.tasknumber and a.tasknumber
=b.tasknumber