August 8, 2018 at 5:07 am
I have this code.SELECT
CT.claim_type_name AS ClaimType,
COUNT(CM.claim_type) AS Cases,
ISNULL(SUM(CC.original_amount),0) AS SumInsuredTotal
INTO #ChosenYear
FROM [ALISPREP].[UAT3].dbo.t_claim_type CT
LEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_main CM
ON CM.claim_type = CT.claim_type
LEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_cover CC
ON CC.claim_id = CM.claim_id
WHERE CM.opening_reg_date BETWEEN @DateFrom AND @DateTo
GROUP BY CT.claim_type_name, CT.claim_type
ORDER BY CT.claim_type_name
and basically I want to say the same thing but with this condition:WHERE CM.opening_reg_date IS ONE YEAR BEFORE @DateFrom and @DateTo
Any ideas?
August 8, 2018 at 5:24 am
How about
DECLARE @Increment Int = 0 -- or -1
SELECT
CT.claim_type_name AS ClaimType,
COUNT(CM.claim_type) AS Cases,
ISNULL(SUM(CC.original_amount),0) AS SumInsuredTotal
INTO #ChosenYear
FROM [ALISPREP].[UAT3].dbo.t_claim_type CT
LEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_main CM
ON CM.claim_type = CT.claim_type
LEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_cover CC
ON CC.claim_id = CM.claim_id
WHERE CM.opening_reg_date BETWEEN DATEADD(year, @Increment, @DateFrom) AND DATEADD(year, @Increment, @DateTo)
GROUP BY CT.claim_type_name, CT.claim_type
ORDER BY CT.claim_type_name
August 8, 2018 at 5:29 am
Looks good but how do I combine that with the other select?
I made the temp table in hope to join onto it but I can't.
August 8, 2018 at 5:34 am
laurie-789651 - Wednesday, August 8, 2018 5:24 AMHow about
DECLARE @Increment Int = 0 -- or -1SELECT
CT.claim_type_name AS ClaimType,
COUNT(CM.claim_type) AS Cases,
ISNULL(SUM(CC.original_amount),0) AS SumInsuredTotal
INTO #ChosenYear
FROM [ALISPREP].[UAT3].dbo.t_claim_type CT
LEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_main CM
ON CM.claim_type = CT.claim_type
LEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_cover CC
ON CC.claim_id = CM.claim_id
WHERE CM.opening_reg_date BETWEEN DATEADD(year, @Increment, @DateFrom) AND DATEADD(year, @Increment, @DateTo)
GROUP BY CT.claim_type_name, CT.claim_type
ORDER BY CT.claim_type_name
I think putting the CM criteria in the WHERE clause will invalidate the LEFT JOIN and make it the equivalent of an INNER JOIN. The code:CM.opening_reg_date BETWEEN DATEADD(year, @Increment, @DateFrom) AND DATEADD(year, @Increment, @DateTo)
needs to go in the ON clause of the LEFT JOIN.
August 8, 2018 at 5:40 am
Not really sure what you're aiming for here.
Can you explain in a bit more detail.
August 8, 2018 at 5:45 am
I just want to make exactly the same selection of columns but with a year before. So I'm selecting three columns but I want to see six, three that are returned with @DateFrom and @DateTo and three next to them returned with a year before
August 8, 2018 at 7:59 am
DECLARE @DateFrom DATE, @DateTo DATE
SET @DateFrom = '20170101'
SET @DateTo = '20171231'
;
WITH CTE_CY AS
(
SELECT
CT.claim_type_name AS ClaimType,
COUNT(CM.claim_type) AS Cases,
ISNULL(SUM(CC.original_amount),0) AS SumInsuredTotal
FROM
[ALISPREP].[UAT3].dbo.t_claim_type CT
LEFT OUTER JOIN
[ALISPREP].[UAT3].dbo.p_claim_main CM ON CM.claim_type = CT.claim_type
LEFT OUTER JOIN
[ALISPREP].[UAT3].dbo.p_claim_cover CC ON CC.claim_id = CM.claim_id
WHERE
CM.opening_reg_date BETWEEN @DateFrom AND @DateTo
GROUP BY
CT.claim_type_name, CT.claim_type
)
,
CTE_PY AS
(
SELECT
CT.claim_type_name AS ClaimType,
COUNT(CM.claim_type) AS Cases,
ISNULL(SUM(CC.original_amount),0) AS SumInsuredTotal
FROM
[ALISPREP].[UAT3].dbo.t_claim_type CT
LEFT OUTER JOIN
[ALISPREP].[UAT3].dbo.p_claim_main CM ON CM.claim_type = CT.claim_type
LEFT OUTER JOIN
[ALISPREP].[UAT3].dbo.p_claim_cover CC ON CC.claim_id = CM.claim_id
WHERE
CM.opening_reg_date BETWEEN DATEADD(YEAR,-1,@DateFrom) AND DATEADD(YEAR,-1,@DateTo)
GROUP BY
CT.claim_type_name, CT.claim_type
)
August 8, 2018 at 8:39 am
I've come up with something similar, but using Jonathan's suggestion about the where clause.
USE [tempdb]
GO
DECLARE @DateFrom Date = '01 Aug 2018', @DateTo Date = '03 Aug 2018';
IF OBJECT_ID('dbo.t_claim_type') IS NOT NULL DROP TABLE dbo.t_claim_type;
IF OBJECT_ID('dbo.p_claim_main') IS NOT NULL DROP TABLE dbo.p_claim_main;
IF OBJECT_ID('dbo.p_claim_cover') IS NOT NULL DROP TABLE dbo.p_claim_cover;
CREATE TABLE dbo.t_claim_type (claim_type int, claim_type_name Varchar(20));
INSERT dbo.t_claim_type VALUES (1, 'Car Claim'), (2, 'House Claim');
CREATE TABLE dbo.p_claim_main (claim_id Int, claim_type int, opening_reg_date date);
INSERT dbo.p_claim_main VALUES (11, 1, '02 Aug 2018'), (12, 2, '03 Aug 2017'), (13, 1, '01 Aug 2017'), (14, 1, '01 Aug 2017');
CREATE TABLE dbo.p_claim_cover (claim_id Int, original_amount decimal(9,2));
INSERT dbo.p_claim_cover VALUES (11, 252.60), (12, 1326.44), (13, 750.25), (14, 750.25);
WITH CTE_LAST_YEAR AS
(
SELECT
CT.claim_type_name AS ClaimType,
COUNT(CM.claim_type) AS Cases,
ISNULL(SUM(CC.original_amount),0) AS SumInsuredTotal
FROM dbo.t_claim_type CT
LEFT OUTER JOIN dbo.p_claim_main CM
ON CM.claim_type = CT.claim_type AND CM.opening_reg_date BETWEEN DATEADD(year, -1, @DateFrom) AND DATEADD(year, -1, @DateTo)
LEFT OUTER JOIN dbo.p_claim_cover CC
ON CC.claim_id = CM.claim_id
GROUP BY CT.claim_type_name, CT.claim_type
),
CTE_THIS_YEAR AS
(
SELECT
CT.claim_type_name AS ClaimType,
COUNT(CM.claim_type) AS Cases,
ISNULL(SUM(CC.original_amount),0) AS SumInsuredTotal
FROM dbo.t_claim_type CT
LEFT OUTER JOIN dbo.p_claim_main CM
ON CM.claim_type = CT.claim_type AND CM.opening_reg_date BETWEEN @DateFrom AND @DateTo
LEFT OUTER JOIN dbo.p_claim_cover CC
ON CC.claim_id = CM.claim_id
GROUP BY CT.claim_type_name, CT.claim_type
)
SELECT ISNULL(TY.ClaimType, LY.ClaimType) as ClaimType,
ISNULL(TY.Cases, 0) as CasesTY,
ISNULL(TY.SumInsuredTotal, 0) as SumInsuredTotalTY,
ISNULL(LY.Cases, 0) as CasesLY,
ISNULL(LY.SumInsuredTotal, 0) as SumInsuredTotalLY
FROM CTE_LAST_YEAR LY
FULL JOIN CTE_THIS_YEAR TY ON TY.ClaimType = LY.ClaimType
ORDER BY ISNULL(TY.ClaimType, LY.ClaimType);
August 8, 2018 at 8:47 am
I think that a cross tab might perform better here, especially since you're already doing aggregates on the data. I used a CROSS APPLY instead of writing essentially the same CASE statement multiple times.
DECLARE @DateFrom DATE, @DateTo DATE
SET @DateFrom = '20170101'
SET @DateTo = '20171231'
;
SELECT
CT.claim_type_name AS ClaimType,
COUNT(ct.cur_cases) AS Cur_Cases,
ISNULL(SUM(ct.cur_amount),0) AS Cur_SumInsuredTotal,
COUNT(ct.Prev_cases) AS Prev_Cases,
ISNULL(SUM(ct.Prev_amount),0) AS Prev_SumInsuredTotal
FROM
[ALISPREP].[UAT3].dbo.t_claim_type CT
LEFT OUTER JOIN
[ALISPREP].[UAT3].dbo.p_claim_main CM ON CM.claim_type = CT.claim_type
AND CM.opening_reg_date BETWEEN DATEADD(YEAR,-1,@DateFrom) AND @DateTo
LEFT OUTER JOIN
[ALISPREP].[UAT3].dbo.p_claim_cover CC ON CC.claim_id = CM.claim_id
CROSS APPLY
(
SELECT CM.claim_type, CC.original_amount, NULL, NULL
WHERE
CM.opening_reg_date BETWEEN @DateFrom AND @DateTo
UNION ALL
SELECT NULL, NULL, CM.claim_type, CC.original_amount
WHERE
CM.opening_reg_date BETWEEN DATEADD(YEAR,-1,@DateFrom) AND DATEADD(YEAR,-1,@DateTo)
) ct(cur_cases, cur_amount, prev_cases, prev_amount)
GROUP BY
CT.claim_type_name, CT.claim_type
;
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 8, 2018 at 9:06 am
What about simplifying everything?
I'm assuming that you don't actually need 6 columns and you only need 5 as one would be repeated.
SELECT
CT.claim_type_name AS ClaimType,
COUNT(CASE WHEN CM.opening_reg_date BETWEEN @DateFrom AND @DateTo THEN 1 END) AS Cases,
SUM(CASE WHEN CM.opening_reg_date BETWEEN @DateFrom AND @DateTo THEN CC.original_amount ELSE 0 END) AS SumInsuredTotal,
COUNT(CASE WHEN CM.opening_reg_date NOT BETWEEN @DateFrom AND @DateTo THEN 1 END) AS Cases_PrevYear,
SUM(CASE WHEN CM.opening_reg_date NOT BETWEEN @DateFrom AND @DateTo THEN CC.original_amount ELSE 0 END) AS SumInsuredTotal_PrevYear
INTO #ChosenYear
FROM [ALISPREP].[UAT3].dbo.t_claim_type CT
LEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_main CM
ON CM.claim_type = CT.claim_type
LEFT OUTER JOIN [ALISPREP].[UAT3].dbo.p_claim_cover CC
ON CC.claim_id = CM.claim_id
WHERE CM.opening_reg_date BETWEEN @DateFrom AND @DateTo
OR CM.opening_reg_date IS DATEADD( yy, -1, @DateFrom) and DATEADD( yy, -1, @DateTo)
GROUP BY CT.claim_type_name, CT.claim_type
ORDER BY CT.claim_type_name
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply