Executing same SQL code twice

• NikosV

  SSCrazy

  Points: 2111

  More actions

  August 24, 2018 at 5:24 am

  #376766

  I have an SQL query which uses a DateFrom and DateTo variable. This query later will go into a stored procedure. DateFrom and DateTo will become the parameters. So if the user specifies DateFrom as 2018-01-01 and DateTo 2018-08-24 an X amount of results will be returned.

  Combined with those results, I would like to execute exactly the same code for the year before and show all the data in one table.

  Here is my current code.

  
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

  Underneath the temporary table I'm creating, I'm doing my SELECT with this in the WHERE.
  WHERE ClaimMain.OpeningRegistrationDate BETWEEN @DateFrom AND @DateTo

  I need to do the same SELECT exactly but replacing the above line with this.
  WHERE ClaimMain.OpeningRegistrationDate BETWEEN DATEADD(YEAR,-1,@DateFrom) AND DATEADD(YEAR,-1,@DateTo)

  My initial thought, was to take all the existing code, put it into a CTE called CTEDefinedYear, then after altering  the date condition, copy all that code into another CTE called CTEPreviousYear, Full Join the two CTE's  and select my columns as per normal. However this means that apart from the date condition, I am repeating ALL my code twice. I know repetition, depending on what we're doing I suppose is a bad thing and my query is already 117 lines long which means I'll be doubling all that easily.

  If I used the mentioned CTE approach, I obviously wouldn't copy the temp tables data again but I'd copy the rest of the code.

  So my questions here are:

  1. What would be the best approach to basically run the same code again but with a different date condition and return all the data from both date conditions? A table with 30 columns, 15 for this year, 15 for last year.
  2. Generally in situations like this, what should I be careful of and how should I think about this? Is the CTE approach suitable? What are some things to keep in mind here because it's the first time I'm doing this and it's probably the first of many.

  Thanks guys, happy to make any further clarifications .
  

• Phil Parkin

  SSC Guru

  Points: 246759

  More actions

  August 24, 2018 at 5:30 am

  #2003178

  Why not just modify the WHERE clause?
  WHERE ClaimMain.OpeningRegistrationDate BETWEEN DATEADD(YEAR,-1,@DateFrom) and @DateTo
  

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• Jonathan AC Roberts

  SSCoach

  Points: 18279

  More actions

  August 24, 2018 at 5:51 am

  #2003180

  Instead of making it a Stored Procedure you could make it a Table Valued Function. A drawback of this is that you cannot have a temporary table in a TVF, so you would have to make that a table variable. But then you can treat the function like a table so you could have:
  DECLARE @DateFrom DATE = '2018-01-01';
DECLARE @DateTo DATE = '2018-08-21';
DECLARE @DateFrom2 DATE = '2017-01-01';
DECLARE @DateTo2 DATE = '2017-08-21';
SELECT @DateFrom DateFrom,
               @DateTo DateTo,
               tvf.*
   FROM myTVF(@DateFrom,@DateTo) tvf
UNION ALL
SELECT @DateFrom DateFrom,
               @DateTo DateTo,
               tvf.* 
   FROM myTVF(@DateFrom2,@DateTo2) tvf

• NikosV

  SSCrazy

  Points: 2111

  More actions

  August 24, 2018 at 5:54 am

  #2003181

  Jonathan AC Roberts - Friday, August 24, 2018 5:51 AM

  Instead of making it a Stored Procedure you could make it a Table Valued Function. A drawback of this is that you cannot have a temporary table in a TVF, so you would have to make that a table variable. But then you can treat the function like a table so you could have:
  DECLARE @DateFrom DATE = '2018-01-01';
DECLARE @DateTo DATE = '2018-08-21';
DECLARE @DateFrom2 DATE = '2017-01-01';
DECLARE @DateTo2 DATE = '2017-08-21';
SELECT @DateFrom DateFrom,
               @DateTo DateTo,
               tvf.*
   FROM myTVF(@DateFrom,@DateTo) tvf
UNION ALL
SELECT @DateFrom DateFrom,
               @DateTo DateTo,
               tvf.* 
   FROM myTVF(@DateFrom2,@DateTo2) tvf

  Has to be a SP I'm afraid. We do financial reporting and we call stored procs.

• NikosV

  SSCrazy

  Points: 2111

  More actions

  August 24, 2018 at 5:57 am

  #2003182

  Phil Parkin - Friday, August 24, 2018 5:30 AM

  Why not just modify the WHERE clause?
  WHERE ClaimMain.OpeningRegistrationDate BETWEEN DATEADD(YEAR,-1,@DateFrom) and @DateTo
  

  I'm not sure what you mean. I need both dates and both result sets in one result.

• Phil Parkin

  SSC Guru

  Points: 246759

  More actions

  August 24, 2018 at 6:10 am

  #2003185

  Aha, my logic is flawed.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• Jonathan AC Roberts

  SSCoach

  Points: 18279

  More actions

  August 24, 2018 at 6:22 am

  #2003186

  NikosV - Friday, August 24, 2018 5:54 AM

  Has to be a SP I'm afraid. We do financial reporting and we call stored procs.

  Ok in the SP you can create another temporary table to store the dates:
  DECLARE @DateFrom DATE = '2018-01-01';
DECLARE @DateTo DATE = '2018-08-21';
;WITH CTE AS (
SELECT @DateFrom DateFrom, @DateTo DateTo
UNION ALL
SELECT DATEADD(yy,-1,@DateFrom), DATEADD(yy,-1,@DateTo))
SELECT *
INTO #Dates
FROM CTE
  Then in the main SQL statement you can join to this table
  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
INNER JOIN #Dates d ON ClaimMain.OpeningRegistrationDate BETWEEN d.DateFrom AND d.DateTo
WHERE GenPar.ParameterName = 'ClaimType'
AND GenPar.ParameterValue IN (SELECT ClaimDescription FROM #ClaimType)
AND PCover.#Row = 1
GROUP BY d.DateFrom,
ClaimDescription,
  GenPar.ParameterValue
ORDER BY ClaimDescription

  You also might want to include one of the dates in the SELECT results so you can see which year the row applies to.

• NikosV

  SSCrazy

  Points: 2111

  More actions

  August 24, 2018 at 6:28 am

  #2003187

  Jonathan AC Roberts - Friday, August 24, 2018 6:22 AM

  Ok in the SP you can create another temporary table to store the dates:
  DECLARE @DateFrom DATE = '2018-01-01';
DECLARE @DateTo DATE = '2018-08-21';
;WITH CTE AS (
SELECT @DateFrom DateFrom, @DateTo DateTo
UNION ALL
SELECT DATEADD(yy,-1,@DateFrom), DATEADD(yy,-1,@DateTo))
SELECT *
INTO #Dates
FROM CTE
  Then in the main SQL statement you can join to this table
  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
INNER JOIN #Dates d ON ClaimMain.OpeningRegistrationDate BETWEEN d.DateFrom AND d.DateTo
WHERE GenPar.ParameterName = 'ClaimType'
AND GenPar.ParameterValue IN (SELECT ClaimDescription FROM #ClaimType)
AND PCover.#Row = 1
GROUP BY d.DateFrom,
ClaimDescription,
  GenPar.ParameterValue
ORDER BY ClaimDescription

  You also might want to include one of the dates in the SELECT results so you can see which year the row applies to.

  Wow. Ok, I'll give it a bash on Monday and get back to you. Thanks Jonathan.

• drew.allen

  SSC Guru

  Points: 76950

  More actions

  August 24, 2018 at 7:47 am

  #2003196

  You posted the EXACT SAME QUESTION two weeks ago.

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• NikosV

  SSCrazy

  Points: 2111

  More actions

  August 24, 2018 at 7:51 am

  #2003197

  I totally forgot about that. It was for another report.

  Good memory Drew. That's why the CTEs sprung to mind. Since this is a duplicate what should I do? Can I merge the questions?
  Also, is there a way to find previous questions Ive asked?

• drew.allen

  SSC Guru

  Points: 76950

  More actions

  August 24, 2018 at 8:28 am

  #2003205

  NikosV - Friday, August 24, 2018 7:51 AM

  I totally forgot about that. It was for another report.

  Good memory Drew. That's why the CTEs sprung to mind. Since this is a duplicate what should I do? Can I merge the questions?
  Also, is there a way to find previous questions Ive asked?

  Clicking on someone's avatar (including your own) will take you to their profile.  One of the links on their profile is "Topics" which will show you every single thread that they have started.  There is another for "Replies".  This is how I found the link to your previous question.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• NikosV

  SSCrazy

  Points: 2111

  More actions

  August 24, 2018 at 8:32 am

  #2003208

  Thanks Drew. Found it.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  August 24, 2018 at 10:30 am

  #2003241

  Phil Parkin - Friday, August 24, 2018 5:30 AM

  Why not just modify the WHERE clause?
  WHERE ClaimMain.OpeningRegistrationDate BETWEEN DATEADD(YEAR,-1,@DateFrom) and @DateTo
  

  That would have the unfortunate effect of including data that doesn't belong unless you were already selecting an entire year, which is not the case.   The OP needs to select the same time frame from both this year AND last year.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Phil Parkin

  SSC Guru

  Points: 246759

  More actions

  August 24, 2018 at 10:35 am

  #2003242

  sgmunson - Friday, August 24, 2018 10:30 AM

  Phil Parkin - Friday, August 24, 2018 5:30 AM

  Why not just modify the WHERE clause?
  WHERE ClaimMain.OpeningRegistrationDate BETWEEN DATEADD(YEAR,-1,@DateFrom) and @DateTo
  

  That would have the unfortunate effect of including data that doesn't belong unless you were already selecting an entire year, which is not the case.   The OP needs to select the same time frame from both this year AND last year.

  See my subsequent post.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  August 24, 2018 at 10:53 am

  #2003246

  How about we add the YEAR to the GROUP BY ?   Here's some more formatted code for you:
  DECLARE @DateFrom AS date = '20180101',
        @DateTo AS date = '20180821';

  DECLARE @DateFromPriorYear AS date = DATEADD(year, -1, @DateFrom),
        @DateToPriorYear AS date = DATEADD(year, -1, @DateTo);

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

  CREATE TABLE #ClaimType (
    ClaimDescription varchar(255) PRIMARY KEY CLUSTERED
);
INSERT INTO #ClaimType
    (
    ClaimDescription
    )
SELECT ClaimDescription
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')
    ) AS Temp(ClaimDescription);

  SELECT
    YearGroup                    = YEAR(CM.OpeningRegistrationDate),
    ClaimType                    = GenPar.ParameterValue,
    Submitted                    = COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),
    ApprovedPaid                = COUNT(CASE WHEN CurrentStatus IN (30,40) 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 (30, 40) 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 (30, 40) 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 AS GenPar
    INNER JOIN Company.Schema.ClaimMain AS CM
        ON GenPar.ParameterId = CM.ClaimType
    INNER JOIN (
        SELECT
            ROW_NUMBER() OVER(PARTITION BY YEAR(CM.OpeningRegistrationDate), PC.PolicyNumber ORDER BY PC.PolicyNumber) AS [#Row],
            PC.PolicyNumber,
            PC.SumAssured
        FROM Company.Schema.PolicyCover AS PC
        WHERE    PC.ClosingStatus    = 10
            AND BasicCoverFlag        = 1
            AND SumAssured            <> 0
        ) AS PCover
            ON PCover.PolicyNumber = CM.PolicyNumber
WHERE    (
        CM.OpeningRegistrationDate    BETWEEN @DateFromPriorYear AND @DateToPriorYear
        OR
        CM.OpeningRegistrationDate    BETWEEN @DateFrom AND @DateTo
        )
    AND GenPar.ParameterName        = 'ClaimType'
    AND GenPar.ParameterValue        IN (SELECT ClaimDescription FROM #ClaimType)
    AND PCover.[#Row]                = 1
GROUP BY
    YEAR(CM.OpeningRegistrationDate),
    ClaimDescription,
    GenPar.ParameterValue
ORDER BY
    ClaimDescription;

  This won't duplicate your code.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)