Executing same SQL code twice

• Phil Parkin

  SSC Guru

  Points: 247183

  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
  

  ---

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  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: 247183

  More actions

  August 24, 2018 at 6:10 am

  #2003185

  Aha, my logic is flawed.

  ---

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  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: 77014

  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: 77014

  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: 110651

  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: 247183

  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.

  ---

• sgmunson

  SSC Guru

  Points: 110651

  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)

• NikosV

  SSCrazy

  Points: 2111

  More actions

  August 24, 2018 at 11:31 am

  #2003250

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

  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.

  I'm trying to picture how the code wilk behave because I'm not at a pc. It looks.pretty simple. 
  It's the OR confusing me. Will this solution return all records from both time periods into one table set?
  I'm getting the grouping part I think..

  I'll give it a try and post back Steve thanks.