I've been trying to do this the whole 2 weeks but seems like to no avail. 3 tables involved here. but am expecting the result would be distinct case no. for the same date & user. otherwise, it must return all.
SELECT DISTINCT A.CASENO,A.DATE,A.TIME,A.TRANNO,B.PCODE,C.PDESC,A.USER FROM tableA A RIGHT JOIN tableB B ON A.CASENO=B.CASENO RIGHT JOIN tableC C ON C.PCODE=B.PCODE WHERE A.DATE between 140124 and 140331
Result still have duplicates because not same time and tranno. See result on CASENO.= 08088040
A.CASENO A.DATE A.TIME A.TRANNO B.PCODE C.PDESC A.USER 08088040 140124 182516 321 TYQ PREPLAN1 ANTHONY 08088040 140124 182131 318 TYQ PREPLAN1 ANTHONY 36360569 140128 111056 431 CVB POSTT1 MARIA 36360569 140310 113221 433 CVB POSTT1 MARIA 37386911 140213 150240 230 M2P PLANAVG FELISE 37386911 140213 135220 223 M2P PLANAVG HECTOR 39222881 140128 94122 104 TYQ PREPLAN1 ELLA 40895213 140213 164409 104 CVB POSTT1 WINNIE 51311866 140124 103203 319 M2P PLANAVG BRATT
For same date, same user and same pcode and pdesc, caseno. 08088040 came out twice because of the time and tranno difference. here the max tranno would indicate the latest transaction done on the same case no. the max time also shows that the latest datetime action done for that particular case no. was on 24/01/2014 @ 18:25:16. For the same date and user, I just want the latest transaction details.
I've tried added the below script as the first condition but result was 0. It has also caused long delays before I could get the output.
WHERE A.TRANNO=(SELECT MAX(TRANNO) FROM tableA A where A.CASENO=B.CASENO)
FYI, the rests, eg. case no. 37386911 is considered unique because of different user, so I will want that to be returned. Meanwhile, case no.36360569 is also unique record because different date. although same user.
So, how should I achieve this please? Much of the examples I found was only showing for 1 table and 1 criteria to get distinct values.
The script should be those used for SQL DB2. I actually no background at all in SQL. Most of the query I've done earlier was based on trial and error but it was successful because of no complicated conditions like this.
Hopefully, somebody expert here can share the knowledge.