«

[SoniaArun]

select a.ecode,b.empname,a.fromdate,a.[to],
case when c.leavedetails=1 or c.leavedetails=2 then
(select datediff(dd,a.fromdate,a.[to])-(datediff(wk,a.fromdate,a.[to])*2)-
case when datepart(dw,a.fromdate)=1 then 1 else 0 end +
case when datepart(dw,a.[to])=1 then 1 else 0 end +1)
else
datediff(dd,a.fromdate,a.[to])+1
end as days
,e.prjname,d.leavetype

from leavedetails a join empdata b on a.ecode=b.empcode
join leaverelation c on c.leavedetails=a.id
join leavetype d on c.leavetype=d.id
join project e on c.prjid=e.id