Executing same SQL code twice

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

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

  • 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

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

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

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

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

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

  • 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

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

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

  • 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

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

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

Viewing 14 posts - 16 through 28 (of 28 total)

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