Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forums/sql-server-forum/)
-   -   Counting Grace Period days (http://www.go4expert.com/forums/counting-grace-period-days-t1208/)

Que 15Aug2006 08:29

Counting Grace Period days
 
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.

shabbir 15Aug2006 15:15

Re: Counting Grace Period days
 
A sample could be
Code: SQL

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.

Que 21Aug2006 22:54

Re: Counting Grace Period days
 
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,changedat e,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?


All times are GMT +5.5. The time now is 10:21.