Counting the number days between a start to an end date

[QueTech]

Hi,

I am pulling data from a cashe database on Windows XP workstation using Crystal 10.

I am modifying a productivity report ran by my company on a monthly basis. I am tasked with granting new staff and staff moving to new positions a grace period. The grace period for new staff is 2 weeks and the grace period for moving staff is 1 week. The grace period will begin on there employment start date or the start date of there new position. I need to count from there start date to the end of the grace period taking into consideration holidays, weekends and were the start date is in relation to the month the date starts in. For example, if the start date is 3/22/2006 and this is a new staff person then on the March productivity this staff should get credit for 8 days (64 hrs), also when the productivity is run in April this same staff should get credit for the remaining 6 days (48 hrs). I would determine whether the staff is a new staff by checking the start date. if there is no previous(startdate). I will determine a moved staff similarly, if not isnull(next(startdate) then moved staff.

I am unsure how to count the dates and take into consideration if days will roll over from a previous month. Any help will be great.

 

[synapsevampire]

I suggest building out a period table for this, I have sample code for SQL Server here:

faq767-4532

This is common to data warehouses, and would allow you to populate a table with all dates, then join that table to your data, and palce the date criteria against the period table, with the additions of where day type = "B", etc.

Anyway, for additional help, I would suggest posting example data and expected output rather than trying to describe it.

-k

 

[QueTech]

Here's an example of what I am working with. In this example the productivity report is ran at the beginning of each month.

Staff 1
T.Brown - Start date = 3/22/2006

Staff 2
J.Smith - Start dates = 1/01/2005, 3/22/2006

Staff 3
D.Moore - Start date = 3/13/2006

Staff 1 should be given 14 days of grace period. 8 days in March and 5 days in April.

Staff 2 should be given 7 days of grace period. all in the month of March.

Staff 3 should be given 14 days of grace period. All in the month of March.

I think using a period table will be helpful.

 

[IdoMillet]

Yes, with a period table you would:
1. add it to the report but create no join. Instead add a record selection formula such as:

Period.Calendar_date >= Emp.Start_Date AND
Period.Calendar_date <= @Start_Date_Plus_Grace_Period

2. Now, if you group on employee and within employee by Month(Period.Calendar_Date) you can simply count any non-null field to know how many day credits that employee should have for that month.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[QueTech]

The Period table setup without a problem.
Thank You

 

[QueTech]

The Period Table works in test report,however, I derive my startdate from another report. I run a sub report in my main report (Productivity) to get ther startdate. The Producitivity report has 6 groupings. I am trying to add the additional groups. Is there another way I can use the period table?

 

[QueTech]

I have my startdate for example StaffID 1 startdate= 3/23/2006. StaffID 1 is a new staff that will get 14 days grace.

These values are stored in a shared variable from a sub-report. Can I still use the period table. Or is there another way for me to count the number days to the end of the grace period while skipping weekends and holiday. And also carrying over any remaining day to the next month if neccesary.

 

[QueTech]

My queston now is can I use a sub-report variable in my record selection? Startdate and Startdate_plus_grace.