Execute same SELECT with a different condition

• NikosV

  SSCrazy

  Points: 2111

  More actions

  August 8, 2018 at 5:07 am

  #362112

  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?
  

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  August 8, 2018 at 5:24 am

  #2000753

  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

• NikosV

  SSCrazy

  Points: 2111

  More actions

  August 8, 2018 at 5:29 am

  #2000754

  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.

• Jonathan AC Roberts

  SSCoach

  Points: 18355

  More actions

  August 8, 2018 at 5:34 am

  #2000755

  laurie-789651 - Wednesday, August 8, 2018 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

  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.

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  August 8, 2018 at 5:40 am

  #2000757

  Not really sure what you're aiming for here.
  Can you explain in a bit more detail.

• NikosV

  SSCrazy

  Points: 2111

  More actions

  August 8, 2018 at 5:45 am

  #2000758

  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

• aaron.reese

  SSChampion

  Points: 13498

  More actions

  August 8, 2018 at 7:59 am

  #2000803

  Instead on inserting directly into a #temp table, build two CTE (common table expressions) and then link them together with the CT table using LEFT JOINS: you have to do this in case one or more of the claim types does not exist in one of the data sets.

  
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
)

  SELECT
     CT.claim_type_name AS ClaimType
     ,CY.Cases
     ,CY.SumInsuredTotal
     ,PY.Cases
     ,PY.SumInsuredTotal
   LEFT JOIN
     CTE_CY AS CY ON CY.ClaimType = CT.claim_type_name
   LEFT JOIN
     CTE_PY AS PY ON CY.ClaimType = CT.claim_type_name

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  August 8, 2018 at 8:39 am

  #2000822

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

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  August 8, 2018 at 8:47 am

  #2000826

  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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 8, 2018 at 9:06 am

  #2000837

  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


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2