Calculate percent change between columns

  • 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.

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

  • 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

  • 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.

  • 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?

  • 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

  • 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

  • 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

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

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