mburk1968
asked on
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
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]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thanks
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 ...