Hello Everyone,
Here again I came with a question. Below is my query for a report that I want. It's working fine with no issues.
SELECT TP.Location,
TE.Name,
TEI.imp_amount_approved_by_finance AS Amount,
CONVERT(DATE, TEI.imp_fin_appr_date, 103) AS PaidDate
FROM tbl_emp_imprest TEI
JOIN tbl_Projects TP ON TP.Sno = TEI.imp_emp_location
JOIN tbl_Employee TE ON TE.Sno = TEI.imp_id
WHERE imp_amount_approved_by_finance > 0
AND CAST(imp_fin_appr_date AS DATE) BETWEEN CAST('2021-01-01' AS DATE) AND CAST('2021-12-31' AS DATE)
GROUP BY TP.Location,
TE.Name,
TEI.imp_amount_approved_by_finance,
TEI.imp_fin_appr_date;
And the result of the above query as follows in the screen-shot
What I want is; that let's say for 1st row Location and Name( Agai-JSL , Barun Mondal) should come only once for repeated values and repeated values should replaced with blank values.
Kindly suggest.
Thanks
February 11, 2022 at 11:04 am
This sort of thing is best done in the report (ie, at presentation time) rather than in the query.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 11, 2022 at 1:51 pm
Yep. Formatting is best done in the client app. Always.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 11, 2022 at 2:57 pm
You didn't provide any sample data so I can't test this at all:
SELECT TP.Location,
TE.Name,
CASE WHEN LAG(Amount, 1) OVER(PARTITION BY TP.Location, TE.Name ORDER BY TEI.imp_fin_appr_date) = TEI.imp_amount_approved_by_finance
THEN '' ELSE CAST(TEI.imp_amount_approved_by_finance AS varchar(20)) END AS Amount,
CONVERT(DATE, TEI.imp_fin_appr_date, 103) AS PaidDate
FROM tbl_emp_imprest TEI
JOIN tbl_Projects TP ON TP.Sno = TEI.imp_emp_location
JOIN tbl_Employee TE ON TE.Sno = TEI.imp_id
WHERE imp_amount_approved_by_finance > 0
AND CAST(imp_fin_appr_date AS DATE) BETWEEN CAST('2021-01-01' AS DATE) AND CAST('2021-12-31' AS DATE)
GROUP BY TP.Location,
TE.Name,
TEI.imp_fin_appr_date,
TEI.imp_amount_approved_by_finance
ORDER BY TP.Location,
TE.Name,
TEI.imp_fin_appr_date;
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
I almost always agree with Phil and Grant that this should be done at presentation time in whatever app is generating the final output.
But, then there's the human factors of not having anyone that actually knows how to do such things in the tool they're using to make the final output. A reasonable example of that is how to do it auto-magically in a spreadsheet. Another problem may be that you have no app to produce the final output other than what you can squirt out of SSMS into an email or whatever.
So... I'll make you an offer. It you change the output that you've posted as an otherwise unusable graphic into "readily consumable" data with a CREATE TABLE and INSERT/VALUES so I have a source table to demonstrate code with, I'll show ya how to do it. If you have no clue as to what I'm talking about, study the article at the first link in my signature line below.
Be advised that I'm NOT going to import data from a bloody spreadsheet or file to help you. Please post the data in the form I've asked for.
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply