SQL - Changing column values based on temporary table dataset

Question

SQL Server Mgt Studio 2012 is what I'm using. To sum it up im wanting to execute several commands at once. I'm a .NET developer so i think in terms of if and elses.

I have raw data in [TableData] like this:

ID | Code 1 | Code 2 | Type Code | From Date | Thru Date | Adj Code | Final
1  | 2001   | 9982   | 10        | 2015-01-13| 2015-01-13| 0        | Y
2  | 2001   | 9982   | 10        | 2015-01-13| 2015-01-13| 1        | N
3  | 2001   | 9982   | 10        | 2015-01-13| 2015-01-24| 2        | Y
4  | 2001   | 2653   | 40        | 2014-12-29| 2014-12-29| 0        | Y
5  | 2001   | 2653   | 40        | 2014-12-29| 2014-12-29| 1        | N

I inserted data from TableData into a temporary table called TempData where AdjCode = 1.

tmp table [TempData] data:

ID | Code 1 | Code 2 | Type Code | From Date | Thru Date | Adj Code 
1  | 2001   | 9982   | 10        | 2015-01-13| 2015-01-13| 1
2  | 2001   | 2653   | 40        | 2014-12-29| 2013-12-29| 1

I want to examine each row in TempData by the [Type Code].

If the [Type Code] is '10' then I want to do a new select on TableData where TempData's [Code 1], [Code 2], [Type Code], and [From Date] columns Match.

That should look like this :

ID | Code 1 | Code 2 | Type Code | From Date | Thru Date | Adj Code | Final
1  | 2001   | 9982   | 10        | 2015-01-13| 2015-01-13| 0        | Y
2  | 2001   | 9982   | 10        | 2015-01-13| 2015-01-24| 2        | Y

Then based off this recordset, If there is an [Adj Code] with '2' I want to update corresponding TableData's [Final] column 'Y'. Similarly, i woudl liek to update matching TableData's matching record with 'N' for [Final] column 'N' where [Adj Code] = 0.

If the [Type Code] is anything other than '10' (the other one with '40' in this case), I want to do the same process except add the [Thru Date] as well... looks like this: select on TableData where TempData's [Code 1], [Code 2], [Type Code], and [From Date] AND [Thru Date] columns Match.

That should look like this:

ID | Code 1 | Code 2 | Type Code | From Date | Thru Date | Adj Code | Final
1  | 2001   | 2653   | 40        | 2014-12-29| 2014-12-29| 0        | Y
2  | 2001   | 2653   | 40        | 2014-12-29| 2014-12-29| 1        | N

I hope this isnt too confusing... I'd be glad to provide more information as i need to complete this for a project at work asap. Thanks.

Please show your Sql. you are have to write store proc for this. good luck — Hiten004, May 19 '15 at 20:50
I solved this by using a nested cursors. http://stackoverflow.com/questions/469019/cursor-inside-cursor — tshoemake, May 20 '15 at 13:09

score 0 · Accepted Answer · answered Jun 02 '15 at 13:41

I used nested cursors following this article: stackoverflow.com/questions/469019/cursor-inside-cursor

While it is a temporary solution and works, the runtime is incredibly slow with cursors. It takes over an hour to complete my query (hits over 90k records).

SQL - Changing column values based on temporary table dataset

1 Answers1