Show record based on this criteria

[Guest]

Appreciate if you can help me figure out a way to resolve this query.
I'm trying to query for customer maintenance records based on the criteria
below but I'm not sure how I can go about doing so:

BEGIN checking from the last record:

IF the "Support Flag Field" = "NO" AND "Start Date field" <= Now() - 60days
THEN shows only the the previous record before the last record
ELSE shows the last record


No. Support Flag Start Date Customer
1 Yes 28/02/2002 A
2 Yes 28/02/2003 A
3 Yes 28/02/2004 A
4 No 28/02/2005 A
5 Yes 28/02/2002 B
6 Yes 28/02/2003 B
7 Yes 28/02/2004 B
8 Yes 28/02/2005 B


Base on above table, for customer "A", the query should pick record number 3.
For customer "B", the query should pick record number 8.

I hope the explanation above is informative. Thanks in advance

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe your criteria would be better stated like this:

"Select the latest StartDate per customer where the Support Flag is
Yes."

Perhaps this:

SELECT [No.], SupportFlag, StartDate, Customer
FROM table As T
WHERE StartDate = (SELECT Max(StartDate) FROM table
WHERE Customer = T.Customer
AND SupportFlag = "Yes")

This supposes that there is only one StartDate per Customer/SupportFlag.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkdi/oechKqOuFEgEQI8BgCcDN3yUaqtNi5kck2cxIsvavU2hAoAnj0O
0HbZACtM1p2QEL142+oSu1fe
=oAcM
-----END PGP SIGNATURE-----

 

[Guest]

Thank you MGFoster. Your example works.

Austen Lim

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe your criteria would be better stated like this:

"Select the latest StartDate per customer where the Support Flag is
Yes."

Perhaps this:

SELECT [No.], SupportFlag, StartDate, Customer
FROM table As T
WHERE StartDate = (SELECT Max(StartDate) FROM table
WHERE Customer = T.Customer
AND SupportFlag = "Yes")

This supposes that there is only one StartDate per Customer/SupportFlag.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkdi/oechKqOuFEgEQI8BgCcDN3yUaqtNi5kck2cxIsvavU2hAoAnj0O
0HbZACtM1p2QEL142+oSu1fe
=oAcM
-----END PGP SIGNATURE-----

Appreciate if you can help me figure out a way to resolve this query.
I'm trying to query for customer maintenance records based on the criteria
below but I'm not sure how I can go about doing so:

BEGIN checking from the last record:

IF the "Support Flag Field" = "NO" AND "Start Date field" <= Now() - 60days
THEN shows only the the previous record before the last record
ELSE shows the last record


No. Support Flag Start Date Customer
1 Yes 28/02/2002 A
2 Yes 28/02/2003 A
3 Yes 28/02/2004 A
4 No 28/02/2005 A
5 Yes 28/02/2002 B
6 Yes 28/02/2003 B
7 Yes 28/02/2004 B
8 Yes 28/02/2005 B


Base on above table, for customer "A", the query should pick record number 3.
For customer "B", the query should pick record number 8.

I hope the explanation above is informative. Thanks in advance