?
Solved

SQL 2005 Stored Procedure Case

Posted on 2011-09-23
3
Medium Priority
?
173 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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

741 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