SQL 2005 Stored Procedure Case

I have the need to control the sort order on a report by Category and Type. I was thinking that I could modify the Case statement below to hard code the sort for Category and Type not unless there is a better way of performing this?

Sort for Category
Trial
Arbitrations
Mediations
Involuntary Dismissals
Settlements
Incident Only Payments
Early Offer Claims

Sort for Type
Category = Trial
Judgment for Plaintiff
Judgment for Defendant
Pending Appeals

Category = Arbitration
Award for Plaintiff
Award for Defendant

Category = Mediation
Mediation

Category = Involuntary Dismissals

Category = Settlements
Settlements

Category = Incident Only Payments
Incident Only Payments

Category = Early Offer Claims
Early Offer Claims

CASE WHEN CSC.DispositionCode = '5' THEN 'Trial'
                          WHEN CSC.DispositionCode = '6' THEN 'Trial'
                          WHEN CSC.DispositionCode = ' '
                               AND AppealFlag = 'Y' THEN 'Trial'
                          WHEN CSC.DispositionCode = '11' THEN 'Trial'
                          WHEN CSC.DispositionCode = '12' THEN 'Trial'
                          WHEN CSC.DispositionCode = '13' THEN 'Arbitration'
                          WHEN CSC.DispositionCode = '14' THEN 'Arbitration'
                          WHEN CSC.DispositionCode = '15' THEN 'Mediation'
                          WHEN CSC.DispositionCode = '18'
                          THEN 'Early Offer Claims'
                          WHEN CSC.DispositionCode = '4'
                          THEN 'Involuntary Dismissal'
                          WHEN CSC.DispositionCode = '3'
                               AND [Indem Paid] = '0.00'
                          THEN 'Voluntary Dismissal'
                          WHEN CSC.DispositionCode = '3'
                               AND [Indem Paid] > '0.00' THEN 'Settlements'
                          WHEN CSC.DispositionCode = 'E'
                               AND [Indem Paid] > '0.00'
                          THEN 'Incident Only Payment'
                        END AS [Category],                     
                        CASE WHEN CSC.DispositionCode = '5'
                             THEN 'Judgment for Plaintiff'
                             WHEN CSC.DispositionCode = '6'
                             THEN 'Judgment for Defendant'
                             WHEN CSC.DispositionCode = ' '
                                  AND AppealFlag = 'Y' THEN 'Pending Appeal'
                             WHEN CSC.DispositionCode = '11'
                             THEN 'For Plaintiff After Appeal'
                             WHEN CSC.DispositionCode = '12'
                             THEN 'For Defendant After Appeal'
                             WHEN CSC.DispositionCode = '13'
                             THEN 'Award for Plaintiff'
                             WHEN CSC.DispositionCode = '14'
                             THEN 'Award for Defense'
                             WHEN CSC.DispositionCode = '15' THEN 'Mediation'
                             WHEN CSC.DispositionCode = '18'
                             THEN 'Early Offer Claims'
                             WHEN CSC.DispositionCode = '4'
                             THEN 'Involuntary Dismissal'
                             WHEN CSC.DispositionCode = '3'
                                  AND [Indem Paid] = '0.00'
                             THEN 'Voluntary Dismissal'
                             WHEN CSC.DispositionCode = '3'
                                  AND [Indem Paid] > '0.00' THEN 'Settlements'
                             WHEN CSC.DispositionCode = 'E'
                                  AND [Indem Paid] > '0.00'
                             THEN 'Incident Only Payment'
                        END AS [Type]

Open in new window

mburk1968Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
well, reading again, I think I would make the 2 fields a computed field in the table (eventually using a function) ...
and then, have a table that lists the sort order by type/category, and join your table with that table and order by that new table's sort order value...
clear as mud, I presume ...

create table mySortTypeCategory ( type varchar(100), category varchar(100), sort_order varchar(100))
insert into mySortTypeCategory values ( 'Trial', 'Judgment for Plaintiff' , 'A1' )
insert into mySortTypeCategory values ( 'Trial', 'Judgment for Defendant', 'A2' )
etc -...

select t.*
  from yourtable t
  join mySortTypeCategory c
     on c.type = t.type
   and c.category = t.category
 order by c.sort_order 

Open in new window


hope this helps


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
looks ok, except that:
AND [Indem Paid] = '0.00'
should be:
AND [Indem Paid] = 0.00
presuming that the field Indem Paid is a numeric field. with the quotes, you are calling for trouble ...

0
 
mburk1968Author Commented:
Actually its clear, I was hoping that I didn't need to create a table but it looks like that is my best option.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.