Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sequential numeric Expand / Collapse
Author
Message
Posted Friday, July 5, 2013 1:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 10:28 AM
Points: 39, Visits: 177
I have a need to update numeric values in a table column so that they are unique and sequencial, the order of which is determined by another column in the same table, and by data in a 2nd table. I am running into a problem when there is duplicate data, and I can't figure out how to get the numeric values to be unique.

For the data below, I want the seq_nbr column in TABLE1 to be in order 1,2,3,4 based on the order the codes are listed horizontally in TABLE2.

TABLE1
seq_no seq_nbr dx_code
E3CD8342-1294-4CBA-9201-D51C07E9FB0C 1 366.16
997312BA-8C90-4773-B0FC-1838C46A4728 3 370.03
5DC781A2-71BC-4148-9D56-DA95D3F8F081 4 362.52
E65354B3-F404-430B-8153-EDD7D1921431 4 362.52

TABLE2
dx_code1 dx_code2 dx_code3 dx_code4
366.16 362.52 370.03 362.52



CREATE TABLE Table1
(seq_no UNIQUEIDENTIFIER,seq_nbr INT,dx_code VARCHAR(6))

CREATE TABLE Table2
(dx_code1 VARCHAR(6),dx_code2 VARCHAR(6),dx_code3 VARCHAR(6),dx_code4 VARCHAR(6))


INSERT INTO Table1(seq_no,seq_nbr,dx_code)
SELECT NEWID(),'1','366.16'
GO
INSERT INTO Table1 (seq_no,seq_nbr,dx_code)
SELECT NEWID(),'3','370.03'
GO
INSERT INTO Table1 (seq_no,seq_nbr,dx_code)
SELECT NEWID(),'4','362.52'
GO
INSERT INTO Table1 (seq_no,seq_nbr,dx_code)
SELECT NEWID(),'4','362.52'
GO

INSERT INTO Table2 (dx_code1,dx_code2,dx_code3,dx_code4)
VALUES('366.16','362.52','370.03','362.52')
GO


Pivot TABLE2

CREATE TABLE #diag_codes(dx INT IDENTITY,sequence char(8),dx_code VARCHAR(6))

INSERT INTO #diag_codes (sequence,dx_code)

SELECT sequence,dx_code
FROM(SELECT dx_code1,dx_code2,dx_code3,dx_code4
FROM Table2 ) d
UNPIVOT
(dx_code FOR sequence IN (dx_code1,dx_code2,dx_code3,dx_code4)
)AS unpvt_assess

select * from #diag_codes

DROP TABLE #diag_codes

//Edit

Hit post too soon.

I have the syntax above for creating the 2 tables and adding data for the test scenario.

I tried some queries with the pivot of TABLE2 to get my numeric values updated, but was running into difficulty due to the duplicate dx_code values.

Post #1470869
Posted Sunday, July 7, 2013 6:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:53 PM
Points: 3,438, Visits: 5,390
I hesitate to say this but I don't think you've got enough information to solve this.

Any query that might appear to solve it would need to rely on the ordering of the rows in Table1, which SQL does not guarantee.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1471009
Posted Monday, July 8, 2013 12:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Try this:



IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable;

CREATE TABLE #TempTable
(seq_nbr INT IDENTITY(1,1), dx_code1 VARCHAR(6),dx_code2 VARCHAR(6),dx_code3 VARCHAR(6),dx_code4 VARCHAR(6))

INSERT INTO #TempTable (dx_code1,dx_code2,dx_code3,dx_code4)
VALUES('366.16','362.52','370.03','362.52')

--SELECT * FROM #TempTable

IF OBJECT_ID('tempdb..#diag_codes') IS NOT NULL
DROP TABLE #diag_codes;

CREATE TABLE #diag_codes(rownum int,seq_nbr int identity(1,1),sequence char(8),dx_code VARCHAR(6))

INSERT INTO #diag_codes (rownum,sequence,dx_code)
SELECT
ROW_NUMBER() OVER (PARTITION BY dx_code ORDER BY sequence) AS rownum,
sequence,
dx_code
FROM(SELECT seq_nbr, dx_code1,dx_code2,dx_code3,dx_code4
FROM #TempTable ) d
UNPIVOT
(dx_code FOR sequence IN (dx_code1,dx_code2,dx_code3,dx_code4)
)AS unpvt_assess

SELECT
sequence
,dx_code
FROM
#diag_codes as d
WHERE
rownum = 1
ORDER BY
sequence



 
Post #1471055
Posted Monday, July 8, 2013 11:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 10:28 AM
Points: 39, Visits: 177
My original post may not have been very clear as to what I would like for the end result.

Taking it a step farther to help illustrate the direction I am going, I did a simple join of Table1 and #diag_codes on the dx_code field.
SELECT * FROM Table1 t JOIN #diag_codes dc ON t.dx_code =dc.dx_code

Now that leaves me in a sense with two duplicate rows. The only difference being the seq_no column. I am trying to figure out if there is any logical way to remove the duplicates or prevent the duplicates from occurring, and then update the column Table1.seq_nbr with the value in the field #diag_codes.dx.

I have uploaded two attachments to help illustrate what I am trying to achieve. The "join with duplicates" shows the results of the above query, where I have crossed out the duplicates that I want removed.

The "desired result" attachment shows what the data in Table1 should look like after updating the seq_nbr column.



  Post Attachments 
join with duplicates.jpg (2 views, 74.62 KB)
desired result.jpg (4 views, 37.20 KB)
Post #1471304
Posted Monday, July 8, 2013 12:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
I have to second what Dwain already stated. You don't have any way to force the order here. Why is Row 2 correct and Row 3 not correct? How do you know what order those will appear? You don't because you have no order by, and you have nothing to use as an order by. For that matter how do you know if dx_code2 belongs to dx 2? Why can't it be dx_code4? There is nothing other than the perceived order of the unpivot to know this. If you had a normalized table for Table2 this would be not only possible, it would be simple.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1471322
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse