Pick a Start and end Date from table

[fkavanagh]

Hello,
I use a table where the fields are calculated_date, personnel_no, paycode and calc_hours among others. Hours for an employee would be inputed by date depending on what paycode they were asigned against. An individual could have several paycodes against them for each day.

What I am trying to do is when one particular paycode (250)occurs for the first time in the date range selected, I want it to be flagged as the start_date and when the next different paycode (except 305) occurs I want to flag the previous entry as the end date. And then the next time the paycode occurs repeat the procedure etc. This information needs to be grouped by personnal_no. We have 1700 employees.

I hope you are not as confused as I am. I would appreciate any comment weather it be a solution or "That Can't be Done" so I can look at other ways of getting this data.

Thanks a million,
Fergal

 

[Madawc]

For Crystal 8.5, I'd do a Running Total for start date, get minimum date but with formula check for code 250.

End date is trickier, you could do a formula that shows
Previous(your.date) when it meets the criterion.

Madawc Williams
East Anglia, Great Britain

 

[Fergalk]

Thanks Madawc for the reply,

It would not be much of an issue only I need to catch multiple start dates and end dates for the paycode over a 12 month, 6 month and 3 month period. The paycode is absence and it is counting the amount of individual absences an employee has.

I am looking at looping of some sort but with such a large data source I am afraid of knocking the database over while this is running.

Fergal

 

[lbass]

I think you could write a formula something like the following to create the flags:

if {table.code} = 250 and
{table.code} <> previous({table.code}) and
{table.code} <> next({table.code}) and
{table.employeeID} = next({table.employeeID}) then &quot;Start and End&quot; else

if {table.code} = 250 and
({table.employeeID} = previous({table.employeeID}) and
{table.code} <> previous({table.code}) or
{table.employeeID} <> previous({table.employeeID})) then &quot;Start&quot; else

if {table.code} = 250 and
{table.employeeID} = previous({table.employeeID}) and
{table.employeeID} = next({table.employeeID}) and
{table.code} = previous({table.code}) and
{table.code} <> next({table.code}) then &quot;End&quot;

Of course, this doesn't address how you want to use the flags.

-LB

 

[fkavanagh]

Thanks lbass,

What I am trying to do is list the start and end dates for each employee where they have an occurance of the paycode in the time frame of the report.

The layout of the report would be grouped by employee, start date beside end date beside paycode beside days, with more that one occurance listed underneath. The group footer will have a count of the start dates and a sum of the days and then a calculation called bradford factor.

I am now looking at trying to develop something within the database PL/SQL or a good view. Other comments welcome.

Fergal