April 5, 2006 at 5:17 am
I would like to write a function that checks:
Weather serial numbers are they the same.
ResolutionCode for Field = 1 and ResolutionCode Cancel = 8 Also check the start time to compare them as the cancel call must be a greater or equal then the first call.
Exmaple : -
The first line has Serial number = 123 and ResolutionCode Field = 1 this is a valid call, datetime is 14/02/2006 15:16
Then I look at the second line and see it has the same serial number as the first, its ResolutionCode Cancel = 8, so now I know the the first call was made a but then it was cancelled, and it was cancelled at this dateime 2006/02/14 15:20
The cancel call must be a greater or equal then the first call in this case its 4 minutes after the first call.
What I'd like to get as a result is and indictor that says both rows cancel each other out, and this call is a void call. hence set void to 1 in both these cases
Here is what i've written so far in my function.
CREATE FUNCTION dbo.udf_Voy_Void_ind(@SerialNum_1 char(15) ,@SerialNum_2 char(15), @Cancel_date_time datetime, @Field_date_time datetime, @Field_Res_code char(2), @Cancel_Res_code char(2))
RETURNS tinyint
AS
BEGIN
DECLARE @Void_ind tinyint
-- check the SerialNum and make sure the date time for the field was less than the cancel date time
If @SerialNum_1 = @SerialNum_2 and (@Field_Res_code = '1' and @Cancel_Res_code = '8')
Begin
-- set the value to 1
Set @Void_ind = 1
End
Else
--Set the value to 0
Set @Void_ind = 0
RETURN @Void_ind
END
April 6, 2006 at 2:06 am
I was thinking, that seeing as all the data is in one table that if i create a view that holds all the data that either holds all the ResolutionCode for Field = 1 or ResolutionCode for cancel = 8
Then i can compare the serial number to see if they are the same..
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply