Table entry appears multiple times

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



  • NikosV - Thursday, August 9, 2018 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.



    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.

  • I'll try!

    Thanks.

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

  • NikosV - Thursday, August 9, 2018 11:46 PM

    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.

    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

  • No problem Drew. After some fiddling I saw that too but couldn.t make it work.
    Any chance of an example?

    Thanks

  • NikosV - Friday, August 10, 2018 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

    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