Dynamics Community Forum Thread Details

Extending dates in dbo.calendar

I think our year/date range has reached its limit, how do I insert more dates or do I have to re-create dbo.calendar? if so I'm fairly new with this any inputs would be really highly appreciated.
We have an SSRS "Productivity Summary Report" the values on "HRS_PAID" has returned a value of '0' since 2021 started.
There's a custom report created by the previous IT that's looking up to the table dbo.CSGAUCalendar that performs a calculation against function dbo.CSGAUWorkingdays, I think it's returning '0' value because it cannot find YR2021 in dbo.CSGAUCalendar.

[dbo].[CSGAUWorkingDays]
(
@startDate SMALLDATETIME,
@endDate SMALLDATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @result INT
SELECT @result = COUNT(*)
FROM dbo.CSGAUCalendar
WHERE dt >= @startDate
AND dt <= @endDate
AND isWorkDay = 1;
RETURN @result
END

[dbo].[CSGAUCalendar] (
[dt] SMALLDATETIME NOT NULL,
[isWeekDay] AS (CONVERT([bit],case when datepart(weekday,[dt])=(7) OR datepart(weekday,[dt])=(1) then (0) else (1) end,0)),
[isWorkDay] BIT DEFAULT ((1)) NULL,
PRIMARY KEY CLUSTERED ([dt] ASC)

Categories:

Microsoft Dynamics GP

Quick Links