TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Akhter HUssain
720
1.3k
101.3k
Allowance and Deduction amount set in one row ,against each employee
Aug 29 2020 8:09 AM
Below is my query,I want Allowance and Deduction of each employee in one row,employee ID must not be repeated ,as you may see image Employee ID 30007 ,is being repeated.
SELECT
(M.Emp_ID)
as
ID , M.EMp_Name
as
Name
, M.EMP_Salary
as
Salary,D.Designation_Name
as
Designation,
CASE
WHEN
T.DA_Type_ID =
'Allowance'
THEN
Sum
(ASD.assign_amt)
ELSE
0
END
AS
Allowance,
CASE
WHEN
T.DA_Type_ID =
'Deduction'
THEN
Sum
(ASD.assign_amt)
ELSE
0
END
AS
Deduction,
isnull
(M.emp_OT,0)
as
emp_OT
,0
'txtpresent'
, 0
'txtabsent'
,0
'txtsalary'
,0
'lbOT_Amount'
,0
'txthour'
FROM
tbl_Employee_Master M
inner
join
tbl_designation D
on
D.Designation_ID=M.Designation_ID
inner
join
tbl_assign_Allowance_Deduction ASD
on
asd.Emp_ID=M.Emp_ID
inner
join
tbl_Type_allowance_Deduction T
on
T.DA_ID=ASD.DA_ID
group
by
M.Emp_ID,M.EMp_Name,M.EMP_Salary,D.Designation_Name,T.DA_Type_ID,M.emp_OT
image link
https://ibb.co/6rXt7nz
Reply
Answers (
4
)
How to arrange stored procedures in order in sqlyog
Having opposite function SQL