August 24, 2018 at 5:24 am
I have an SQL query which uses a DateFrom and DateTo variable. This query later will go into a stored procedure. DateFrom and DateTo will become the parameters. So if the user specifies DateFrom as 2018-01-01 and DateTo 2018-08-24 an X amount of results will be returned.
Combined with those results, I would like to execute exactly the same code for the year before and show all the data in one table.
Here is my current code.
DECLARE @DateFrom DATE = '2018-01-01';
DECLARE @DateTo DATE = '2018-08-21';
IF OBJECT_ID('tempdb..#ClaimType') IS NOT NULL
DROP TABLE #ClaimType
CREATE TABLE #ClaimType
(
ClaimDescription VARCHAR(255) PRIMARY KEY CLUSTERED
);
INSERT INTO #ClaimType(ClaimDescription)
SELECT * FROM
(
VALUES ('2nd Annuitant Death - Living Spouse'),
('Accidental Death'),
('Accidental Death ( Traffic )'),
('Accidental Death (Common Carrier)'),
('Accidental Death (Common Carrier) Top-Up'),
('Accidental Death Basic Cover'),
('Accidental Death Top-Up'),
('Accidental Death Travel'),
('Accidental Death with PTD'),
('Accidental Death with PTD-DO NOT USE'),
('Accidental Economical Disability >= 67% due to Physiological Disability >= 25%'),
('Accidental Permanent Loss'),
('Accidental Physiological Disability >= 67%'),
('Accidental Total & Permanent Disability'),
('Co-insurance - First Death'),
('Critical Illness'),
('Death'),
('Death (annuity)'),
('Death (Old)'),
('Death Basic Cover'),
('Death Other Than Accident (CV Only)'),
('Death with Accidental Death'),
('Disability'),
('Disability due to Accident ( Traffic )'),
('Disability due to Disease'),
('Disability due to mental Health'),
('Disability without Benefit'),
('Disability WP 565'),
('Economical Disability >= 25%'),
('Economical Disability >= 25% due to Physiological Disability >= 25%'),
('Economical Disability >= 67%'),
('Economical Disability >= 67% due to Physiological Disability >= 25%'),
('Family Protector'),
('First Death'),
('First Death - Accidental'),
('Hospital Indemnity'),
('Income Cover'),
('Income Replacement'),
('LifeCare Critical Illness'),
('LTC Death'),
('LTC Event Claim'),
('Main Annuitant Death with Living Spouse'),
('Medical Expense Claim'),
('Medical Investigation Expense'),
('Non Life Payee Death'),
('Partial Disability'),
('Payor Death - WOP'),
('Payor Disability - WOP'),
('Physiological Disability >= 25%'),
('Physiological Disability >= 67%'),
('Refund'),
('Resiliation due to Death'),
('Spouse Rider Death'),
('Total & Permanent Disability'),
('Total Disability'),
('Waiver Benefit'),
('Waiver of Premium'),
('Waiver of Premiums')
)Temp(Col)
SELECT GenPar.ParameterValue AS ClaimType,
Submitted = COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),
ApprovedPaid = COUNT(CASE WHEN CurrentStatus IN (40,30) THEN 1 END),
Rejected = COUNT(CASE CurrentStatus WHEN 25 THEN 1 END),
Pending = COUNT(CASE CurrentStatus WHEN 12 THEN 1 END),
TotalSubmittedSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 10 THEN PCover.SumAssured END),0),
TotalApprovedSumInsured = ISNULL(SUM(CASE WHEN ClaimMain.CurrentStatus IN (40,30) THEN PCover.SumAssured END),0),
TotalRejectedSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 25 THEN PCover.SumAssured END),0),
TotalPendingSumInsured = ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 12 THEN PCover.SumAssured END),0),
ApprovedVsSubmitted = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN 1 END)
-COUNT(CASE WHEN CurrentStatus IN (40,30) THEN 1 END)
/NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),0)),0) AS VARCHAR) + '%',
RejectedVsSubmitted = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN 1 END)
-COUNT(CASE CurrentStatus WHEN 25 THEN 1 END)
/NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),0)),0) AS VARCHAR) + '%',
PendingVsSubmitted = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN 1 END)
-COUNT(CASE CurrentStatus WHEN 12 THEN 1 END)
/NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN 1 END),0)),0) AS VARCHAR) + '%',
ApprovedVsSubmittedSum = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END)
-COUNT(CASE CurrentStatus WHEN 12 THEN PCover.SumAssured END)
/NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END),0)),0) AS VARCHAR) + '%',
RejectedVsSubmittedSum = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END)
-COUNT(CASE CurrentStatus WHEN 25 THEN PCover.SumAssured END)
/NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END),0)),0) AS VARCHAR) + '%',
PendingVsSubmittedSum = CAST(ISNULL((COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END)
-COUNT(CASE CurrentStatus WHEN 12 THEN PCover.SumAssured END)
/NULLIF(COUNT(CASE CurrentStatus WHEN 10 THEN PCover.SumAssured END),0)),0) AS VARCHAR) + '%' FROM Company.Schema.GeneralParameter GenPar
INNER JOIN Company.Schema.ClaimMain ON GenPar.ParameterId = ClaimMain.ClaimType
INNER JOIN (SELECT ROW_NUMBER()OVER(PARTITION BY PolicyNumber ORDER BY PolicyNumber)#Row,
PolicyNumber,
SumAssured
FROM Company.Schema.PolicyCover
WHERE ClosingStatus = 10 AND BasicCoverFlag = 1 AND SumAssured <> 0
)PCover ON PCover.PolicyNumber = ClaimMain.PolicyNumber
WHERE ClaimMain.OpeningRegistrationDate BETWEEN @DateFrom AND @DateTo
AND GenPar.ParameterName = 'ClaimType'
AND GenPar.ParameterValue IN (SELECT ClaimDescription FROM #ClaimType)
AND PCover.#Row = 1
GROUP BY ClaimDescription,
GenPar.ParameterValue
ORDER BY ClaimDescription
Underneath the temporary table I'm creating, I'm doing my SELECT with this in the WHERE.WHERE ClaimMain.OpeningRegistrationDate BETWEEN @DateFrom AND @DateTo
I need to do the same SELECT exactly but replacing the above line with this.WHERE ClaimMain.OpeningRegistrationDate BETWEEN DATEADD(YEAR,-1,@DateFrom) AND DATEADD(YEAR,-1,@DateTo)
My initial thought, was to take all the existing code, put it into a CTE called CTEDefinedYear, then after altering the date condition, copy all that code into another CTE called CTEPreviousYear, Full Join the two CTE's and select my columns as per normal. However this means that apart from the date condition, I am repeating ALL my code twice. I know repetition, depending on what we're doing I suppose is a bad thing and my query is already 117 lines long which means I'll be doubling all that easily.
If I used the mentioned CTE approach, I obviously wouldn't copy the temp tables data again but I'd copy the rest of the code.
So my questions here are:
1. What would be the best approach to basically run the same code again but with a different date condition and return all the data from both date conditions? A table with 30 columns, 15 for this year, 15 for last year.
2. Generally in situations like this, what should I be careful of and how should I think about this? Is the CTE approach suitable? What are some things to keep in mind here because it's the first time I'm doing this and it's probably the first of many.
Thanks guys, happy to make any further clarifications .
August 24, 2018 at 5:30 am
WHERE ClaimMain.OpeningRegistrationDate BETWEEN DATEADD(YEAR,-1,@DateFrom) and @DateToThe absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 24, 2018 at 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
August 24, 2018 at 5:54 am
Jonathan AC Roberts - Friday, August 24, 2018 5:51 AMInstead 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.
August 24, 2018 at 5:57 am
Phil Parkin - Friday, August 24, 2018 5:30 AMWhy 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.
August 24, 2018 at 6:10 am
Aha, my logic is flawed.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 24, 2018 at 6:22 am
NikosV - Friday, August 24, 2018 5:54 AMHas 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 tableFROM 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.
August 24, 2018 at 6:28 am
Jonathan AC Roberts - Friday, August 24, 2018 6:22 AMOk 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 tableFROM 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 ClaimDescriptionYou 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.
August 24, 2018 at 7:47 am
You posted the EXACT SAME QUESTION two weeks ago.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 24, 2018 at 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?
August 24, 2018 at 8:28 am
NikosV - Friday, August 24, 2018 7:51 AMI 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
August 24, 2018 at 8:32 am
August 24, 2018 at 10:30 am
Phil Parkin - Friday, August 24, 2018 5:30 AMWhy 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)
August 24, 2018 at 10:35 am
sgmunson - Friday, August 24, 2018 10:30 AMPhil Parkin - Friday, August 24, 2018 5:30 AMWhy not just modify the WHERE clause?WHERE ClaimMain.OpeningRegistrationDate BETWEEN DATEADD(YEAR,-1,@DateFrom) and @DateToThat 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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 24, 2018 at 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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply