Duplicate records show only once in result set

  • Hello Everyone,

    Below is my SQL query

    SELECT imp_id, 
    (
    SELECT name
    FROM tbl_Employee
    WHERE sno = 11
    ) AS Name,
    CASE
    WHEN tbl_emp_imprest.other_location <= 0
    OR tbl_emp_imprest.other_location IS NULL
    THEN tbl_projects.location
    ELSE CASE
    WHEN tblOtherLocation.location IS NULL
    THEN tbl_projects.location
    ELSE tblOtherLocation.location
    END
    END AS Location,
    CASE
    WHEN tbl_emp_imprest.other_location <= 0
    OR tbl_emp_imprest.other_location IS NULL
    THEN tbl_projects.Sno
    ELSE CASE
    WHEN tblOtherLocation.location IS NULL
    THEN tbl_projects.sno
    ELSE tblOtherLocation.other_location_id
    END
    END AS location_id,
    CASE
    WHEN ISNULL(imprest_head, 0) = 10
    THEN dbo.Total_Diesel_Amount(11, 04, 2021)
    ELSE 0
    END AS expenditure,
    0 AS reco_amount,
    0 AS approved_amount,
    0 AS audit_amount,
    CASE
    WHEN ISNULL(imprest_head, 0) = 10
    THEN dbo.Total_Diesel_Imprest(11, 04, 2021)
    ELSE imp_amount_approved_by_finance
    END AS paid_amount,

    ISNULL(imprest_head, 0) AS particular_id,
    ISNULL(
    (
    SELECT head
    FROM tblHeads
    WHERE head_id = imprest_head
    ), '') AS particular,
    id
    FROM tbl_emp_imprest
    LEFT JOIN tblOtherLocation ON other_location_id = tbl_emp_imprest.other_location
    LEFT JOIN tbl_projects ON tbl_projects.sno = tbl_emp_imprest.imp_emp_location
    WHERE imp_id = 11
    AND imp_amount_approved_by_finance > 0
    AND expenditure_submit = 0
    AND DATEPART(year, imp_fin_appr_date) = 2021
    AND DATEPART(month, imp_fin_appr_date) = 04;

    This query gives a result set but with some duplicate rows.

    Why these duplicates rows are coming and how remove those from the result set.

    Thanks

  • Well just looking at the location_id in the result set these are not duplicate records.

  • ZZartin wrote:

    Well just looking at the location_id in the result set these are not duplicate records.

    There is one duplicate - location_id = 19...my guess is there are multiple projects, but that is just a guess.  There are many problems with the query - as written.

    No table aliases - and some columns using the full table.column reference but most do not.  Makes it impossible to determine what table has a column named expenditure_submit.

    Sub-queries that should be joins - since it appears to be a one-one relationship.

    Date criteria using functions - should just define the beginning of the month and end of the month and use a range check.  For example:

    AND imp_fin_appr_date >= '20210401'
    AND imp_fin_appr_date < '20210501';

    There also appears to be a scalar-function, but the function doesn't use any data from the query.  Could avoid calling that function for each row by setting the result to a variable and using the variable instead of the call.

    With that said - duplicates are either caused by multiple locations or multiple projects.  I would verify the relationship between the tables and ensure all PK columns are used in the join, and if that doesn't resolve the issue - then you need to identify which one is needed for this query.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Makes it impossible to determine what table has a column named expenditure_submit.

    expenditure_submit belongs to tbl_emp_imprest

    Could avoid calling that function for each row by setting the result to a variable and using the variable instead of the call

    How?

    then you need to identify which one is needed for this query

    any one of those is sufficient for me.

    Kindly help.

     

  • Just identifying the table here doesn't help - there are columns in the select and where the table isn't identified.  Use table aliases - and use the table alias when referencing every column.

    Declare @diesel_imprest {same as returned value from function} = dbo.Total_Diesel_Imprest(11, 04, 2021);

    Declare a variable and define it as the same data type as returned by the function - and set it to the return value for the function.  Then use that value in your query instead of calling out to the function for each row.

    any one of those is sufficient for me.[\quote]

    Any one of which?  I have no idea what the criteria is or why that is causing duplicates - you need to identify the relationship on the table and determine which should be returned.  Once you have that identified then you can determine how to return the correct data.  That could be done by adding additional criteria to the join - or using an OUTER APPLY - or something else.

    You can convert these to joins:

    (
    SELECT name
    FROM tbl_Employee
    WHERE sno = 11
    ) AS Name,

    (
    SELECT head
    FROM tblHeads
    WHERE head_id = imprest_head
    ), '') AS particular,

    For the first one - you can do this:

    FROM tbl_Employee   emp
    INNER JOIN tbl_emp_imprest ei On ei.imp_id = emp.sno
    ...
    WHERE emp.sno = 11

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Now I have modified the query as suggested by you.

    Declare @diesel_expenditure INT;

    SET @diesel_imprest = dbo.Total_Diesel_Imprest(11, 04, 2021);
    SET @diesel_expenditure = dbo.Total_Diesel_Amount(11, 04, 2021)

    SELECT TEI.imp_id, TE.Name
    AS Name,
    CASE
    WHEN TEI.other_location <= 0
    OR TEI.other_location IS NULL
    THEN tbl_projects.location
    ELSE CASE
    WHEN tblOtherLocation.location IS NULL
    THEN tbl_projects.location
    ELSE tblOtherLocation.location
    END
    END AS Location,
    CASE
    WHEN TEI.other_location <= 0
    OR TEI.other_location IS NULL
    THEN tbl_projects.Sno
    ELSE CASE
    WHEN tblOtherLocation.location IS NULL
    THEN tbl_projects.sno
    ELSE tblOtherLocation.other_location_id
    END
    END AS location_id,
    CASE
    WHEN ISNULL(imprest_head, 0) = 10
    THEN @diesel_expenditure
    ELSE 0
    END AS expenditure,
    0 AS reco_amount,
    0 AS approved_amount,
    0 AS audit_amount,
    CASE
    WHEN ISNULL(imprest_head, 0) = 10
    THEN @diesel_imprest
    ELSE imp_amount_approved_by_finance
    END AS paid_amount,

    ISNULL(imprest_head, 0) AS particular_id,

    TH.head AS particular,
    id
    FROM tbl_emp_imprest TEI
    JOIN tblHeads TH ON TH.head_id = TEI.imprest_head
    JOIN tbl_Employee TE ON TE.Sno = TEI.imp_id
    LEFT JOIN tblOtherLocation ON other_location_id = TEI.other_location
    LEFT JOIN tbl_projects ON tbl_projects.sno = TEI.imp_emp_location
    WHERE TEI.imp_id = 11
    AND TEI.imp_amount_approved_by_finance > 0
    AND TEI.expenditure_submit = 0
    AND DATEPART(year, TEI.imp_fin_appr_date) = 2021
    AND DATEPART(month, TEI.imp_fin_appr_date) = 04
    ORDER BY TEI.imp_fin_appr_date

    The result is same

    I have taking paid_amount and expenditure (total for particular_id  = 10).

    I only want only one record should be there for particular_id 10, rest particular ids remain same. Right now there are 4 rows for particular_id 10

    Thanks

     

     

    • This reply was modified 1 week, 2 days ago by  gaurav.

Viewing 6 posts - 1 through 6 (of 6 total)

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