Select query

[nirapreet]

Hello all,


I am trying to generate a qyery based on a table.
My table has fields as below
FnCode ErrCode ErrDesc and effective date

The logic behind it is, The data is uploaded in the table on any date for diff fnCodes.
My data is like
FnCode ErrCode ErrDesc and effective date
aaa err2 adderr 29/12/2005
aaa err3 picerr 29/12/2005
aaa err2 adderr 01/01/2005
aaa err1 pqrerr 01/01/2005

bbb err1 invterr 30/12/2004
bbb err9 interr 30/12/2004
bbb err6 psterr 21/01/2005

The logic is on 29/12/2005 errors were uploaded for fncode aaa. And then on 01/01/2005. On 1/1/2005 the errors are err and err1. The err3 has been removed. This is because this error was fixed and is not uploaded.

Similarly for fncode bbb, on 30/12/04, the err1 and err9 was present. On 21/01/2005 data was uploadd again and the err1 and err9 is removed but this time err6 is a new error.
What I want from m query is that it should report the fixed errors .ie the erros which are not in the latest date but were in the previous one.

Result should be
FnCode ErrCode ErrDesc and effective date
aaa err3 picerr 29/12/2005

bbb err1 invterr 30/12/2004
bbb err9 interr 30/12/2004

Any solutions please..!!

Thanks in advance

 

[ByteMyzer]

You might try something along the lines of:

SELECT T1.*
FROM Table1 AS T1
WHERE T1.[effective date]=
    (SELECT MAX(T2.[effective date])
    FROM Table1 AS T2
    WHERE T2.FnCode=T1.FnCode AND T2.[effective date]<
        (SELECT MAX(T3.[effective date])
        FROM Table1 AS T3
        WHERE T3.FnCode=T1.FnCode;)
    ;)
AND T1.ErrCode NOT IN
    (SELECT T4.ErrCode
    FROM Table1 AS T4
    WHERE T4.FnCode=T1.FnCode AND T4.[effective date]=
        (SELECT MAX(T5.[effective date])
        FROM Table1 AS T5
        WHERE T5.FnCode=T1.FnCode;)
    ;)
;

See if this works for you.

 

[nirapreet]

It worked...

You are a gem..!!

It worked..!! Thanks for your help..!!

Nira