I am rewriting my companies Productivity Report which was for the most part done by hand. The report is almost completely done in CR10 using the InterSystems ODBC driver with one main report and two sub-reports. I using SQL Server 2005
The last pieces of data I need to display on this report is the staffs Medical Leave Allows and Employment Grace Period. I have a Period Table in my data base,which will be helpful because I need to count the number days from the start date/Medical Leave date to the end of the Grace Period/Medical Leave Enddate taking into consideration the weekends and holidays.
Medical Leave Allowance:
Medical Leave Date to Medical Leave Enddate (not a problem).
Employee Grace Period :
If GracePeriod.Empl Change reason = "New Employee" then Date Allowance = 14 + (((Dateadd(startdate)-Period table.active weekends)-Period Table.active holidays)
else if GracePeriod.Empl Change reason ="Updated Status" Date Allowance = 7 + (((Dateadd(startdate)-Period table.active weekends)-Period Table.active holidays)
I need to provide the staff person with productivity credit for each working date he/she are allowed. I would like to write a view or stored procedure that would run against a staffs ID and display the Grace Period Enddate but I'm not sure about my syntax or use of the period table. Any guidancde would be appreciated.
Counting Grace Period days
A sample could be
I feel like I am making this harder than it should be. So, let me provide more information so that it is clearer to anyone reading.
I have a view (GraceInfo) that was written by the DB Vendor. GraceInfo has the following fields:
This is what I would right in a query from your instructions:
SELECT Declare @GraceType varchar(10), @X interger@Y interger,CHANGEREASON_I AS Expr1
if Expr1 = "New Employee" then X := 14
else if Expr1 = "Update Status" then X:= 7;
For Y = 1 to X
If (Activityday <> "0") and (activityVac <> "0")
then GraceDate := dateadd(1 +d, startdate), Y=Y+1;
Can anyone help with the syntax?