Executing same SQL code twice

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

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  August 24, 2018 at 12:11 pm

  #2003261

  NikosV - Friday, August 24, 2018 11:31 AM

  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.

  As to the OR clause, that allows the date to be in either range, and ALL the rows for both years end up in the final result set,
  with the addition of the YEAR of the date to the GROUP BY ending up lumping each years data into it's own row for each of
  the other grouping columns.

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

• NikosV

  SSCrazy

  Points: 2111

  More actions

  August 24, 2018 at 12:37 pm

  #2003268

  sgmunson - Friday, August 24, 2018 12:11 PM

  NikosV - Friday, August 24, 2018 11:31 AM

  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.

  As to the OR clause, that allows the date to be in either range, and ALL the rows for both years end up in the final result set,
  with the addition of the YEAR of the date to the GROUP BY ending up lumping each years data into it's own row for each of
  the other grouping columns.

  Nice. Makes sense. I'll have a go at it on Monday. 
  Thanks

• NikosV

  SSCrazy

  Points: 2111

  More actions

  August 26, 2018 at 10:47 pm

  #2003384

  Thing is here guys, I need to append the exact same columns onto the existing set, but for the prior year.

  From what I understand this takes all rows, and adds them to the existing set, as is, if there are rows for the prior year. right?

• Jonathan AC Roberts

  SSCoach

  Points: 18354

  More actions

  August 26, 2018 at 10:56 pm

  #2003385

  NikosV - Sunday, August 26, 2018 10:47 PM

  Thing is here guys, I need to append the exact same columns onto the existing set, but for the prior year. With these two suggested solutions, how would I go about selecting my additional (prior year) columns?

  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.DateToINNER 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,d.DateFrom,
ClaimDescription,
GenPar.ParameterValue
ORDER BY    d.DateFrom DESC, d.DateFrom DESC, ClaimDescription
  Just put an ORDER BY in the SQL.

• NikosV

  SSCrazy

  Points: 2111

  More actions

  August 26, 2018 at 11:01 pm

  #2003386

  Jonathan AC Roberts - Sunday, August 26, 2018 10:56 PM

  NikosV - Sunday, August 26, 2018 10:47 PM

  Thing is here guys, I need to append the exact same columns onto the existing set, but for the prior year. With these two suggested solutions, how would I go about selecting my additional (prior year) columns?

  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.DateToINNER 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,d.DateFrom,
ClaimDescription,
GenPar.ParameterValue
ORDER BY    d.DateFrom DESC, d.DateFrom DESC, ClaimDescription
  Just put an ORDER BY in the SQL.

  I'm not sure I understand.

  Right now, I have these columns.

  
 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) + '%'


  Those columns are for the defined year, I want to select the same columns again, for the prior year so I would have say instead of Submitted, I would have SubmittedPreviousYear.

• Jonathan AC Roberts

  SSCoach

  Points: 18354

  More actions

  August 26, 2018 at 11:16 pm

  #2003387

  NikosV - Sunday, August 26, 2018 11:01 PM

  Those columns are for the defined year, I want to select the same columns again, for the prior year so I would have say instead of Submitted, I would have SubmittedPreviousYear.

  I took it you wanted to select them like a UNION not adding additional columns to the result set, is that right?

• NikosV

  SSCrazy

  Points: 2111

  More actions

  August 26, 2018 at 11:30 pm

  #2003389

  Jonathan AC Roberts - Sunday, August 26, 2018 11:16 PM

  NikosV - Sunday, August 26, 2018 11:01 PM

  Those columns are for the defined year, I want to select the same columns again, for the prior year so I would have say instead of Submitted, I would have SubmittedPreviousYear.

  I took it you wanted to select them like a UNION not adding additional columns to the result set, is that right?

  Yes I just realised that. Sorry maybe I wasn't clear enough. Yes I'd like to append additional columns. (Client request). I'm thinking, since I need the same columns, I need to add them to my SELECT. If I add these columns to my SELECT, they need to have a different WHERE date condition. Can this condition go within the actual column declaration, like a multiple CASE scenario? If not, I'm thinking I will have to repeat myself but at the same time I don't want to be crucified by other devs later on 🙂

  This isn't correct, but something like this.
  
DECLARE @DateFromPriorYear AS DATE = DATEADD(YEAR, -1, @DateFrom);
DECLARE @DateToPriorYear AS DATE = DATEADD(YEAR, -1, @DateTo);

  SubmittedPreviousYear = COUNT(CASE CurrentStatus WHEN 10 THEN 1 AND ClaimMain.OpeningRegistrationDate
            BETWEEN @DateFromPriorYear AND @DateToPriorYear END)


• andycadley

  SSCertifiable

  Points: 5296

  More actions

  August 27, 2018 at 5:38 am

  #2003410

  Do something like this:

  With UnpivotRows As
  (
  Select
      … maths goes here...
  From …
  Cross Join
  (
      Select 0 As YearsAgo, @DateFrom as DateFrom, @DateTo As DateTo
      Union All Select 1, DateAdd(Year, -1, @DateFrom), @DateAdd(Year, -1, @DateTo)
  ) As YRS
  Where OpeningDate Between DateFrom And DateTo
  Group By YearsAgo ….
  )
  Select
  *
  From unpivotedrows A
  Join unpivotedrows B On A... = B....
  Where A.YearsAgo = 0 And B.YearsAgo = 1

• Jonathan AC Roberts

  SSCoach

  Points: 18354

  More actions

  August 27, 2018 at 7:00 am

  #2003416

  NikosV - Sunday, August 26, 2018 11:30 PM

  Jonathan AC Roberts - Sunday, August 26, 2018 11:16 PM

  I took it you wanted to select them like a UNION not adding additional columns to the result set, is that right?

  Yes I just realised that. Sorry maybe I wasn't clear enough. Yes I'd like to append additional columns. 

  So you want to append rows or append columns?

• NikosV

  SSCrazy

  Points: 2111

  More actions

  August 27, 2018 at 7:56 am

  #2003430

  Jonathan AC Roberts - Monday, August 27, 2018 7:00 AM

  NikosV - Sunday, August 26, 2018 11:30 PM

  Jonathan AC Roberts - Sunday, August 26, 2018 11:16 PM

  I took it you wanted to select them like a UNION not adding additional columns to the result set, is that right?

  Yes I just realised that. Sorry maybe I wasn't clear enough. Yes I'd like to append additional columns. 

  So you want to append rows or append columns?

  Hi Jonathan. Columns please. Thr current code shows chosen year, the next set of same columns will show previous year.

• Jonathan AC Roberts

  SSCoach

  Points: 18354

  More actions

  August 27, 2018 at 9:06 am

  #2003449

  NikosV - Monday, August 27, 2018 7:56 AM

  Jonathan AC Roberts - Monday, August 27, 2018 7:00 AM

  NikosV - Sunday, August 26, 2018 11:30 PM

  Jonathan AC Roberts - Sunday, August 26, 2018 11:16 PM

  I took it you wanted to select them like a UNION not adding additional columns to the result set, is that right?

  Yes I just realised that. Sorry maybe I wasn't clear enough. Yes I'd like to append additional columns. 

  So you want to append rows or append columns?

  Hi Jonathan. Columns please. Thr current code shows chosen year, the next set of same columns will show previous year.

  I would create a Table Valued Function with the main query in and parameters of @StartDate and @EndDate, then I would create a stored procedure that selects from the TVF twice (once for each year) and adds a rownum column then full joins on the rownum column, something like this:

  DECLARE @StartDate datetime, @EndDate datetime

  ;WITH CTE AS
(
select row_number() over (order by(select 1)) rowNum,*
FROM myTVF(@StartDate, @EndDate)
), CTE2 AS
(
select row_number() over (order by(select 1)) rowNum,*
FROM myTVF(DATEADD(yy,-1,@StartDate), DATEADD(yy,-1,@EndDate))
)
SELECT
a.ClaimType        ClaimType1,
a.Submitted        Submitted1,
a.ApprovedPaid       ApprovedPaid1,
a.Rejected        Rejected1,
a.Pending        Pending1,
a.TotalSubmittedSumInsured   TotalSubmittedSumInsured1,
a.TotalApprovedSumInsured   TotalApprovedSumInsured1,
a.TotalRejectedSumInsured   TotalRejectedSumInsured1,
a.TotalPendingSumInsured   TotalPendingSumInsured1,
a.ApprovedVsSubmitted    ApprovedVsSubmitted1,
a.RejectedVsSubmitted    RejectedVsSubmitted1,
a.PendingVsSubmitted     PendingVsSubmitted1,
a.ApprovedVsSubmittedSum   ApprovedVsSubmittedSum1,
a.RejectedVsSubmittedSum   RejectedVsSubmittedSum1,
a.PendingVsSubmittedSum1   PendingVsSubmittedSum1
b.ClaimType        ClaimType2,
b.Submitted        Submitted2,
b.ApprovedPaid       ApprovedPaid2,
b.Rejected        Rejected2,
b.Pending        Pending2,
b.TotalSubmittedSumInsured   TotalSubmittedSumInsured2,
b.TotalApprovedSumInsured   TotalApprovedSumInsured2,
b.TotalRejectedSumInsured   TotalRejectedSumInsured2,
b.TotalPendingSumInsured   TotalPendingSumInsured2,
b.ApprovedVsSubmitted    ApprovedVsSubmitted2,
b.RejectedVsSubmitted    RejectedVsSubmitted2,
b.PendingVsSubmitted     PendingVsSubmitted2,
b.ApprovedVsSubmittedSum   ApprovedVsSubmittedSum2,
b.RejectedVsSubmittedSum   RejectedVsSubmittedSum2,
b.PendingVsSubmittedSum2   PendingVsSubmittedSum2
FROM CTE a
FULL JOIN CTE2 b ON b.RowNum=a.RowNum

• adaddio

  Mr or Mrs. 500

  Points: 539

  More actions

  August 27, 2018 at 3:58 pm

  #2003526

  I think you can do something like what I have below. I just mocked up some simple test data with a simple aggregate on the QTY column but I think you could adapt it for your specific use case:

  
--declare your start dates and end dates
declare @DateFrom date = '8/25/18';
Declare @DateTo date = '8/29/18';

  --this CTE is just for generating test data:
with testdata as (
select '8/24/17' as date ,2 as qty
union
select '8/24/17' as date ,5 as qty
union
select '8/25/17' as date ,7 as qty
union
select '8/25/17' as date ,9 as qty
union
select '8/26/17' as date ,5 as qty
union
select '8/26/17' as date ,3 as qty
union
select '8/27/17' as date ,5 as qty
union
select '8/25/18' as date ,3 as qty
union
select '8/25/18' as date ,8 as qty
union
select '8/26/18' as date ,8 as qty
union
select '8/26/18' as date ,1 as qty
union
select '8/28/18' as date ,1 as qty
)

  --now we actually want to get our data aggregated
,AggregatedData as
(
select
--select the stage so we can group on it
Stage
--conditional sum based on current year flag
,sum(case when DateFlags.CurrentYearDateRange = 1 then qty else 0 end)    as CurrentYearTotal
--conditional sum based on previous year flag
,sum(case when DateFlags.PreviousYearDateRange = 1 then qty else 0 end) as PreviousYearTotal

  from testdata td
cross apply(
            select
                --flag for current period
                case when td.date between @datefrom and @dateto Then 1
                else 0 end as CurrentYearDateRange
                --flag for previous period
                ,case when td.date between dateadd(year,-1,@datefrom) and dateadd(year,-1,@dateto) Then 1
                else 0 end as PreviousYearDateRange
                --determine if the row falls into current period or the previous period
                ,case
                    when td.date between @datefrom and @dateto Then 'Current'
                    when td.date between dateadd(year,-1,@datefrom) and dateadd(year,-1,@dateto) Then 'Previous'
                 end as Stage
            )DateFlags

  --where clause needs to respect both date range buckets
where DateFlags.CurrentYearDateRange = 1 or DateFlags.PreviousYearDateRange = 1

  group by stage
)
--final select...only want 1 row per stage
select
CurrentYearTotal
,previous.PreviousYearTotal
from AggregatedData cur
cross apply(
            select
            PreviousYearTotal
            from AggregatedData prev
            where prev.Stage = 'Previous'
            )Previous

  where cur.stage = 'Current'

• NikosV

  SSCrazy

  Points: 2111

  More actions

  August 27, 2018 at 11:49 pm

  #2003544

  Jonathan AC Roberts - Monday, August 27, 2018 9:06 AM

  NikosV - Monday, August 27, 2018 7:56 AM

  Jonathan AC Roberts - Monday, August 27, 2018 7:00 AM

  NikosV - Sunday, August 26, 2018 11:30 PM

  Jonathan AC Roberts - Sunday, August 26, 2018 11:16 PM

  I took it you wanted to select them like a UNION not adding additional columns to the result set, is that right?

  Yes I just realised that. Sorry maybe I wasn't clear enough. Yes I'd like to append additional columns. 

  So you want to append rows or append columns?

  Hi Jonathan. Columns please. Thr current code shows chosen year, the next set of same columns will show previous year.

  I would create a Table Valued Function with the main query in and parameters of @StartDate and @EndDate, then I would create a stored procedure that selects from the TVF twice (once for each year) and adds a rownum column then full joins on the rownum column, something like this:

  DECLARE @StartDate datetime, @EndDate datetime

  ;WITH CTE AS
(
select row_number() over (order by(select 1)) rowNum,*
FROM myTVF(@StartDate, @EndDate)
), CTE2 AS
(
select row_number() over (order by(select 1)) rowNum,*
FROM myTVF(DATEADD(yy,-1,@StartDate), DATEADD(yy,-1,@EndDate))
)
SELECT
a.ClaimType        ClaimType1,
a.Submitted        Submitted1,
a.ApprovedPaid       ApprovedPaid1,
a.Rejected        Rejected1,
a.Pending        Pending1,
a.TotalSubmittedSumInsured   TotalSubmittedSumInsured1,
a.TotalApprovedSumInsured   TotalApprovedSumInsured1,
a.TotalRejectedSumInsured   TotalRejectedSumInsured1,
a.TotalPendingSumInsured   TotalPendingSumInsured1,
a.ApprovedVsSubmitted    ApprovedVsSubmitted1,
a.RejectedVsSubmitted    RejectedVsSubmitted1,
a.PendingVsSubmitted     PendingVsSubmitted1,
a.ApprovedVsSubmittedSum   ApprovedVsSubmittedSum1,
a.RejectedVsSubmittedSum   RejectedVsSubmittedSum1,
a.PendingVsSubmittedSum1   PendingVsSubmittedSum1
b.ClaimType        ClaimType2,
b.Submitted        Submitted2,
b.ApprovedPaid       ApprovedPaid2,
b.Rejected        Rejected2,
b.Pending        Pending2,
b.TotalSubmittedSumInsured   TotalSubmittedSumInsured2,
b.TotalApprovedSumInsured   TotalApprovedSumInsured2,
b.TotalRejectedSumInsured   TotalRejectedSumInsured2,
b.TotalPendingSumInsured   TotalPendingSumInsured2,
b.ApprovedVsSubmitted    ApprovedVsSubmitted2,
b.RejectedVsSubmitted    RejectedVsSubmitted2,
b.PendingVsSubmitted     PendingVsSubmitted2,
b.ApprovedVsSubmittedSum   ApprovedVsSubmittedSum2,
b.RejectedVsSubmittedSum   RejectedVsSubmittedSum2,
b.PendingVsSubmittedSum2   PendingVsSubmittedSum2
FROM CTE a
FULL JOIN CTE2 b ON b.RowNum=a.RowNum

  Didn't copy the solution exactly, but it was a solution. Marked as answer. Thanks Jonathan.