Calculate percent change between columns

Question

Post reply

Calculate percent change between columns

NikosV

SSCrazy

Points: 2111
More actions
August 24, 2018 at 2:25 am

#376765

I have this portion of code
SELECT GenPar.ParameterValue AS ClaimType, Submitted = COUNT(CASE CurrentStatus WHEN 10 THEN 1 END), ApprovedPaid = COUNT(CASE WHEN CurrentStatus IN (40,30) THEN 1 END), Declined = COUNT(CASE CurrentStatus WHEN 25 THEN 1 END), Pending = COUNT(CASE CurrentStatus WHEN 12 THEN 1 END), TotalSubmittedSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 10 THEN PCover.SumAssured END),0), TotalApprovedSumInsured = ISNULL(SUM(CASE WHEN ClaimMain.CurrentStatus IN (40,30) THEN PCover.SumAssured END),0), TotalRejectedSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 25 THEN PCover.SumAssured END),0), TotalPendingSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 12 THEN PCover.SumAssured END),0)
What I need to do, is calculate the percent change for certain columns. Like this:
COUNT(Submitted) - COUNT(ApprovedPaid) / COUNT(Submitted) * 100
The problem is, I can't refer to the columns using their aliases in order to do the above calculation.
Any ideas?
EDIT
I've done this
CASE WHEN COUNT(CASE CurrentStatus WHEN 10 THEN 1 END) <> 0 AND COUNT(CASE WHEN CurrentStatus IN (40,30) THEN 1 END) <> 0 THEN COUNT(CASE CurrentStatus WHEN 10 THEN 1 END) -COUNT(CASE WHEN CurrentStatus IN (40,30) THEN 1 END) /COUNT(CASE CurrentStatus WHEN 10 THEN 1 END) * 100 ELSE 0 END
I would like to know if there is anyway of shortening this. The results seem ok.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18339 More actions · Answer 1

Can you put the full SQL statement in with the FROM and GROUP BY?

NikosV SSCrazy Points: 2111 More actions · Answer 2

Yeap.

DECLARE @DateFrom DATE = '2018-01-01'; DECLARE @DateTo DATE = '2018-08-21';

IF OBJECT_ID('tempdb..#ClaimType') IS NOT NULL
DROP TABLE #ClaimType

CREATE TABLE #ClaimType
(
ClaimDescription VARCHAR(255) PRIMARY KEY CLUSTERED
);
INSERT INTO #ClaimType(ClaimDescription)
SELECT * FROM
(
VALUES ('2nd Annuitant Death - Living Spouse'),
   ('Accidental Death'),
   ('Accidental Death ( Traffic )'),
   ('Accidental Death (Common Carrier)'),
   ('Accidental Death (Common Carrier) Top-Up'),
   ('Accidental Death Basic Cover'),
   ('Accidental Death Top-Up'),
   ('Accidental Death Travel'),
   ('Accidental Death with PTD'),
   ('Accidental Death with PTD-DO NOT USE'),
   ('Accidental Economical Disability >= 67% due to Physiological Disability >= 25%'),
   ('Accidental Permanent Loss'),
   ('Accidental Physiological Disability >= 67%'),
   ('Accidental Total & Permanent Disability'),
   ('Co-insurance - First Death'),
   ('Critical Illness'),
   ('Death'),
   ('Death (annuity)'),
   ('Death (Old)'),
   ('Death Basic Cover'),
   ('Death Other Than Accident (CV Only)'),
   ('Death with Accidental Death'),
   ('Disability'),
   ('Disability due to Accident ( Traffic )'),
   ('Disability due to Disease'),
   ('Disability due to mental Health'),
   ('Disability without Benefit'),
   ('Disability WP 565'),
   ('Economical Disability >= 25%'),
   ('Economical Disability >= 25% due to Physiological Disability >= 25%'),
   ('Economical Disability >= 67%'),
   ('Economical Disability >= 67% due to Physiological Disability >= 25%'),
   ('Family Protector'),
   ('First Death'),
   ('First Death - Accidental'),
   ('Hospital Indemnity'),
   ('Income Cover'),
   ('Income Replacement'),
   ('LifeCare Critical Illness'),
   ('LTC Death'),
   ('LTC Event Claim'),
   ('Main Annuitant Death with Living Spouse'),
   ('Medical Expense Claim'),
   ('Medical Investigation Expense'),
   ('Non Life Payee Death'),
   ('Partial Disability'),
   ('Payor Death - WOP'),
   ('Payor Disability - WOP'),
   ('Physiological Disability >= 25%'),
   ('Physiological Disability >= 67%'),
   ('Refund'),
   ('Resiliation due to Death'),
   ('Spouse Rider Death'),
   ('Total & Permanent Disability'),
   ('Total Disability'),
   ('Waiver Benefit'),
   ('Waiver of Premium'),
   ('Waiver of Premiums')
)Temp(Col)

SELECT GenPar.ParameterValue AS ClaimType,
   Submitted = COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),
   ApprovedPaid = COUNT(CASE WHEN CurrentStatus IN (40,30) THEN 1 END),
   Rejected = COUNT(CASE CurrentStatus WHEN 25 THEN 1 END),
   Pending = COUNT(CASE CurrentStatus WHEN 12 THEN 1 END),
   TotalSubmittedSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 10 THEN PCover.SumAssured END),0),
   TotalApprovedSumInsured = ISNULL(SUM(CASE WHEN ClaimMain.CurrentStatus IN (40,30) THEN PCover.SumAssured END),0),
   TotalRejectedSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 25 THEN PCover.SumAssured END),0),
   TotalPendingSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 12 THEN PCover.SumAssured END),0),
   ApprovedVsSubmitted = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN 1 END)
        -COUNT(CASE WHEN CurrentStatus IN (40,30) THEN 1 END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),0)),0) AS VARCHAR) + '%',
   RejectedVsSubmitted = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN 1 END)
        -COUNT(CASE CurrentStatus WHEN 25 THEN 1 END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),0)),0) AS VARCHAR) + '%',
   PendingVsSubmitted = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN 1 END)
        -COUNT(CASE CurrentStatus WHEN 12 THEN 1 END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),0)),0) AS VARCHAR) + '%',
   ApprovedVsSubmittedSum = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END)
        -COUNT(CASE CurrentStatus WHEN 12 THEN PCover.SumAssured END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END),0)),0) AS VARCHAR) + '%',
   RejectedVsSubmittedSum = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END)
        -COUNT(CASE CurrentStatus WHEN 25 THEN PCover.SumAssured END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END),0)),0) AS VARCHAR) + '%',
   PendingVsSubmittedSum = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END)
        -COUNT(CASE CurrentStatus WHEN 12 THEN PCover.SumAssured END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END),0)),0) AS VARCHAR) + '%'

FROM Company.Schema.GeneralParameter GenPar
INNER JOIN Company.Schema.ClaimMain ON GenPar.ParameterId = ClaimMain.ClaimType
INNER JOIN (SELECT ROW_NUMBER()OVER(PARTITION BY PolicyNumber ORDER BY PolicyNumber)#Row,
        PolicyNumber,
        SumAssured
        FROM Company.Schema.PolicyCover
       WHERE ClosingStatus = 10 AND BasicCoverFlag = 1 AND SumAssured <> 0
      )PCover ON PCover.PolicyNumber = ClaimMain.PolicyNumber

WHERE ClaimMain.OpeningRegistrationDate BETWEEN @DateFrom AND @DateTo
AND GenPar.ParameterName = 'ClaimType'
AND GenPar.ParameterValue IN (SELECT ClaimDescription FROM #ClaimType)
AND PCover.#Row = 1
GROUP BY ClaimDescription,
GenPar.ParameterValue
ORDER BY ClaimDescription

Jonathan AC Roberts SSCoach Points: 18339 More actions · Answer 3

NikosV - Friday, August 24, 2018 4:08 AM
Yeap.
DECLARE @DateFrom DATE = '2018-01-01'; DECLARE @DateTo DATE = '2018-08-21';
IF OBJECT_ID('tempdb..#ClaimType') IS NOT NULL
DROP TABLE #ClaimType
CREATE TABLE #ClaimType
(
ClaimDescription VARCHAR(255) PRIMARY KEY CLUSTERED
);
INSERT INTO #ClaimType(ClaimDescription)
SELECT * FROM
(
VALUES ('2nd Annuitant Death - Living Spouse'),
   ('Accidental Death'),
   ('Accidental Death ( Traffic )'),
   ('Accidental Death (Common Carrier)'),
   ('Accidental Death (Common Carrier) Top-Up'),
   ('Accidental Death Basic Cover'),
   ('Accidental Death Top-Up'),
   ('Accidental Death Travel'),
   ('Accidental Death with PTD'),
   ('Accidental Death with PTD-DO NOT USE'),
   ('Accidental Economical Disability >= 67% due to Physiological Disability >= 25%'),
   ('Accidental Permanent Loss'),
   ('Accidental Physiological Disability >= 67%'),
   ('Accidental Total & Permanent Disability'),
   ('Co-insurance - First Death'),
   ('Critical Illness'),
   ('Death'),
   ('Death (annuity)'),
   ('Death (Old)'),
   ('Death Basic Cover'),
   ('Death Other Than Accident (CV Only)'),
   ('Death with Accidental Death'),
   ('Disability'),
   ('Disability due to Accident ( Traffic )'),
   ('Disability due to Disease'),
   ('Disability due to mental Health'),
   ('Disability without Benefit'),
   ('Disability WP 565'),
   ('Economical Disability >= 25%'),
   ('Economical Disability >= 25% due to Physiological Disability >= 25%'),
   ('Economical Disability >= 67%'),
   ('Economical Disability >= 67% due to Physiological Disability >= 25%'),
   ('Family Protector'),
   ('First Death'),
   ('First Death - Accidental'),
   ('Hospital Indemnity'),
   ('Income Cover'),
   ('Income Replacement'),
   ('LifeCare Critical Illness'),
   ('LTC Death'),
   ('LTC Event Claim'),
   ('Main Annuitant Death with Living Spouse'),
   ('Medical Expense Claim'),
   ('Medical Investigation Expense'),
   ('Non Life Payee Death'),
   ('Partial Disability'),
   ('Payor Death - WOP'),
   ('Payor Disability - WOP'),
   ('Physiological Disability >= 25%'),
   ('Physiological Disability >= 67%'),
   ('Refund'),
   ('Resiliation due to Death'),
   ('Spouse Rider Death'),
   ('Total & Permanent Disability'),
   ('Total Disability'),
   ('Waiver Benefit'),
   ('Waiver of Premium'),
   ('Waiver of Premiums')
)Temp(Col)
SELECT GenPar.ParameterValue AS ClaimType,
   Submitted = COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),
   ApprovedPaid = COUNT(CASE WHEN CurrentStatus IN (40,30) THEN 1 END),
   Rejected = COUNT(CASE CurrentStatus WHEN 25 THEN 1 END),
   Pending = COUNT(CASE CurrentStatus WHEN 12 THEN 1 END),
   TotalSubmittedSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 10 THEN PCover.SumAssured END),0),
   TotalApprovedSumInsured = ISNULL(SUM(CASE WHEN ClaimMain.CurrentStatus IN (40,30) THEN PCover.SumAssured END),0),
   TotalRejectedSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 25 THEN PCover.SumAssured END),0),
   TotalPendingSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 12 THEN PCover.SumAssured END),0),
   ApprovedVsSubmitted = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN 1 END)
        -COUNT(CASE WHEN CurrentStatus IN (40,30) THEN 1 END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),0)),0) AS VARCHAR) + '%',
   RejectedVsSubmitted = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN 1 END)
        -COUNT(CASE CurrentStatus WHEN 25 THEN 1 END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),0)),0) AS VARCHAR) + '%',
   PendingVsSubmitted = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN 1 END)
        -COUNT(CASE CurrentStatus WHEN 12 THEN 1 END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),0)),0) AS VARCHAR) + '%',
   ApprovedVsSubmittedSum = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END)
        -COUNT(CASE CurrentStatus WHEN 12 THEN PCover.SumAssured END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END),0)),0) AS VARCHAR) + '%',
   RejectedVsSubmittedSum = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END)
        -COUNT(CASE CurrentStatus WHEN 25 THEN PCover.SumAssured END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END),0)),0) AS VARCHAR) + '%',
   PendingVsSubmittedSum = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END)
        -COUNT(CASE CurrentStatus WHEN 12 THEN PCover.SumAssured END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END),0)),0) AS VARCHAR) + '%'
FROM Company.Schema.GeneralParameter GenPar
INNER JOIN Company.Schema.ClaimMain ON GenPar.ParameterId = ClaimMain.ClaimType
INNER JOIN (SELECT ROW_NUMBER()OVER(PARTITION BY PolicyNumber ORDER BY PolicyNumber)#Row,
        PolicyNumber,
        SumAssured
        FROM Company.Schema.PolicyCover
       WHERE ClosingStatus = 10 AND BasicCoverFlag = 1 AND SumAssured <> 0
      )PCover ON PCover.PolicyNumber = ClaimMain.PolicyNumber
WHERE ClaimMain.OpeningRegistrationDate BETWEEN @DateFrom AND @DateTo
AND GenPar.ParameterName = 'ClaimType'
AND GenPar.ParameterValue IN (SELECT ClaimDescription FROM #ClaimType)
AND PCover.#Row = 1
GROUP BY ClaimDescription,
    GenPar.ParameterValue
ORDER BY ClaimDescription

If the results are correct I'd leave it as it is. Because the items you want to shorten contain an aggregate function (COUNT) it's not easy to shorten them. To make them shorter you would have to make the rest of the SQL a lot longer.

NikosV SSCrazy Points: 2111 More actions · Answer 4

Jonathan AC Roberts - Friday, August 24, 2018 4:31 AM
NikosV - Friday, August 24, 2018 4:08 AM
Yeap.
DECLARE @DateFrom DATE = '2018-01-01'; DECLARE @DateTo DATE = '2018-08-21';
IF OBJECT_ID('tempdb..#ClaimType') IS NOT NULL
DROP TABLE #ClaimType
CREATE TABLE #ClaimType
(
ClaimDescription VARCHAR(255) PRIMARY KEY CLUSTERED
);
INSERT INTO #ClaimType(ClaimDescription)
SELECT * FROM
(
VALUES ('2nd Annuitant Death - Living Spouse'),
   ('Accidental Death'),
   ('Accidental Death ( Traffic )'),
   ('Accidental Death (Common Carrier)'),
   ('Accidental Death (Common Carrier) Top-Up'),
   ('Accidental Death Basic Cover'),
   ('Accidental Death Top-Up'),
   ('Accidental Death Travel'),
   ('Accidental Death with PTD'),
   ('Accidental Death with PTD-DO NOT USE'),
   ('Accidental Economical Disability >= 67% due to Physiological Disability >= 25%'),
   ('Accidental Permanent Loss'),
   ('Accidental Physiological Disability >= 67%'),
   ('Accidental Total & Permanent Disability'),
   ('Co-insurance - First Death'),
   ('Critical Illness'),
   ('Death'),
   ('Death (annuity)'),
   ('Death (Old)'),
   ('Death Basic Cover'),
   ('Death Other Than Accident (CV Only)'),
   ('Death with Accidental Death'),
   ('Disability'),
   ('Disability due to Accident ( Traffic )'),
   ('Disability due to Disease'),
   ('Disability due to mental Health'),
   ('Disability without Benefit'),
   ('Disability WP 565'),
   ('Economical Disability >= 25%'),
   ('Economical Disability >= 25% due to Physiological Disability >= 25%'),
   ('Economical Disability >= 67%'),
   ('Economical Disability >= 67% due to Physiological Disability >= 25%'),
   ('Family Protector'),
   ('First Death'),
   ('First Death - Accidental'),
   ('Hospital Indemnity'),
   ('Income Cover'),
   ('Income Replacement'),
   ('LifeCare Critical Illness'),
   ('LTC Death'),
   ('LTC Event Claim'),
   ('Main Annuitant Death with Living Spouse'),
   ('Medical Expense Claim'),
   ('Medical Investigation Expense'),
   ('Non Life Payee Death'),
   ('Partial Disability'),
   ('Payor Death - WOP'),
   ('Payor Disability - WOP'),
   ('Physiological Disability >= 25%'),
   ('Physiological Disability >= 67%'),
   ('Refund'),
   ('Resiliation due to Death'),
   ('Spouse Rider Death'),
   ('Total & Permanent Disability'),
   ('Total Disability'),
   ('Waiver Benefit'),
   ('Waiver of Premium'),
   ('Waiver of Premiums')
)Temp(Col)
SELECT GenPar.ParameterValue AS ClaimType,
   Submitted = COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),
   ApprovedPaid = COUNT(CASE WHEN CurrentStatus IN (40,30) THEN 1 END),
   Rejected = COUNT(CASE CurrentStatus WHEN 25 THEN 1 END),
   Pending = COUNT(CASE CurrentStatus WHEN 12 THEN 1 END),
   TotalSubmittedSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 10 THEN PCover.SumAssured END),0),
   TotalApprovedSumInsured = ISNULL(SUM(CASE WHEN ClaimMain.CurrentStatus IN (40,30) THEN PCover.SumAssured END),0),
   TotalRejectedSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 25 THEN PCover.SumAssured END),0),
   TotalPendingSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 12 THEN PCover.SumAssured END),0),
   ApprovedVsSubmitted = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN 1 END)
        -COUNT(CASE WHEN CurrentStatus IN (40,30) THEN 1 END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),0)),0) AS VARCHAR) + '%',
   RejectedVsSubmitted = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN 1 END)
        -COUNT(CASE CurrentStatus WHEN 25 THEN 1 END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),0)),0) AS VARCHAR) + '%',
   PendingVsSubmitted = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN 1 END)
        -COUNT(CASE CurrentStatus WHEN 12 THEN 1 END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),0)),0) AS VARCHAR) + '%',
   ApprovedVsSubmittedSum = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END)
        -COUNT(CASE CurrentStatus WHEN 12 THEN PCover.SumAssured END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END),0)),0) AS VARCHAR) + '%',
   RejectedVsSubmittedSum = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END)
        -COUNT(CASE CurrentStatus WHEN 25 THEN PCover.SumAssured END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END),0)),0) AS VARCHAR) + '%',
   PendingVsSubmittedSum = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END)
        -COUNT(CASE CurrentStatus WHEN 12 THEN PCover.SumAssured END)
        /NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END),0)),0) AS VARCHAR) + '%'
FROM Company.Schema.GeneralParameter GenPar
INNER JOIN Company.Schema.ClaimMain ON GenPar.ParameterId = ClaimMain.ClaimType
INNER JOIN (SELECT ROW_NUMBER()OVER(PARTITION BY PolicyNumber ORDER BY PolicyNumber)#Row,
        PolicyNumber,
        SumAssured
        FROM Company.Schema.PolicyCover
       WHERE ClosingStatus = 10 AND BasicCoverFlag = 1 AND SumAssured <> 0
      )PCover ON PCover.PolicyNumber = ClaimMain.PolicyNumber
WHERE ClaimMain.OpeningRegistrationDate BETWEEN @DateFrom AND @DateTo
AND GenPar.ParameterName = 'ClaimType'
AND GenPar.ParameterValue IN (SELECT ClaimDescription FROM #ClaimType)
AND PCover.#Row = 1
GROUP BY ClaimDescription,
    GenPar.ParameterValue
ORDER BY ClaimDescription
If the results are correct I'd leave it as it is. Because the items you want to shorten contain an aggregate function (COUNT) it's not easy to shorten them. To make them shorter you would have to make the rest of the SQL a lot longer.

Ok that's fine no problem. Thanks.

If I have another different question related to this code could I ask here or should I post another question?

Jonathan AC Roberts SSCoach Points: 18339 More actions · Answer 5

NikosV - Friday, August 24, 2018 4:42 AM
Ok that's fine no problem. Thanks.
If I have another different question related to this code could I ask here or should I post another question?

Just post another question

drew.allen SSC Guru Points: 76984 More actions · Answer 6

NikosV - Friday, August 24, 2018 2:25 AM
I have this portion of code
SELECT GenPar.ParameterValue AS ClaimType, Submitted = COUNT(CASE CurrentStatus WHEN 10 THEN 1 END), ApprovedPaid = COUNT(CASE WHEN CurrentStatus IN (40,30) THEN 1 END), Declined = COUNT(CASE CurrentStatus WHEN 25 THEN 1 END), Pending = COUNT(CASE CurrentStatus WHEN 12 THEN 1 END), TotalSubmittedSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 10 THEN PCover.SumAssured END),0), TotalApprovedSumInsured = ISNULL(SUM(CASE WHEN ClaimMain.CurrentStatus IN (40,30) THEN PCover.SumAssured END),0), TotalRejectedSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 25 THEN PCover.SumAssured END),0), TotalPendingSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 12 THEN PCover.SumAssured END),0)
What I need to do, is calculate the percent change for certain columns. Like this:
COUNT(Submitted) - COUNT(ApprovedPaid) / COUNT(Submitted) * 100
The problem is, I can't refer to the columns using their aliases in order to do the above calculation.
Any ideas?
EDIT
I've done this
CASE WHEN COUNT(CASE CurrentStatus WHEN 10 THEN 1 END) <> 0 AND COUNT(CASE WHEN CurrentStatus IN (40,30) THEN 1 END) <> 0 THEN COUNT(CASE CurrentStatus WHEN 10 THEN 1 END) -COUNT(CASE WHEN CurrentStatus IN (40,30) THEN 1 END) /COUNT(CASE CurrentStatus WHEN 10 THEN 1 END) * 100 ELSE 0 END
I would like to know if there is anyway of shortening this. The results seem ok.

The easiest way to get around this is to use a CTE. You do all of your aggregates and assign aliases in the CTE and then you can use those aliases in the main query as much as you want.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

andycadley SSCertifiable Points: 5296 More actions · Answer 7

Make the original query a derived table and then calculate the percentages from that, i.e. something like:

SELECT ClaimType, Submitted, ApprovedPaid, Declined, Pending, TotalSubmittedSumInsured, TotalApprovedSumInsured, TotalRejectedSumInsured, TotalPendingSumInsured, Submitted - ApprovedPaid / Submitted * 100 As PercentChange From ( ... original query goes here... ) X