Dates

[Elamranii]

Hi again,
Sorry I forgot to include the table
I have this table where I am trying to compare ChgDt.
I want to see a record that is higher than the one on top of it.

example: the only one I want to see in the case below is srn3 because its ChgDt is higher than srno2 ChgDt.

mbrrecid srno ChgDt
00115005501 1 20041209
00115005501 2 20040218
00115005501 3 20041214
00115005501 4 20011205
00115005501 5 20010101
00115005501 6 19991219
00115005501 7 19960313
00115005501 8 19951210
00115005501 9 19920630
00115005501 10 19920403

srno1 ChgDt is higher than 2 (Skip)
srno2 ChgDt is lower than 3 (I want to see this record)
srno4 ChgDt is hihger than 5 (Skip)
etc....

Is there a formula I can use. Thanks in advance

 

[PHV]

Something like this ?
SELECT A.mbrrecid, A.srno, A.ChgDt, B.ChgDt
FROM tblMBR AS A INNER JOIN tblMBR AS B ON A.mbrrecid = B.mbrrecid AND A.srno = B.srno+1
WHERE A.ChgDt > B.ChgDt;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Elamranii]

Thanks for your quick reply, Here is what I got:

mbrrecid srno A.ChgDt B.ChgDt
00115005501 1 20041209 19920403
00115005501 1 20041209 19920630
00115005501 1 20041209 19951210
00115005501 1 20041209 19960313
00115005501 1 20041209 19991219
00115005501 1 20041209 20010101
00115005501 1 20041209 20011205
00115005501 1 20041209 20040218
00115005501 2 20040218 19920403
00115005501 2 20040218 19920630
00115005501 2 20040218 19951210
00115005501 2 20040218 19960313
00115005501 2 20040218 19991219
00115005501 2 20040218 20010101
00115005501 2 20040218 20011205
I only want to see one record.
Thanks

 

[PHV]

Could you please post the EXACT SQL CODE giving your result, as mine worked with your sample data.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Elamranii]

The 2 tables I used are called Sheet1 and 1. Here is the code I used

SELECT DISTINCT A.mbrrecid, A.srno, A.ChgDt, B.ChgDt
FROM Sheet1 AS A INNER JOIN 1 AS B ON A.mbrrecid = B.mbrrecid
WHERE (((A.ChgDt)>.[ChgDt]));

 

[PHV]

Where is coming the 2nd table from ?
Your SQL is different from mine:
SELECT A.mbrrecid, A.srno, A.ChgDt, B.ChgDt
FROM Sheet1 AS A INNER JOIN Sheet1 AS B ON A.mbrrecid = B.mbrrecid AND A.srno = B.srno+1
WHERE A.ChgDt > B.ChgDt

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Elamranii]

1 is a copy of table Sheet1. 1 and Sheet1 are the exact same tables.
When I used your code I got all those duplicate records.
I am still learning how to use SQL. Let me know what to change to make it work.
Thanks again

 

[PHV]

When you used WHICH code ?
I've tested mine with your sample data and it returns this:[tt]
mbrrecid srno A.ChgDt B.ChgDt
00115005501 3 20041214 20040218[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Elamranii]

I got it. This is the code I used:

SELECT A.mbrrecid, A.srno, A.ChgDt, B.ChgDt
FROM 1 AS A INNER JOIN 1 AS B ON A.mbrrecid = B.mbrrecid AND A.srno = B.srno+1
WHERE A.ChgDt > B.ChgDt;

And this the result:

mbrrecid srno A.ChgDt B.ChgDt
00115005501 3 20041214 20040218

I really appreciate your patience and your time.
Ismail