Should I use an update cursor for this and if so how? 1

[ChrisRutherford]

Hi there,

I have a problem with populating a field in a table as there are a few records.

I have a table called Coverage. In there we have two fields, Policy_Number (varchar(20)) and Coverage_Number (varchar(10)). There could be up to four records per policy in this table. Currently the Policy_Number field is the only one populated, the Coverage_Number is set to Null.

Now what I need to do is the following...

Lets say I want to populate the coverage_number field with either 01, 02, 03 or 04 based on the policy number. I'm not sure how to do this. I start with a table looking like this...

Policy_Number Coverage_Number
123456 Null
123456 Null

... and I want something like this...

Policy_Number Coverage_Number
123456 01
123456 02
123456 03

In my table though there are many policies, some with just one entry and others with up to four entries. I can set the Coverage_Number field to '01' for those policies that have one entry easily enough using the following code...

update dbo.Coverage
set Coverage_Number = '01'
where Policy_Number in (select Policy_Number
from dbo.Coverage
group by Policy_Number
having count(*) = 1)

However any more than one entry per policy and I'm stuck. Any help would be much appreciated.

Kind regards,

Chris

 

[ChrisRutherford]

Oh, slight P.S. that may help.

The Coverage table also has a row called Product_Code (varchar(10)). This is unique for each Policy_Number, Coverage_Number combination. For example...

Policy_Number Coverage_Number, Product_Code
123456 01 001
123456 02 001F
A11236 01 001
B35233 01 001B
B35233 02 UID
B23211 03 001F

However this is another example of the desired result, currently the Coverage_Number field is null.

Kind Regards,

Chris

 

[gmmastros]

What you are asking is possible. I'll show you how. Unfortunately, it is not easy to explain this, so I will show you by example. First, the concept. I create a table variable with an identity column. Then, I will insert the data in to the table variable such that it is ordered by the policy number and then the product code.

To help demonststrate this, I will create a table variable that represents your sample data.

[green]-- Sample data[/green]
Declare @Temp Table(Policy_Number VarChar(20), Coverage_Number VarChar(2), Product_Code VarChar(10))

Insert Into @Temp Values('123456',NULL,'001')
Insert Into @Temp Values('123456',NULL,'001F')
Insert Into @Temp Values('A11236',NULL,'001')
Insert Into @Temp Values('B35233',NULL,'001B')
Insert Into @Temp Values('B35233',NULL,'UID')
Insert Into @Temp Values('B23211',NULL,'001F')

[green]-- Code begins here[/green]
Declare @Data 
table   (RowId Integer Identity(1,1), 
        Policy_Number VarChar(20), 
        Coverage_Number VarChar(2),
        Product_Code VarChar(10)
        )

Insert Into @Data(Policy_Number, Product_Code)
Select Policy_Number,
       Product_Code
From   @Temp
Order By Policy_Number, Product_Code

Select * From @Data

At this point, notice how the RowId column is incrementing and your policy numbers are in order. Now, we'll get the min RowId for each policy_number.

Select Min(RowId) As MinRowId, Policy_Number
From   @Data
Group By Policy_Number

The results look like this...
[tt][blue]
RowId Policy_Number Coverage_Number Product_Code
----------- -------------------- --------------- ------------
1 123456 NULL 001
2 123456 NULL 001F
3 A11236 NULL 001
4 B23211 NULL 001F
5 B35233 NULL 001B
6 B35233 NULL UID

(6 row(s) affected)

MinRowId Policy_Number
----------- --------------------
1 123456
3 A11236
4 B23211
5 B35233

(4 row(s) affected)
[/blue][/tt]

Now, notice that if we take the RowId, then subtract the MinRowId and Add 1, we will have our Coverage Number! Like this...

Select RowId - MinRowId + 1 As Coverage_Number,
       D.Policy_Number, 
       Product_Code
From   @Data D
       Inner Join (
         Select Min(RowId) As MinRowId, 
                Policy_Number
         From   @Data
         Group By Policy_Number
         ) As A
         On D.Policy_Number = A.Policy_Number

Which results in this...
[tt][blue]Coverage_Number Policy_Number Product_Code
--------------- -------------------- ------------
1 123456 001
2 123456 001F
1 A11236 001
1 B23211 001F
1 B35233 001B
2 B35233 UID
[/blue][/tt]

We're almost there. You want the coverage number to be a varchar(2) with zero padding. We also need to update the original table, so...

[green]-- Sample Data[/green]
Declare @Temp Table(Policy_Number VarChar(20), Coverage_Number VarChar(2), Product_Code VarChar(10))

Insert Into @Temp Values('123456',NULL,'001')
Insert Into @Temp Values('123456',NULL,'001F')
Insert Into @Temp Values('A11236',NULL,'001')
Insert Into @Temp Values('B35233',NULL,'001B')
Insert Into @Temp Values('B35233',NULL,'UID')
Insert Into @Temp Values('B23211',NULL,'001F')

[green]-- Query Starts Here![/green]
Declare @Data 
table   (RowId Integer Identity(1,1), 
        Policy_Number VarChar(20), 
        Coverage_Number VarChar(2),
        Product_Code VarChar(10)
        )

Insert Into @Data(Policy_Number, Product_Code)
Select Policy_Number,
       Product_Code
From   [!]@Temp[/!]
Order By Policy_Number, Product_Code

Update T
Set    T.Coverage_Number = B.Coverage_Number
From   [!]@Temp[/!] T
       Inner Join (
         Select Right('0' + Convert(VarChar(2), RowId - MinRowId + 1), 2) As Coverage_Number, 
                D.Policy_Number, 
                Product_Code
         From   @Data D
                Inner Join (
                  Select Min(RowId) As MinRowId, 
                         Policy_Number
                  From   @Data
                  Group By Policy_Number
                  ) As A
                  On D.Policy_Number = A.Policy_Number
         ) As B
         On  T.Policy_Number = B.Policy_Number
         And T.Product_Code = B.Product_Code

Select * From [!]@Temp[/!]

So, there you have it. A set based solution that creates your coverage numbers for you. In all of these examples, I used a table variable to represent your table. In your final query, you will want to replace @Temp with your actual table name. I hope this has helped. If you have any questions about the logic or syntax presented here, please ask.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ChrisRutherford]

Hi there,

Thanks for the post George, this worked first time and is very efficent! There are close on 700,000 records in this table which I was a little worried about however things are perfect. It takes about 2 minutes to update records so again thank you.

Appreciate the time you took to write this.

Kind regards,

Chris

 

[SQLSister]

Chris, I think Geogre deserves a star fo this one. Had you done this using a cursor as you were orginally considering it probably would have taken hours to run. George really gave you a terrific solution.

Questions about posting. See faq183-874

 

[ChrisRutherford]

Hi there,

Totally agree however I'm not sure how to do that. I notice there is a link to "Thank SQLSister", I'll try that.

Kind regards,

Chris

 

[ChrisRutherford]

Oops, almost gave you the star SQLSister, but I realised my almost mistake and gave the star to George.

Thanks again team, excellent solution.

Kind regards,

Chris