May 4, 2021 at 7:23 pm
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
May 4, 2021 at 7:37 pm
Well just looking at the location_id in the result set these are not duplicate records.
May 4, 2021 at 8:49 pm
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
May 5, 2021 at 4:30 am
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.
May 5, 2021 at 5:37 pm
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
May 5, 2021 at 6:13 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply