August 24, 2018 at 11:31 am
sgmunson - Friday, August 24, 2018 10:53 AMHow 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.
August 24, 2018 at 12:11 pm
NikosV - Friday, August 24, 2018 11:31 AMI'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)
August 24, 2018 at 12:37 pm
sgmunson - Friday, August 24, 2018 12:11 PMNikosV - Friday, August 24, 2018 11:31 AMI'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
August 26, 2018 at 10:47 pm
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?
August 26, 2018 at 10:56 pm
NikosV - Sunday, August 26, 2018 10:47 PMThing 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.
August 26, 2018 at 11:01 pm
Jonathan AC Roberts - Sunday, August 26, 2018 10:56 PMNikosV - Sunday, August 26, 2018 10:47 PMThing 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.
August 26, 2018 at 11:16 pm
NikosV - Sunday, August 26, 2018 11:01 PMThose 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?
August 26, 2018 at 11:30 pm
Jonathan AC Roberts - Sunday, August 26, 2018 11:16 PMNikosV - Sunday, August 26, 2018 11:01 PMThose 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)
August 27, 2018 at 5:38 am
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
August 27, 2018 at 7:00 am
NikosV - Sunday, August 26, 2018 11:30 PMJonathan AC Roberts - Sunday, August 26, 2018 11:16 PMI 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?
August 27, 2018 at 7:56 am
Jonathan AC Roberts - Monday, August 27, 2018 7:00 AMNikosV - Sunday, August 26, 2018 11:30 PMJonathan AC Roberts - Sunday, August 26, 2018 11:16 PMI 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.
August 27, 2018 at 9:06 am
NikosV - Monday, August 27, 2018 7:56 AMJonathan AC Roberts - Monday, August 27, 2018 7:00 AMNikosV - Sunday, August 26, 2018 11:30 PMJonathan AC Roberts - Sunday, August 26, 2018 11:16 PMI 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
August 27, 2018 at 3:58 pm
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'
August 27, 2018 at 11:49 pm
Jonathan AC Roberts - Monday, August 27, 2018 9:06 AMNikosV - Monday, August 27, 2018 7:56 AMJonathan AC Roberts - Monday, August 27, 2018 7:00 AMNikosV - Sunday, August 26, 2018 11:30 PMJonathan AC Roberts - Sunday, August 26, 2018 11:16 PMI 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