Function to combine 2 rows into 1

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)

Function to combine 2 rows into 1

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-03 : 10:53:15

Hi i need to look at more than 1 row in the table with a function i've
writen to check to see if the serial number is equal and if a datetime
is greater than the last datetime where the serial number is equal. I've got 2 indicators which looks at weather
a value has been 'Sent' @First_Call_ind and then 'canceled'@Cancel_First_Call_ind later.
I need a way of combinding these to indactors into one row, so i can sent the void to 1

- see my table layout below this is the septator '|'

------------------------------------------------------------------------------------------------
@First_Call_ind | @Cancel_First_Call_ind | Serial No| Date time | Void |
------------------------------------------------------------------------------------------------
1 | 0 | 123 | 11/12/05 13:29:32 | |
------------------------------------------------------------------------------------------------
0 | 1 | 123 | 11/12/05 13:33:32 | |
------------------------------------------------------------------------------------------------

Here is my function am working on, i know it not right but its to give you a idea of what am looking for.

CREATE FUNCTION dbo.udf_Voy_Void_ind(@SerialNum_1 char(15) ,@SerialNum_2 char(15), @Cancel_date_time datetime, @Field_date_time datetime, @First_Call_ind char(2),@Cancel_First_Call_ind char(2))
RETURNS tinyint
AS

BEGIN

DECLARE @Void_ind tinyint

If @SerialNum_1 = @SerialNum_2 and @Cancel_date_time >= @Field_date_time and @Cancel_First_Call_ind = '0' and @First_Call_ind = '1' or @Cancel_First_Call_ind = '1' and @First_Call_ind = '0'
Begin
Set @Void_ind = 1
End
Else

set @Void_ind = 0

RETURN @Void_ind
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-04 : 02:49:33

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-04 : 07:16:09

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 @Cancel_date_time >= @Field_date_time
Begin

If @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

End
Else
--Set the value to 0
Set @Void_ind = 0

RETURN @Void_ind
END

Call to function dbo.udf_Voy_Void_ind(SerialNum, SerialNum, StartTime, StartTime, ResolutionCode, ResolutionCode)

What i need to check is that the Serial numbers are they the same.
ResolutionCode for Field = 8 and ResolutionCode Cancel = 0 Also check the start time to compare them

I've given you 2 lines the first line has 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_ind = 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

CaseID|SerialNum|Serv_Prod_ind|Solve_ind|Part_ind|Field_ind|Serv_ind|Cancel_ind|First_Call_ind|Cancel_First_Call_ind|StartTime|Void
-----------------------------------------------------------------------------------------
UAT0006860 2230927017 1 0 0 1 0 0 1 2006/02/14 15:16 0
-----------------------------------------------------------------------------------------
UAT0006863 2230927017 1 0 0 0 0 1 0 1 2006/02/14 15:20 0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 06:07:19

You didnt give sample data
Refer this and post accordingly
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-05 : 06:17:50

quote:
Originally posted by madhivanan

You didnt give sample data
Refer this and post accordingly
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

Sorry, i though by what you meant as sample data was the table i was working with, the only sample data i have is the function which i've already posted, i can post a updated version, now sorry about the confusion.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-05 : 06:40:12

quote:
Originally posted by rookie_sql

quote:
Originally posted by madhivanan

You didnt give sample data
Refer this and post accordingly
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

Sorry, i though by what you meant as sample data was the table i was working with, the only sample data i have is the function which i've already posted, i can post a updated version, now sorry about the confusion.

I've updated my 2nd post now.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-06 : 04:31:10

You didnt give much details as specified in that link

Madhivanan

Failing to plan is Planning to fail

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-06 : 05:24:15

Ok here is the table am working with, you've got the function in the 2nd post.

SELECT [StartTime], [CaseID], [ResolutionCode], [Cancelled], [Serviceable],[Solve], [ResolutionText], [ServiceEvent], [CallType],
[SerialNum] FROM [tabRCATransfer]

The view

CREATE VIEW dbo.Test2
AS
SELECT TOP 100 PERCENT dbo.Test_V_RCA.CaseID, dbo.Test_V_RCA.Solve, dbo.Test_V_RCA.ServiceEvent, dbo.Test_V_RCA.CallType,
dbo.Test_V_RCA.Serviceable, dbo.Test_V_RCA.ChaseEvent, dbo.Test_V_RCA.CustUpdate, dbo.Test_V_RCA.Cancelled, dbo.Test_V_RCA.PartsOrder,
dbo.Test_V_RCA.CCPORComplaint, dbo.Test_V_RCA.CRUOrder, dbo.Test_V_RCA.ResolutionCode, dbo.Test_V_RCA.ResolutionText,
dbo.Test_V_RCA.Issue, dbo.tabEmployee.EmployeeID, dbo.tabEmployee.EmpLastName, dbo.tabEmployee.EmpFirstname,
dbo.tabProductsLU.KBName, dbo.tabProductCodeLU.CCOACode, dbo.tabProductCodeLU.ProductGroup, dbo.tabProductsLU.ProductSkillGroup,
dbo.tabProductCodeLU.ProductOrg, dbo.tabProductCodeLU.ProductSubOrg, dbo.tabProductCodeLU.ProductBusinessGrp,
dbo.Test_V_RCA.ProdID AS Expr1, dbo.Test_V_RCA.EmployeeID AS Expr2, dbo.Test_V_RCA.CountryCD3, dbo.Test_V_RCA.ContractDesc,
dbo.Test_V_RCA.ChaseNum, dbo.Test_V_RCA.RecallEvent, dbo.Test_V_RCA.SerialNum, dbo.Test_V_RCA.StartTime,
dbo.udf_Voy_cancel_ind(dbo.Test_V_RCA.Cancelled) AS Cancel_ind, dbo.udf_Voy_Field_ind(dbo.Test_V_RCA.ServiceEvent,
dbo.Test_V_RCA.Cancelled, dbo.Test_V_RCA.RecallEvent) AS Field_ind, dbo.udf_Voy_Part_ind(dbo.Test_V_RCA.ServiceEvent,
dbo.Test_V_RCA.Cancelled, dbo.Test_V_RCA.PartsOrder, dbo.Test_V_RCA.CRUOrder, dbo.Test_V_RCA.Solve) AS Part_ind,
dbo.udf_Voy_Solve_ind(dbo.Test_V_RCA.Solve, dbo.Test_V_RCA.ServiceEvent, dbo.Test_V_RCA.Cancelled, dbo.Test_V_RCA.PartsOrder,
dbo.Test_V_RCA.CRUOrder) AS Solve_ind, dbo.udf_Voy_Serv_Prod_ind(dbo.Test_V_RCA.Serviceable, dbo.Test_V_RCA.ContractDesc,
dbo.Test_V_RCA.RCAAvailable) AS Serv_Prod_ind
FROM dbo.Test_V_RCA INNER JOIN
dbo.tabEmployee ON dbo.Test_V_RCA.EmployeeID = dbo.tabEmployee.EmployeeID INNER JOIN
dbo.tabProductCodeLU INNER JOIN
dbo.tabProductsLU ON dbo.tabProductCodeLU.CCOACode = dbo.tabProductsLU.CCOACode ON
dbo.Test_V_RCA.ProdID COLLATE Latin1_General_CI_AS = dbo.tabProductsLU.ProdID AND
dbo.Test_V_RCA.CountryCD3 COLLATE Latin1_General_CI_AS = dbo.tabProductsLU.CountryCD3 LEFT OUTER JOIN
dbo.tabRCATransfer ON dbo.Test_V_RCA.CaseID = dbo.tabRCATransfer.CaseID AND
dbo.Test_V_RCA.SerialNum = dbo.tabRCATransfer.SerialNum
ORDER BY dbo.Test_V_RCA.StartTime

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources