You can copy the below message onto a notepad the list below will become significant
Table 1
MAC_TYPE LEFT(serial_no,6) LOCATION T1_CNTR T2_CNTR_ACT T2_CNTR_RST T3_CNTR_ACT T3_CNTR_RST CT_ACT CT_RST LEFT(eng_visit,2) DATE_VISIT DUE_DATE
1 BMW-01 122121 AEDXB 200 2000 2000 200 232 32 23 NN 10/10/97 10/10/97
2 BMW-01 122121 AEDXB 200 2000 2000 200 2000 23 23 NN 10/10/97 10/10/97
3 BMW-01 122121 AEDXB 200 320 232 32 32 32 32 NN 10/10/97 10/10/97
4 BMW-01 122121 AEDXB 200 232 3232 3232 323 32323 3232 NN 10/10/97 10/10/97
5 BMW-01 122121 AEDXB 200 32323 3232 2332 3232 32323 3232 NN 10/10/97 10/10/97
6 BMW-01 122121 AEDXB 200 0 0 0 0 0 0 NN 10/10/97 10/10/97
7 BMW-01 122121 AEDXB 200 202002 2020 2020 2020 2020 202 NN 10/02/98 10/03/98
8 BMW-01 122121 AEDXB 7000 8000 9000 2500 32200 34500 2500 NN 12/10/98 18/12/98
9 BMW-01 122121 AEDXB 950 2020 2020 330 3030 2400 2500 NN 10/10/98 10/10/98
10 BMW-01 122121 AEDXB 10000 1000 95000 95000 30000 40404 3003 NN 10/10/98 12/12/98
11 BMW-01 122121 AEDXB 4500 23232 3232 2232 1212 1212 12121 NN 12/12/98 13/12/98
12 BMW-01 122121 AEDXB 3500 23232 12121 1212 121 12121 12121 NN 10/10/98 11/10/98
13 BMW-01 122121 AEDXB 5000 4500 49494 23232 2323 3232 3232 NN 10/10/98 11/12/98
14 BMW-01 122121 AEDXB 9500 9050 90901 9090 90 9090 909 NN 10/10/98 18/08/98
15 BMW-01 122121 AEDXB 10000 10000 101010 101010 101001 1010 1010 NN / / 21/12/21
16 BMW-01 122121 AEDXB 90900 9090 900 9090 990 9090 0 NN 10/10/98 11/10/98
17 BMW-01 122121 AEDXB 8900 89001 19818 19919 19191 191919 1121 NN / / / /
18 BMW-01 122121 AEDXB 432 32094 32293 3232832 3293829 3293829 3298329 NN 06/07/98 06/07/98
19 BMW-01 122121 AEDXB 3232 2121 2121 23121 2121 2121 2121 NN 06/07/98 06/07/98
20 BMW-01 122121 AEDXB 32321 1001 10101 101010 10101 101010 10101 NN 06/07/98 06/07/98
Table 2
Above table Indexed using Date Visit
MAC_TYPE LEFT(serial_no,6) LOCATION T1_CNTR T2_CNTR_ACT T2_CNTR_RST T3_CNTR_ACT T3_CNTR_RST CT_ACT CT_RST LEFT(eng_visit,2) DATE_VISIT DUE_DATE
1 BMW-01 122121 AEDXB 10000 10000 101010 101010 101001 1010 1010 NN / / 21/12/21
2 BMW-01 122121 AEDXB 8900 89001 19818 19919 19191 191919 1121 NN / / / /
3 BMW-01 122121 AEDXB 432 32094 32293 3232832 3293829 3293829 3298329 NN 06/07/98 06/07/98
4 BMW-01 122121 AEDXB 3232 2121 2121 23121 2121 2121 2121 NN 06/07/98 06/07/98
5 BMW-01 122121 AEDXB 32321 1001 10101 101010 10101 101010 10101 NN 06/07/98 06/07/98
6 BMW-01 122121 AEDXB 200 202002 2020 2020 2020 2020 202 NN 10/02/98 10/03/98
7 BMW-01 122121 AEDXB 200 2000 2000 200 232 32 23 NN 10/10/97 10/10/97
8 BMW-01 122121 AEDXB 200 2000 2000 200 2000 23 23 NN 10/10/97 10/10/97
9 BMW-01 122121 AEDXB 200 320 232 32 32 32 32 NN 10/10/97 10/10/97
10 BMW-01 122121 AEDXB 200 232 3232 3232 323 32323 3232 NN 10/10/97 10/10/97
11 BMW-01 122121 AEDXB 200 32323 3232 2332 3232 32323 3232 NN 10/10/97 10/10/97
12 BMW-01 122121 AEDXB 200 0 0 0 0 0 0 NN 10/10/97 10/10/97
13 BMW-01 122121 AEDXB 950 2020 2020 330 3030 2400 2500 NN 10/10/98 10/10/98
14 BMW-01 122121 AEDXB 10000 1000 95000 95000 30000 40404 3003 NN 10/10/98 12/12/98
15 BMW-01 122121 AEDXB 3500 23232 12121 1212 121 12121 12121 NN 10/10/98 11/10/98
16 BMW-01 122121 AEDXB 5000 4500 49494 23232 2323 3232 3232 NN 10/10/98 11/12/98
17 BMW-01 122121 AEDXB 9500 9050 90901 9090 90 9090 909 NN 10/10/98 18/08/98
18 BMW-01 122121 AEDXB 90900 9090 900 9090 990 9090 0 NN 10/10/98 11/10/98
19 BMW-01 122121 AEDXB 7000 8000 9000 2500 32200 34500 2500 NN 12/10/98 18/12/98
20 BMW-01 122121 AEDXB 4500 23232 3232 2232 1212 1212 12121 NN 12/12/98 13/12/98
Table1 & Table 2 are same. Table 1 No Indexes, Table 2 indexed on Date_visit
I have been trying to pickup the maximum(date_visit) i.e.. the last record in table2 or Record No. 11 in table. By
givin the below SQl Command its giving erroneous results.
select distinct max(tran_trans.date_visit) as last_visit,tran_trans.mac_type,tran_trans.serial_no, ;
tran_trans.location,;
tran_trans.t1_cntr,tran_trans.t2_cntr_act,tran_trans.t2_cntr_rst,;
tran_trans.t3_cntr_act,tran_trans.t3_cntr_rst,ct_act,ct_rst,eng_visit,due_date,status,;
action,spare_parts,sir_no from data\tran_trans;
where mac_type='BMW-01' and serial_no='122121';
order by last_visit,mac_type,serial_no,location,t1_cntr;
group by tran_trans.mac_type,tran_trans.serial_no,;
tran_trans.location;
into cursor query1
Please Help as to where I am growing wrong.
Regards
Ravi
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only