Calculating void calls
-
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 |Void
UAT0011393 | 2140249737 | 14/03/06 12:12 | 30 | F | f | 1
UAT0011628 | 2140249737 | 15/03/06 10:06 | 1 | F | t | 0
UAT0011638 | 2140249737 | 15/03/06 10:18 | 30 | F | f | 1
UAT0011713 | 2140249737 | 15/03/06 11:43 | 30 | F | f | 1
UAT0011742 | 2140249737 | 15/03/06 12:50 | 8 | T | f | 0
UAT0011749 | 2140249737 | 15/03/06 13:09 | 8 | T | f | 0
UAT0011758 | 2140249737 | 15/03/06 13:22 | 15 | F | f | 1
UAT0011761 | 2140249737 | 15/03/06 13:23 | 8 | T | f | 0
UAT0011762 | 2140249737 | 15/03/06 13:24 | 1 | F | t | 0
UAT0011764 | 2140249737 | 15/03/06 13:26 | 30 | F | f | 1
UAT0011765 | 2140249737 | 15/03/06 13:28 | 8 | T | f | 0
-
Which column are you trying to set
Update Mytable
set Void = case when Cancelled = F and ServiceEvent = T Then 1
when Cancelled = T and ServiceEvent = F then 1 Else 0
End
-
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
-
What is it that distiguishes CaseID 2 from 1 and 3? As in your first example of data, what connects ANY 2 calls (CaseID's) if multiple calls meet the same conditions?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The system create a new case id for ever call so i cannot relate the calls by case id, the only wat i can relate the calls is by the serialnumber, here is a sample data, where i've 5 calls for the same serialnumber, case id 1 is a valid the way i know its vaild is the Cancelled = F and Service Event T l, case id 2, is valid and always will be , case id 3 is the cancel, the way i know its cancel is the Cancelled = T and Service Event F, now if you look back at case id 1 you see it the same serial number as case if 3 so i need to cancel both calls the way i do this is i set a void column to 0.
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
-
In that case... this works... there may be a correlated sub-query solution but I didn't use one... obviously, you would replace #yourtable with your real table name after you're done testing...
--===== Create a temp table to hold experimental data in (not part of final)
CREATE TABLE #yourtable
(
CaseId VARCHAR(20),
SERIALNUM VARCHAR(20),
STARTTIME DATETIME,
Code INT,
Cancelled CHAR(1),
ServiceEvent CHAR(1),
Void INT DEFAULT 1
)
--===== Populate the experimental table with test data from the first post
-- (also not part of final)
INSERT INTO #yourtable
(CaseId,SERIALNUM,STARTtIME,Code,Cancelled,ServiceEvent)
SELECT 'UAT0011393','2140249737',CONVERT(DATETIME,'14/03/06',4)+'12:12','30','F','f' UNION ALL
SELECT 'UAT0011628','2140249737',CONVERT(DATETIME,'15/03/06',4)+'10:06','1','F','t' UNION ALL
SELECT 'UAT0011638','2140249737',CONVERT(DATETIME,'15/03/06',4)+'10:18','30','F','f' UNION ALL
SELECT 'UAT0011713','2140249737',CONVERT(DATETIME,'15/03/06',4)+'11:43','30','F','f' UNION ALL
SELECT 'UAT0011742','2140249737',CONVERT(DATETIME,'15/03/06',4)+'12:50','8','T','f' UNION ALL
SELECT 'UAT0011749','2140249737',CONVERT(DATETIME,'15/03/06',4)+'13:09','8','T','f' UNION ALL
SELECT 'UAT0011758','2140249737',CONVERT(DATETIME,'15/03/06',4)+'13:22','15','F','f' UNION ALL
SELECT 'UAT0011761','2140249737',CONVERT(DATETIME,'15/03/06',4)+'13:23','8','T','f' UNION ALL
SELECT 'UAT0011762','2140249737',CONVERT(DATETIME,'15/03/06',4)+'13:24','1','F','t' UNION ALL
SELECT 'UAT0011764','2140249737',CONVERT(DATETIME,'15/03/06',4)+'13:26','30','F','f' UNION ALL
SELECT 'UAT0011765','2140249737',CONVERT(DATETIME,'15/03/06',4)+'13:28','8','T','f'
--===== Do the update according to the problem definition.
-- (This is the final...)
UPDATE #yourtable
SET VOID = 0
FROM #yourtable y,
(
SELECT d1.CaseID AS CaseID1,d2.CaseID AS CaseID2
FROM
(
SELECT CaseID,SerialNum,StartTime
FROM #yourtable
WHERE Cancelled = 'F'
AND ServiceEvent = 'T'
)d1
,
(
SELECT CaseID,SerialNum,StartTime
FROM #yourtable
WHERE Cancelled = 'T'
AND ServiceEvent = 'F'
)d2
WHERE d2.StartTime >= d1.StartTime
AND d2.SerialNum = d1.SerialNum
)d
WHERE y.CaseID = d.CaseID1
OR y.CaseID = d.CaseID2
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff, thanks for the very clear example.
I've just one more question,
I've added 3 more rows of data at the end of the insert step, these are valid calls that do not have cancel related to them, so they should should up as "1" in the void column, how would i go about adding this into the query so for vaild calls that do not have a cancel related to them are counted. I am going to put a time limit on calls being cancelled if they are > 2days they will be gross calls if they are <= 2 days they will be Net calls
Thanks
Francis,
--===== Populate the experimental table with test data from the first post
-- (also not part of final)
INSERT INTO #yourtable
(CaseId,SERIALNUM,STARTtIME,Code,Cancelled,ServiceEvent)
SELECT 'UAT0011393','2140249737',CONVERT(DATETIME,'14/03/06',4)+'12:12','30','F','f' UNION ALL
SELECT 'UAT0011628','2140249737',CONVERT(DATETIME,'15/03/06',4)+'10:06','1','F','t' UNION ALL
SELECT 'UAT0011638','2140249737',CONVERT(DATETIME,'15/03/06',4)+'10:18','30','F','f' UNION ALL
SELECT 'UAT0011713','2140249737',CONVERT(DATETIME,'15/03/06',4)+'11:43','30','F','f' UNION ALL
SELECT 'UAT0011742','2140249737',CONVERT(DATETIME,'15/03/06',4)+'12:50','8','T','f' UNION ALL
SELECT 'UAT0011749','2140249737',CONVERT(DATETIME,'15/03/06',4)+'13:09','8','T','f' UNION ALL
SELECT 'UAT0011758','2140249737',CONVERT(DATETIME,'15/03/06',4)+'13:22','15','F','f' UNION ALL
SELECT 'UAT0011761','2140249737',CONVERT(DATETIME,'15/03/06',4)+'13:23','8','T','f' UNION ALL
SELECT 'UAT0011762','2140249737',CONVERT(DATETIME,'15/03/06',4)+'13:24','1','F','t' UNION ALL
SELECT 'UAT0011764','2140249737',CONVERT(DATETIME,'15/03/06',4)+'13:26','30','F','f' UNION ALL
SELECT 'UAT0011765','2140249737',CONVERT(DATETIME,'15/03/06',4)+'13:28','8','T','f' UNION ALL
SELECT 'UAT0011766','2140249737',CONVERT(DATETIME,'16/03/06',4)+'13:28','1','T','f' UNION ALL
SELECT 'UAT0011767','2140249737',CONVERT(DATETIME,'17/03/06',4)+'13:28','1','T','f'
-
I only count 2 extra rows but that may be due to a simple lack of coffee...
Anyway... I think this might be what you are looking for so far as the 2 day limit... I highlighted the change I made...
--===== Do the update according to the problem definition.
-- (This is the final...)
UPDATE #yourtable
SET VOID = 0
FROM #yourtable y,
(
SELECT d1.CaseID AS CaseID1,d2.CaseID AS CaseID2
FROM
(
SELECT CaseID,SerialNum,StartTime
FROM #yourtable
WHERE Cancelled = 'F'
AND ServiceEvent = 'T'
)d1
,
(
SELECT CaseID,SerialNum,StartTime
FROM #yourtable
WHERE Cancelled = 'T'
AND ServiceEvent = 'F'
)d2
WHERE d2.StartTime >= d1.StartTime
AND d2.SerialNum = d1.SerialNum
AND d2.StartTime - d1.StartTime <=2
)d
WHERE y.CaseID = d.CaseID1
OR y.CaseID = d.CaseID2
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff your knowledge is priceless thanks, i will go through the code to understand it in detail now.
-
Thank you for the very nice compliment... I only hope it does what you need
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff, am back again for your help i've been given new requierement, for this report am workong on and i've now a better understanding of what i need, regards the code you sent me, it sets all the cancel calls to 0 and the field calls to 0 were their was a field call made and then it was cancelled, but the problem i now have is that, what if i've a call the was sent to the field and then a second call that was sent to the field on the same day or <= 2 days my rule of cancel say set the cancel and the fiedd = 0 were it was <= 2days this mean it will cancel both calls where i only need to cancel the lasted or the last feild call were it has the same serial number as my cancelled call.
Example
1 2 3
Feild Field Cancel
only field 2 and cancel 3 should be invalid not field 1.
-
Sorry for the delay
... did you figure it out or do you still need some help on this...--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff, am still working on it, i will post a update in the next few days.
Thanks
Francis
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply