1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Counting Grace Period days

Discussion in 'SQL Server' started by Que, Aug 15, 2006.

  1. Que

    Que New Member

    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.
  2. shabbir

    shabbir Administrator Staff Member

    A sample could be
    	DECLARE @GraceType varchar(10)
    	IF EXISTS (Select ... /*EmplChangeReason with a select SQL*/)
    		SET @GraceType = 'New Employee'
    		/* calculate the allowance based on your formula*/
    		SET @GraceType = 'Updated Status'
    		/* calculate the allowance based on your formula*/
    I hope it helps.
  3. Que

    Que New Member

    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
    FROM GraceInfo
    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?

Share This Page