Solved

SQL 2005 Stored Procedure Case

Posted on 2011-09-23
3
171 Views
Last Modified: 2012-08-14
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

0
Comment
Question by:mburk1968
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36586520
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 36586542
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
 

Author Comment

by:mburk1968
ID: 36586558
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question