Seeding an integer sort code into a Data Warehouse from a varchar
I found this a bit of a challenge this morning, and as it’s my first use of windowing functions in SQL Server I thought I’d share.
Our requirement was to add a sort code to our dimension tables for various levels. For some reason many of our business keys are varchars, however I really wanted to get sort codes as integers.
Here’s the code:
select type, dense_rank () over (order by type asc) as type_sort, ledger, dense_rank () over (order by ledger asc) as ledger_sort, customer_code, customer_name, dense_rank () over (order by coalesce(try_cast(customer_code as int), 99999999) asc) as customer_sort from DWH.common.d_customer
This generates three distinct sort keys, one for each level. Quite nifty I reckon.
Long term we’ll look to move this stuff into a source system somewhere, but for now, she’s sorted.
0 Comments
You can be the first one to leave a comment.