Comments by iamFahhad (Top 13 by date)

Thanks.

I'm getting these errors when I executed your code:

Msg 139, Level 15, State 1, Procedure cat, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Procedure cat, Line 164
Must declare the scalar variable "@YourMonth".


This is your code:

alter proc cat
@DateSample DATETIME
as
begin
-- Number of working days between two dates
Declare @LevDays INT, @YourMonth TINYINT=3, @YourYear SMALLINT=2013
DECLARE @fromDate DATETIME, @toDate DATETIME , @ReqMonth SMALLINT, @ReqYear SMALLINT
SELECT @ReqMonth=MONTH(@DateSample),@ReqYear =YEAR(@DateSample)

SELECT @fromDate = CAST(@ReqYear AS VARCHAR(5))+'-'+CAST(@ReqMonth AS VARCHAR(2))+'-'+'01'
SELECT @toDate = DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(@ReqYear AS VARCHAR(5))+'-'+CAST(@ReqMonth AS VARCHAR(2))+'-'+'01' ))

SELECT @fromDate , @toDate

SELECT (DATEDIFF(DAY, @fromDate, @toDate) + 1)
- (DATEDIFF(WEEK, @fromDate, @toDate) * 2)
- (CASE WHEN DATENAME(weekday, @fromDate) = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(weekday, @toDate) = 'Saturday' THEN 1 ELSE 0 END)
-- No of Holidays also we need to deduct.



SELECT E.EmpID, T.LevDays
FROM EmployeeDtls E
INNER JOIN (SELECT EmpID, ISNULL(COUNT(LeaveDate),0)'LevDays'
FROM EmpTab WHERE MONTH(LeaveDate)=@YourMonth AND YEAR(LeaveDate)=@YourYear GROUP BY EmpID) T ON T.EmpID=E.EmpID

END

Can I use the below code in my function?


create function dbo.GetFirstWorkdayOfMonth(@Year INT, @Month INT)
returns DATETIME
as begin
declare @firstOfMonth VARCHAR(20)
SET @firstOfMonth = CAST(@Year AS VARCHAR(4)) + '-' + CAST(@Month AS VARCHAR) + '-01'

declare @currDate DATETIME
set @currDate = CAST(@firstOfMonth as DATETIME)

declare @weekday INT
set @weekday = DATEPART(weekday, @currdate)

-- 7 = saturday, 1 = sunday
while @weekday = 1 OR @weekday = 7
begin
set @currDate = DATEADD(DAY, 1, @currDate)
set @weekday = DATEPART(weekday, @currdate)
end

return @currdate
end

Hi Venkatesh,

Please check your eMail. I have already sent you my complete code along with the database table design and the Function - UDF. Please modify it to be able to accept the Month and Year (Ex: June, 2011).

Thanks.