Help with Query

rookiesql · Apr 11, 2006

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 · Apr 13, 2006

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

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Help with Query

rookiesql

Computer

Recommended for you

rtmpxr

Nuclear

Similar threads

Part and Inventory Search

Sponsor