dba123
Member
- Joined
- Jan 31, 2006
- Messages
- 8
- Programming Experience
- 5-10
How can I create a VB.NET function to run my query below that my SSRS report can run to retreive TotalPostingDays so I can show that in a textbox in my report? I need help creating the function in the Report properties of my SSRS report and not sure how to call this stored procedure to return TotalPostingDays.
ALTER PROCEDURE SSRS_Return_TotalPostingDays
AS
DECLARE @TotalDaysInMonth int,
@today datetime,
@TotalWeekendDays int,
@TotalHolidaysThisMonth int,
@TotalPostingDays int
SET @today =GETDATE()
-- TOTAL DAYS THIS MONTH
SET @TotalDaysInMonth =CASEWHENDatePart(mm,GetDate())IN(1,3,5,7,8,10,12)THEN
31
ELSE
DateDiff(day,GetDate(),DateAdd(mm, 1,GetDate()))
END
-- TOTAL HOLIDAYS THIS MONTH
SELECT @TotalHolidaysThisMonth =(SELECTCOUNT(*)FROM ReportingServer.dbo.Holidays
WHERE HolidayDate BETWEEN(DATEADD(DAY,-DATEPART(DAY, @today)+ 1, @today))
AND(DATEADD(DAY,-DATEPART(DAY, @today),DATEADD(MONTH, 1, @today))))
-- TOTAL # WEEKEND DAYS THIS MONTH
DECLARE @date DATETIME
SET @date ='20060101'
SELECT @TotalWeekendDays = 8 +
CASEWHENISDATE(CONVERT(CHAR(6), @date, 112)+'29')= 1 THEN
CASEWHENDATENAME(WEEKDAY,CONVERT(CHAR(6), @date, 112)+'01')IN('Saturday','Sunday')
THEN 1 ELSE 0 ENDELSE 0 END+
CASEWHENISDATE(CONVERT(CHAR(6), @date, 112)+'30')= 1 THEN
CASEWHENDATENAME(WEEKDAY,CONVERT(CHAR(6), @date, 112)+'02')IN('Saturday','Sunday')
THEN 1 ELSE 0 ENDELSE 0 END+
CASEWHENISDATE(CONVERT(CHAR(6), @date, 112)+'31')= 1 THEN
CASEWHENDATENAME(WEEKDAY,CONVERT(CHAR(6), @date, 112)+'03')IN('Saturday','Sunday')
THEN 1 ELSE 0 ENDELSE 0 END
SET @TotalPostingDays = @TotalDaysInMonth -(@TotalHolidaysThisMonth + @TotalWeekendDays)
RETURN @TotalPostingDays
ALTER PROCEDURE SSRS_Return_TotalPostingDays
AS
DECLARE @TotalDaysInMonth int,
@today datetime,
@TotalWeekendDays int,
@TotalHolidaysThisMonth int,
@TotalPostingDays int
SET @today =GETDATE()
-- TOTAL DAYS THIS MONTH
SET @TotalDaysInMonth =CASEWHENDatePart(mm,GetDate())IN(1,3,5,7,8,10,12)THEN
31
ELSE
DateDiff(day,GetDate(),DateAdd(mm, 1,GetDate()))
END
-- TOTAL HOLIDAYS THIS MONTH
SELECT @TotalHolidaysThisMonth =(SELECTCOUNT(*)FROM ReportingServer.dbo.Holidays
WHERE HolidayDate BETWEEN(DATEADD(DAY,-DATEPART(DAY, @today)+ 1, @today))
AND(DATEADD(DAY,-DATEPART(DAY, @today),DATEADD(MONTH, 1, @today))))
-- TOTAL # WEEKEND DAYS THIS MONTH
DECLARE @date DATETIME
SET @date ='20060101'
SELECT @TotalWeekendDays = 8 +
CASEWHENISDATE(CONVERT(CHAR(6), @date, 112)+'29')= 1 THEN
CASEWHENDATENAME(WEEKDAY,CONVERT(CHAR(6), @date, 112)+'01')IN('Saturday','Sunday')
THEN 1 ELSE 0 ENDELSE 0 END+
CASEWHENISDATE(CONVERT(CHAR(6), @date, 112)+'30')= 1 THEN
CASEWHENDATENAME(WEEKDAY,CONVERT(CHAR(6), @date, 112)+'02')IN('Saturday','Sunday')
THEN 1 ELSE 0 ENDELSE 0 END+
CASEWHENISDATE(CONVERT(CHAR(6), @date, 112)+'31')= 1 THEN
CASEWHENDATENAME(WEEKDAY,CONVERT(CHAR(6), @date, 112)+'03')IN('Saturday','Sunday')
THEN 1 ELSE 0 ENDELSE 0 END
SET @TotalPostingDays = @TotalDaysInMonth -(@TotalHolidaysThisMonth + @TotalWeekendDays)
RETURN @TotalPostingDays