Duplicate records show only once in result set

Question

Post reply

Duplicate records show only once in result set

gaurav

SSC-Addicted

Points: 404

May 4, 2021 at 7:23 pm

#3879308

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

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

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

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 1

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

Jeffrey Williams SSC Guru Points: 90250 More actions · Answer 2

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

gaurav SSC-Addicted Points: 404 More actions · Answer 3

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.

Jeffrey Williams SSC Guru Points: 90250 More actions · Answer 4

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

gaurav SSC-Addicted Points: 404 More actions · Answer 5

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 2 years, 11 months ago by gaurav.