Is it possible to repeat when clause in case statement based on number of variables?

Question

As a part of my work I need to create profiles of claims settlement. Here is a quick idea about my work.

We receive claims data. I need to create report of profiles of claims where we mention range of claim amount and corresponding number of claims and total reimbursement within the range. The example could be-

625 claims of $0 - $1000 with total reimbursement of $510k,
862 claims of $1001 - $5000 with total reimbursement of $3.01 million,
334 claims of $5001 - $10000 with total reimbursement of $2.6 million.

There is always some change in the band or even number of bands. So next time the same range could be requirement of 6 bands, instead of 3. I have automated almost all other part of report. But not able to do this. I could create a procedure where I could mention the number of bands required and the lower/upper bounds of the bands. But then it is not fully automated. My idea is that I mention at the beginning of the query, the number of bands and the bounds. After that I need not change any part of the query.

Below is my effort

DECLARE @Range1 varchar(10)
DECLARE @Range2 varchar(10)
DECLARE @Range3 varchar(10)

SET @Range1 = '0'
SET @Range2 = '1000'
SET @Range3 = '5000'

   SELECT
   CASE WHEN ROUND(clmamt, 0) between ' + @Range1 + ' and ' + @Range2 + ' THEN ''1. $' + @Range1 + ' - $' + @Range2 + '''
        WHEN ROUND(clmamt, 0) between ' + @Range2 + ' and ' + @Range3 + ' THEN ''2. $' + @Range2 + ' - $' + @Range3 + '''
        WHEN ROUND(clmamt, 0) > ' + @Range3 + ' THEN ''3. > $' + @Range3 + '''
        ELSE ''ERROR'' END AS 'Range',
   SUM(clmamt) 'Total Reimbursement', COUNT(l.CLMID ) as 'Total Claims'

        from
   TableA l join
        (
         select CLMID, SUM(clm) 'clmamt' from TableAB
         group by CLMID
        )lc on l.CLMID = lc.CLMID join 
   TableB pt on l.ACCNTID = pt.ACCNTID join
   TableC pn on pt.GROUPID = pn.GROUPID
   where pt.GROUPID in (1,2)
   Group by        CASE WHEN ROUND(clmamt, 0) between 0 and 1000 THEN '1. $0- $1000'
                        WHEN ROUND(clmamt, 0) between 1001 and 5000 THEN '2. $1001- $5000'
                        WHEN ROUND(clmamt, 0) > 5001 THEN '3. > $5000'
                        ELSE 'ERROR' END

If I could do something where I can add a variable to denote number of bands and range of each band. And automatically query adds the number of when clauses in CASE statement, that will be great.

score 1 · Accepted Answer · answered Jun 08 '18 at 11:52

Instead of proliferating hard coded CASE statements, I would suggest that you create a classification table, which you can then JOIN to based on the Claim Amount, and then Group the claims by each band, i.e.

CREATE TABLE ClaimBand
(
    Name NVARCHAR(20),
    MinAmount NUMERIC(10,2),
    MaxAmount NUMERIC(10,2),
);

INSERT INTO ClaimBand(Name, MinAmount, MaxAmount) VALUES
('Small Claims', 0, 1000),
('Medium Claims', 1000, 5000),
('Large Claims', 5000, 10000),
('Super Large Claims', 10000, 999999);

You can now use a data driven approach by joining Claims to the ClaimBand table to classify the claims based on the rows in the ClaimBand, rather than on code:

SELECT band.Name, band.MinAmount, band.MaxAmount, 
       COUNT(*) AS NumClaims, SUM(c.Amount) AS TotalClaimed
FROM Claim c
INNER JOIN ClaimBand band on c.Amount >= band.MinAmount and c.Amount < band.MaxAmount
GROUP BY band.Name, band.MinAmount, band.MaxAmount;

SqlFiddle example here

Notes

You'll need to be a bit careful about the edges of the classification table - in the example here, the lower bound is inclusive, but the upper bound is exclusive.
You could also for instance use NULL to indicate that there is no upper or lower band, and then adjust the query accordingly.
You'll need to check that your bands don't overlap eachother, otherwise the same claim will be classified in multiple bands.

Is it possible to repeat when clause in case statement based on number of variables?

1 Answers1