Counting Grace Period days

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

  1. Que

    Que New Member

    Joined:
    Aug 15, 2006
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Applications Programmer
    Location:
    N.C.
    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

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    A sample could be
    Code:
    BEGIN
    	DECLARE @GraceType varchar(10)
    	IF EXISTS (Select ... /*EmplChangeReason with a select SQL*/)
    	BEGIN
    		SET @GraceType = 'New Employee'
    		/* calculate the allowance based on your formula*/
    	END
    	ELSE
    	BEGIN
    		SET @GraceType = 'Updated Status'
    		/* calculate the allowance based on your formula*/
    	END
    END
    I hope it helps.
     
  3. Que

    Que New Member

    Joined:
    Aug 15, 2006
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Applications Programmer
    Location:
    N.C.
    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:

    EmplID,Inactive,Startdate,changereason_I,changedate,firstname,lastname.

    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

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice