What i exactly wanted is:
If the StartDate is say '7/15/2007', EndDate is '8/20/2009' and the ClientDate is say '9/12/2007' (the day is Wednesday on 12 Sept) then i need to calculate the total no. of Wednesdays from 7/15/2007 to 12/31/2007.
If the ClientDate is anything in the year 2008 say 1/12/2008 (i.e. Saturday), then we need calculate all the Saturdays in 2008 i.e. from 1st Jan to 31st Dec,
similarly if the ClientDate is any in the year 2009 say it is 5/25/2009 (Monday), then we need to calculate the total no. of Mondays from 1 Jan 2009 upto 8/20/2009.
For that i have created following function:
CREATE FUNCTION [dbo].[GetNumberofWeekDaysYearly](@StartDate DATETIME, @EndDate DATETIME, @ClientDate DATETIME)
RETURNS SMALLINT
AS
BEGIN
DECLARE @strMonth VARCHAR(15)
DECLARE @strYear VARCHAR(15)
DECLARE @strwday VARCHAR(15)
SET @strMonth = CAST(MONTH(@ClientDate) AS VARCHAR(15))
SET @strYear = CAST(YEAR(@ClientDate) AS VARCHAR(15))
SET @strwday = DATENAME(dw,@ClientDate)
/* Initialize the variable with the start date */
DECLARE @i SMALLINT
IF @strMonth = CAST(MONTH(@StartDate) AS VARCHAR(15)) AND @strYear = YEAR(@StartDate)
BEGIN
SET @i = DAY(@StartDate)
END
ELSE
BEGIN
SET @i = 1
END
DECLARE @LastDate SMALLINT
SET @LastDate = 32
/* Initialize the variable to the start of month */
DECLARE @j-2 SMALLINT
IF CAST(MONTH(@StartDate) AS VARCHAR(15)) > 1 and @strYear = YEAR(@StartDate)
BEGIN
SET @j-2 = MONTH(@StartDate)
END
ELSE
BEGIN
SET @j-2 = 1
END
/* Initialize the variable to End of month */
DECLARE @LastMonth SMALLINT
IF CAST(MONTH(@EndDate) as VARCHAR(15)) < 12 AND @strYear = YEAR(@EndDate)
BEGIN
SET @LastMonth = MONTH(@EndDate) + 1
END
ELSE
BEGIN
SET @LastMonth = 13
END
DECLARE @dte VARCHAR(10)
DECLARE @TestDate VARCHAR(10)
DECLARE @iNumDays SMALLINT
SET @iNumDays = 0
WHILE @j-2 < @LastMonth
BEGIN
IF(@j = MONTH(@EndDate) AND @strYear = YEAR(@EndDate))
BEGIN
SET @LastDate = DAY(@EndDate) + 1
END
WHILE @i < @LastDate
BEGIN
SET @TestDate = CAST(@j AS VARCHAR(2)) + '/' + CAST(@i AS VARCHAR(2)) + '/' + @strYear
IF ISDATE(@TestDate) = 1
BEGIN
IF (DATENAME(dw, @TestDate) = @strwday)
BEGIN
SET @iNumDays = @iNumDays + 1
END
END
SET @i = @i+1
End
SET @i = 1
END
RETURN @iNumDays
END