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.

such order, many sort, wow

Long term we’ll look to move this stuff into a source system somewhere, but for now, she’s sorted.