August 9, 2018 at 11:46 pm
I have this record in a 'GeneralParameter' table.
Id ParameterName ParameterId ParameterAdditionalId ParameterValue ParameterAdditionalValue LoadingTimestamp
----------- ------------------------------ ----------- --------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------
1421 ClaimType 206 NULL Income Replacement 6 2018-08-10 06:08:25.013
Now I need to find records that fall under the ParameterValue 'Income Replacement' within given dates.
Manually querying just for this criteria returns 3 records.
I now have this code in order to create a report.
DECLARE @DateFrom DATE = '2018-01-01'
DECLARE @DateTo DATE = '2018-08-08'
--Only gets Individuals
;WITH ChosenYear AS
(
SELECT GenPar.ParameterValue AS ClaimType,
COUNT(ClaimMain.claim_id) AS Cases,
ISNULL(ClaimCover.original_amount,0) AS SumInsured
FROM
(
SELECT *
FROM Company.alis.GeneralParameter
WHERE ParameterName='ClaimType'
)GenPar
LEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_main ClaimMain
ON GenPar.ParameterId = ClaimMain.claim_type
LEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_cover ClaimCover
ON ClaimMain.claim_id = ClaimCover.claim_id
WHERE ClaimMain.opening_reg_date BETWEEN @DateFrom AND @DateTo
GROUP BY GenPar.ParameterValue,
ClaimCover.original_amount
),PreviousYear AS
( SELECT GenPar.ParameterValue AS ClaimType,
COUNT(ClaimMain.claim_id) AS Cases,
ISNULL(ClaimCover.original_amount,0) AS SumInsured
FROM
(
SELECT * FROM Company.alis.GeneralParameter
WHERE ParameterName='ClaimType'
)GenPar
LEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_main ClaimMain
ON GenPar.ParameterId = ClaimMain.claim_type
LEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_cover ClaimCover
ON ClaimMain.claim_id = ClaimCover.claim_id
WHERE ClaimMain.opening_reg_date BETWEEN DATEADD(YEAR,-1,@DateFrom) AND DATEADD(YEAR,-1,@DateTo)
GROUP BY GenPar.ParameterValue,
ClaimCover.original_amount
)
SELECT CY.ClaimType,
CY.Cases AS Cases,
ISNULL(CY.SumInsured,0) AS SumInsured,
ISNULL(PY.Cases,0) AS CasesPreviousYear,
ISNULL(PY.SumInsured,0) AS SumInsured,
ISNULL(CY.Cases-PY.Cases,0) AS CasesYearVsPreviousYear,
'%' + ' ' + CAST(ISNULL(100.0*(CY.Cases-PY.Cases)/CY.Cases,0) AS varchar) AS 'Difference',
ISNULL(CY.SumInsured + PY.SumInsured,0) AS TotalSumInsured
FROM ChosenYear CY
LEFT JOIN PreviousYear PY
ON CY.ClaimType = PY.ClaimType
ORDER BY CY.ClaimType
Running this code produces a set of result and returns 3 rows of 'Income Replacement' like so.
ClaimType Cases SumInsured
Income Replacement 2 200.000000
Income Replacement 1 500.000000
Income Replacement 2 800.000000
After further investigation, for example where cases says 2, those 2 are 2 rows of the same customer in another customer transactions table. The reason it brings back two rows is because the customer has two transactions. I need to get back the first row, once.
This is the code that is responsible.
LEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_cover ClaimCover
ON ClaimMain.claim_id = ClaimCover.claim_id
This [ALISPREP].[UAT3].dbo.p_claim_cover returns 2 records for that customer.
I've seen ROW_NUMBER and I'm pretty sure this is the solution but I can't seem to fit it into the whole code.
Ideally, I would like to implement this on here:
I'd rather not touch the CTE's, if possible.
SELECT CY.ClaimType,
CY.Cases AS Cases,
ISNULL(CY.SumInsured,0) AS SumInsured,
ISNULL(PY.Cases,0) AS CasesPreviousYear,
ISNULL(PY.SumInsured,0) AS SumInsured,
ISNULL(CY.Cases-PY.Cases,0) AS CasesVsPreviousYear,
'%' + ' ' + CAST(ISNULL(100.0*(CY.Cases-PY.Cases)/CY.Cases,0) AS varchar) AS 'Difference',
ISNULL(CY.SumInsured + PY.SumInsured,0) AS TotalSumInsured
FROM ChosenYear CY
LEFT JOIN PreviousYear PY
ON CY.ClaimType = PY.ClaimType
Any help would be appreciated.
August 10, 2018 at 12:37 am
NikosV - Thursday, August 9, 2018 11:46 PMI have this record in a 'GeneralParameter' table.
Id ParameterName ParameterId ParameterAdditionalId ParameterValue ParameterAdditionalValue LoadingTimestamp
----------- ------------------------------ ----------- --------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------
1421 ClaimType 206 NULL Income Replacement 6 2018-08-10 06:08:25.013
Now I need to find records that fall under the ParameterValue 'Income Replacement' within given dates.
Manually querying just for this criteria returns 3 records.I now have this code in order to create a report.
DECLARE @DateFrom DATE = '2018-01-01'
DECLARE @DateTo DATE = '2018-08-08'--Only gets Individuals
;WITH ChosenYear AS
(
SELECT GenPar.ParameterValue AS ClaimType,
COUNT(ClaimMain.claim_id) AS Cases,
ISNULL(ClaimCover.original_amount,0) AS SumInsured
FROM
(
SELECT *
FROM Company.alis.GeneralParameter
WHERE ParameterName='ClaimType'
)GenParLEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_main ClaimMain
ON GenPar.ParameterId = ClaimMain.claim_type
LEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_cover ClaimCover
ON ClaimMain.claim_id = ClaimCover.claim_idWHERE ClaimMain.opening_reg_date BETWEEN @DateFrom AND @DateTo
GROUP BY GenPar.ParameterValue,
ClaimCover.original_amount),PreviousYear AS
( SELECT GenPar.ParameterValue AS ClaimType,
COUNT(ClaimMain.claim_id) AS Cases,
ISNULL(ClaimCover.original_amount,0) AS SumInsured
FROM
(
SELECT * FROM Company.alis.GeneralParameter
WHERE ParameterName='ClaimType'
)GenParLEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_main ClaimMain
ON GenPar.ParameterId = ClaimMain.claim_type
LEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_cover ClaimCover
ON ClaimMain.claim_id = ClaimCover.claim_idWHERE ClaimMain.opening_reg_date BETWEEN DATEADD(YEAR,-1,@DateFrom) AND DATEADD(YEAR,-1,@DateTo)
GROUP BY GenPar.ParameterValue,
ClaimCover.original_amount
)SELECT CY.ClaimType,
CY.Cases AS Cases,
ISNULL(CY.SumInsured,0) AS SumInsured,
ISNULL(PY.Cases,0) AS CasesPreviousYear,
ISNULL(PY.SumInsured,0) AS SumInsured,
ISNULL(CY.Cases-PY.Cases,0) AS CasesYearVsPreviousYear,
'%' + ' ' + CAST(ISNULL(100.0*(CY.Cases-PY.Cases)/CY.Cases,0) AS varchar) AS 'Difference',
ISNULL(CY.SumInsured + PY.SumInsured,0) AS TotalSumInsured
FROM ChosenYear CY
LEFT JOIN PreviousYear PY
ON CY.ClaimType = PY.ClaimTypeORDER BY CY.ClaimType
Running this code produces a set of result and returns 3 rows of 'Income Replacement' like so.
ClaimType Cases SumInsured
Income Replacement 2 200.000000
Income Replacement 1 500.000000
Income Replacement 2 800.000000After further investigation, for example where cases says 2, those 2 are 2 rows of the same customer in another customer transactions table. The reason it brings back two rows is because the customer has two transactions. I need to get back the first row, once.
This is the code that is responsible.
LEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_cover ClaimCover
ON ClaimMain.claim_id = ClaimCover.claim_idThis
[ALISPREP].[UAT3].dbo.p_claim_coverreturns 2 records for that customer.I've seen ROW_NUMBER and I'm pretty sure this is the solution but I can't seem to fit it into the whole code.
Ideally, I would like to implement this on here:
I'd rather not touch the CTE's, if possible.
SELECT CY.ClaimType,
CY.Cases AS Cases,
ISNULL(CY.SumInsured,0) AS SumInsured,
ISNULL(PY.Cases,0) AS CasesPreviousYear,
ISNULL(PY.SumInsured,0) AS SumInsured,
ISNULL(CY.Cases-PY.Cases,0) AS CasesVsPreviousYear,
'%' + ' ' + CAST(ISNULL(100.0*(CY.Cases-PY.Cases)/CY.Cases,0) AS varchar) AS 'Difference',
ISNULL(CY.SumInsured + PY.SumInsured,0) AS TotalSumInsured
FROM ChosenYear CY
LEFT JOIN PreviousYear PY
ON CY.ClaimType = PY.ClaimTypeAny help would be appreciated.
To be honest, I think your code is a bit convoluted. If you could post the DDL (CREATE TABLE statement) for each of the tables involved, sample data (not production data) that is representative of the problem domain including some data that will be excluded from the query results as INSERT INTO statements, and then the expected results based on the sample data.
August 10, 2018 at 1:15 am
I'll try!
Thanks.
August 10, 2018 at 1:56 am
It doesn't look like I'll get round to doing it. It's ok I'll try to find a way. Thanks for your time.
August 10, 2018 at 7:22 am
NikosV - Thursday, August 9, 2018 11:46 PMI've seen ROW_NUMBER and I'm pretty sure this is the solution but I can't seem to fit it into the whole code.
Ideally, I would like to implement this on here:
I'd rather not touch the CTE's, if possible.
You cannot avoid a CTE if you want to use ROW_NUMBER() as a filter. The WHERE clause is evaluated before the SELECT clause, so the WHERE clause cannot reference expressions/aliases that are defined in the SELECT clause, specifically, they cannot reference ROW_NUMBER(), because it is defined in the SELECT clause.
The other main approach for this is to use a CROSS APPLY with a TOP(1). Which one performs better depends on how dense your secondary table is with respect to the first table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 10, 2018 at 7:54 am
No problem Drew. After some fiddling I saw that too but couldn.t make it work.
Any chance of an example?
Thanks
August 10, 2018 at 7:57 am
NikosV - Friday, August 10, 2018 7:54 AMNo problem Drew. After some fiddling I saw that too but couldn.t make it work.
Any chance of an example?Thanks
Sure, once you provide the sample data and expected results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply