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.