I am trying to process data coming from an absence database. Basically the only input is a file with 3 fields: Employeenumber,startdate, enddate.

The requirement is to calculate somehow:

1. The number of days between the the dates (easy)
2. The number of days for each month, generating a child record for each month with the number of days, like:

Input:

TBL 11-19-2003 01-26-2004

Output:

Master-record:

TBL 11-19-2003 69

Children:

TBL 11-19-2003 2003 NOV 12
TBL 11-19-2003 2003 DEC 31
TBL 11-19-2003 2004 JAN 26

I do not see a solution with just SQL , but would a stored prodecure be able to handle such (seeming simple) problem?

Hi,

did you consider using a support table like this:

YEAR MONTH DAYS
...
2003 1 31
2003 2 28
2003 3 31
2003 4 30
...
2003 12 31
...

I believe this would enable you to do a couple of joins and do the days calculations.

Next I would try and create a table function like shown above.

Johann