T-SQL Top X, All Other - Multiple Constraints

Question

I have a massive data pull, and I have four keys I want to be able to sort on in particular.

pk.Formula.SPEEDCODE
pk.Formula.THERAPEUTIC_CLASS
pk.RxFill.PATIENT_PRICE
pk.RxFill.SHIP_DATE

Essentially, for each THERAPEUTIC_CLASS, I want to be able to pull the Top X (where X will be defined separately for each THERAPEUTIC_CLASS) SPEEDCODES (which must start with an alphanumeric character) by Sum(PATIENT_PRICE) during a specific time period corresponding to the prior Monday-Sunday week (SHIP_DATE), and then I want to list all the rest of the elements of each THERAPEUTIC_CLASS as "All Other."

I thought I had it figured out earlier, but the pulls take 6 minutes a piece, and I don't want to wait that long. Help?

Sample code:

THERAPEUTIC_CLASS  SPEEDCODE  PATIENT_PRICE
Pain               M1         500.00
Pain               NULL       125.00
Pain                          85.00
Pain               M1         225.00
Pain               P3         600.00
Pain               M1         1000.00
Pain               P3         500.00
Pain               P5         600.00
Pain               NULL       85.00
Pain               P5         450.00
Derm               WART1      250.00
Derm               U2         125.00
Derm               NULL       225.00
Derm               WART1      500.00
Derm               U2         85.00
Derm                          50.00

Expected Output (sorted):

TxClass   Speedcode   Revenue
Pain      M1          1775.00
Pain      P3          1100.00
Pain      P5          1050.00
Pain      All Other   295.00
Derm      WART1       750.00
Derm      U2          210.00
Derm      All Other   275.00

I had semi-working code that was too slow, but I blew it up because it wasn't handling NULL/empty values properly. I only want the code to capture stuff that starts with an alphanumeric, and my code was capturing non-alphanumeric speedcodes. — Don Ford, Aug 28 '15 at 02:23
Hello Ford welcome to StackOverflow, next time try to provide a [**SqlFiddle**](http://sqlfiddle.com/#!15/5368b/6) so we can understand the problem better and give you an answer faster – Also please read [**How to ask**](http://stackoverflow.com/help/how-to-ask) and [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) — Juan Carlos Oropeza, Aug 28 '15 at 03:01
Can you add indices, indexed views and persisted calculated columns? — Jodrell, Aug 28 '15 at 07:41

score 0 · Answer 1 · answered Aug 28 '15 at 07:18

may be by seeing above question and comments exactly i don't know what the exact output you are looking for. My assumpted answer if it works for you fine .

 ;with CTE AS (
select 
THERAPEUTIC_CLASS,
    CASE WHEN speedcode IS NULL  THEN 'All Other'
        ELSE SpeedCode END AS SpeedCode,
    sum(PATIENT_PRICE) As PATIENT_PRICE,
        ROW_number()OVER(PARTITION BY THERAPEUTIC_CLASS 
            ORDER BY THERAPEUTIC_CLASS desc)RN 
    from table1
        GROUP BY THERAPEUTIC_CLASS,speedcode)

        Select THERAPEUTIC_CLASS,
    SpeedCode,
    PATIENT_PRICE 
    from CTE 
        ORDER BY THERAPEUTIC_CLASS DESC,RN DESC

T-SQL Top X, All Other - Multiple Constraints

1 Answers1