Help with Query

[rookiesql]

Hi, I've data in a table were i need to set a indactor to either 1 or 0 depending on the following.

I want to set a void column to 1 or 0

What i have is a table that shows Cancelled = F and ServiceEvent = T i have initally set the void column on this row to 0 as its a vaild call, but what i want to catch is the case were a person ring back and say s/he wants to cancel the call, if this is the case then i've a new row of data where Cancelled = T and ServiceEvent = F, See case id 3, this row will be set to 1 as its void but i also want to update the other row were it was valid untill it was cancelled, the way i knnow it the same caller is by the serialnumber as they be the same, i may also have calls in the table that do not get cancelled, case 4 is a example of this. case 5 is vaild but then case 6 cancels out case 5 so both case id 5 and 6 are not void

Here is a example case id 1 is valild untill the case id 3 now both case id 1 and 3 are void both will be set to 1


Case Id SERIALNUM| STARTtIME | Code |Cancelled | Service Event |Void
1 |123 |15/03/06 13:24 | 1 | F | t | 0
2 |123 |15/03/06 13:26 | 30 | F | f | 0
3 |123 |15/03/06 13:28 | 8 | T | f | 1
4 |123 |15/03/06 13:24 | 1 | F | t | 0
5 |123 |15/03/06 13:24 | 1 | F | t | 0
6 |123 |15/03/06 13:28 | 8 | T | f | 1

P.S am not a SQL programmer am a reports developer using sql 2000

 

[rtmpxr]

I wrote a trigger which works on Firebird engine, but check the compatibility for your sql engine:

CREATE TRIGGER <trigger name> FOR <table>
ACTIVE BEFORE UPDATE POSITION 0
AS
declare variable x integer;
begin
/* Trigger text */
if (new.void=1) then begin
select first 1 id from <table> where serial=new.serial and cancel='T' and id<new.id order by id desc into :x;
if (x is null) then x=0;
update <table> set void=1 where serial=new.serial and cancel='F' and serv='T' and id between x+1) and (new.id-1);
end
end

I used x to put here the ID from the next cancelled call, calls which were ordered DESCENTIVE. That does SELECT and I used FIRST to return just the first record which met the required conditions. If I will modify the void for the id=6 then the select will give me id=3, which will be stored in x. If I will do the same thing for the id=3 the SELECT will return NULL (no record meet the required conditions). This is why I test the x in the next statement.
The UPDATE will do the rest.

HTH