I am working on a intranet Project where I have a Procedure which gives the leave count of employee on a date.I want to add a field Employee_id in that Procedure which exist in two tables in Employee_mst and Leave_app_mst .
I want that if a person login by hsi/her employee_id than it will gave the leave record of that person how can i add the Employee_Id Field.the Procedure code is given Below.
ALTER PROCEDURE [dbo].[proc_getCalenderValue]
@Val varchar(20),
@Comp_id varchar(20)
AS
BEGIN
select @Val date, isnull(sum(isnull(leave,0)),0) count from
(
select
case
when
convert(datetime ,@Val,103)
between
convert(datetime ,from_date,103)
and
convert(datetime ,to_date,103)
then 1
else
0 end leave
from Leave_App_Mst
where close_date is not null and Leave_Status = 'GRANT'
--AND Comp_Id = @Comp_id
-- and datepart(month,from_date) = datepart(month,convert(datetime ,@Val,103))
-- and datepart(year,from_date) = datepart(year,convert(datetime ,@Val,103))
-- and datepart(month,to_date) = datepart(month,convert(datetime ,@Val,103))
-- and datepart(year,to_date) = datepart(year,convert(datetime ,@Val,103))
) x
/*select events,holiday_date,'1' is_holiday from holiday_details
where holiday_id in
(
select holiday_id from holiday_mst
where holiday_year = datepart(year,convert(datetime ,@Val,103))
and Comp_id = @Comp_id
)
and holiday_date = convert(datetime ,@Val,103); */
select events,holiday_date,'1' is_holiday from holiday_details a,holiday_mst b
where a.holiday_id = b.holiday_id and
holiday_date = convert(datetime ,@Val,103) and Comp_id = @Comp_id
END;