0

actually i need the distinct values from multiple union statements but i do not getting this.

My result which i get

here emp_id of 578 has weekend and absent in different rows which i need in single row.

my code goes like this.

 select [Employee Name],emp_id,holiDAY, leave,absent,weekend from ( SELECT *FROM ( select view_emp_info.emp_fullname+' '+'('+''+CONVERT(VARCHAR(50), (view_emp_info.EMP_ID)) +''+ ')' as [Employee Name],view_emp_info.emp_id,tbl_emp_forceduty.Holiday_Name as 'HOLIDAY' ,''AS LEAVE,'' AS ABSENT,'' AS [WEEKEND] from view_emp_Info inner join tbl_emp_forceduty on view_emp_info.emp_id=tbl_emp_forceduty.emp_id where ondate=@date and view_emp_info.BRANCH_NAME=@branch and view_emp_info.emp_id not in (Select emp_id from tbl_emp_attn_log where tdate=@date) and view_emp_info.status_id=1 union select view_emp_info.emp_fullname+' '+'('+ ''+CONVERT(VARCHAR(50),view_emp_info.EMP_ID) +''+ ')' as [Employee Name],view_emp_info.emp_id,'' AS HOLIDAY ,isnull(tbl_org_leave_log.leavetype,' ') 'LEAVE', '' AS 'ABSENT',''AS 'WEEKEND' from view_emp_info inner join tbl_org_leave_log on view_emp_info.emp_id=tbl_org_leave_log.emp_id where leave_date=@date and view_emp_info.BRANCH_NAME=@branch and view_emp_info.emp_id not in (Select emp_id from tbl_emp_attn_log where tdate=@date) and tbl_org_leave_log.Taken='0.5' and view_emp_info.status_id=1 union select view_emp_info.emp_fullname+' '+'('+ ''+CONVERT(VARCHAR(50),view_emp_info.EMP_ID) +''+ ')' as [Employee Name],view_emp_info.emp_id,'' AS HOLIDAY ,'' as [LEAVE] ,'' AS 'ABSENT','Weekend'AS 'WEEKEND' from view_emp_info inner join tbl_org_weekend on view_emp_info.emp_id=tbl_org_weekend.emp_id or tbl_org_weekend.emp_id=0 where view_emp_info.BRANCH_NAME=@branch and days=datepart(weekday,@date) and is_weekend=1 and view_emp_info.emp_id not in (Select emp_id from tbl_emp_attn_log where tdate=@date) UNION select view_emp_info.emp_fullname+' '+'('+ ''+CONVERT(VARCHAR(50),view_emp_info.EMP_ID) +''+ ')' as [Employee Name], view_emp_info.emp_id,'' AS HOLIDAY, '' AS 'LEAVE','Absent' as 'ABSENT',''AS 'WEEKEND' from view_emp_info inner join tbl_emp_attn_log on view_emp_info.emp_id!=tbl_emp_attn_log.emp_id where view_emp_info.BRANCH_NAME=@branch and view_emp_info.emp_id not in (Select emp_id from tbl_emp_attn_log where tdate=@date) ) T1 where EMP_ID IN (SELECT EMP_ID FROM VIEW_EMP_INFO WHERE BRANCH_NAME=@BRANCH) )t2 
0

1 Answer 1

1

Just group by employee_name, employee_id, holiday, leave. Then apply some aggregate functions on the results like max(absent), max(weekend)

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.