Calculating void calls - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculating void calls

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-10 : 11:45:46

I've a table where it holds data for calls
I want to set a column to 1 or 0 depenting on a function call
Void = 0
Valid = 1

What i want my function to do is, calculate weather a call was placed and then cancelled,
if this is the case then its a void call, the initall call will be void and the cancel call
will be void

Example Void call

StartTime Cancelled = F and ServiceEvent = T
StartTime Cancelled = T and ServiceEvent = F

Here is the data in my table
Case Id | SERIAL NUM | STARTtIME | Code |Cancelled | Service Event
UAT0011393 | 2140249737 | 14/03/06 12:12 | 30 | F | f
UAT0011628 | 2140249737 | 15/03/06 10:06 | 1 | F | t
UAT0011638 | 2140249737 | 15/03/06 10:18 | 30 | F | f
UAT0011713 | 2140249737 | 15/03/06 11:43 | 30 | F | f
UAT0011742 | 2140249737 | 15/03/06 12:50 | 8 | T | f
UAT0011749 | 2140249737 | 15/03/06 13:09 | 8 | T | f
UAT0011758 | 2140249737 | 15/03/06 13:22 | 15 | F | f
UAT0011761 | 2140249737 | 15/03/06 13:23 | 8 | T | f
UAT0011762 | 2140249737 | 15/03/06 13:24 | 1 | F | t
UAT0011764 | 2140249737 | 15/03/06 13:26 | 30 | F | f
UAT0011765 | 2140249737 | 15/03/06 13:28 | 8 | T | f

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-10 : 11:51:35

Ur data and table naming r not that clear.

however, u may try

Select Case When
[StartTime Cancelled] = 'F' and ServiceEvent = 'T' or
[StartTime Cancelled] = 'T' and ServiceEvent = 'F' then
'Void'
Else
'Valid'
End
From Ur tbl

Srinika

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-11 : 05:25:34

Sorry regards the data but it get unformatted when i pates it into the fourm.
Am new to SQL so here is what am trying to do,

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, 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

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

Case Id | SERIAL NUM | 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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-11 : 05:33:38

Do you need a query to set the void column when you insert the records into the table OR you only want to update case id 1's void to 1 by insepecting the data in the table ?

This is what you want ?


update u
    set    void = 1
from   yourtable u
where  exists (select * from yourtable x where x.SERIAL_NUM = u.SERIAL_NUM and void = 1)

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-11 : 06:16:57

The data is already in the table so its a update by insepecting the data in the table.
Am not a core developer so you'll have to explain to me a bit more what the "where exist" means, as am dealing with 2 rows of data and i want both rows to be void. Inally my first row will be vaild untill a cancel call is in the table where both serial numbers are the same. I do think your code below is something like what i want, i need to test it.

update u
set void = 1
from yourtable u
where exists (select * from yourtable x where x.SERIAL_NUM = u.SERIAL_NUM and void = 1)

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-11 : 09:13:49

I've tested this and it does work but, it set everything to void (1), in some cases i may have valid calls were there is no cancel, case id 1 is valid till case id 3 so they both cancel each other out, Case id 4 and 5 are valid but the query is also setting them to void..

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources