Execute same SELECT with a different condition

  • 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?

  • 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

  • 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.

  • 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.

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

  • 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

  • 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

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

  • 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

  • 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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply