Solved

SQL 2005 Stored Procedure Case

Posted on 2011-09-23
3
169 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
  • 2
3 Comments
 
LVL 142

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 142

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Merige returns error code when updating 15 53
SQL Encryption question 2 55
SQL Server Degrading on Write 13 67
Delete from table 6 47
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

770 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