|
Subject:
|
Select two MAX record per each
|
|
Posted By:
|
veeruu
|
Post Date:
|
1/21/2006 12:13:55 AM
|
Hi!
I would be very happy if anybody could help me in the following.
table contains the following fields,
ConsultantID
ConsultantType
StartDate
ExpDate
UpdatedDate
UpdatedBy
UpdatedInfo
The tables allows same IDs(more than One) with diff. data with new UpdatedDate
I need a query to display .. MAX Two Reords based on Updated date for all IDs. this is my basic one.
and after getting the two records for each consultantID,
I need to display the secondMax record only if the defference between
the secondMax record (ExpDate) and FirstMAx record (Startdate) is less than 25.
Im working on this qurey from last 3 days ...
Plz.. help me to get the solution...
Thanks & Regards,
Veeru.
|
|
Reply By:
|
jbenson001
|
Reply Date:
|
1/21/2006 4:50:54 PM
|
show some sample data and your desired resutls. This will help us help you
Jim
|
|
Reply By:
|
veeruu
|
Reply Date:
|
1/22/2006 11:57:18 PM
|
Hi, This is My sample Data ...
Plz. help me....
CID CType StartDate ExpDate UpdatedDate
--- ---- -(MM/DD/YYYY)- ----------- ------------
1 B1 01/01/2004 03/03/2004 01/02/2004
1 OPT 03/06/2004 03/03/2005 03/10/2004
1 H1 03/01/2005 01/03/2008 03/04/2005
2 B1 10/01/2004 01/09/2005 10/04/2004
2 OPT 01/06/2005 01/03/2006 03/07/2006
2 H1 01/01/2006 01/03/2009 01/04/2006
I need a select query to display the result as follows..
1 H1 03/01/2005 01/03/2008 03/04/2005
2 OPT 10/06/2005 01/03/2006 10/07/2006
2 H1 01/01/2006 01/03/2009 01/04/2006
i.e. 2 MAX Updated records for each CID.
And display Second Record if diff. between
Second Exp.Date and Current Date is 25.
Thanks & Regards,
Veeru.
|
