Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Unanswered: process data containing start and enddates

    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?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    Apr 2003
    Posts
    191

    Re: process data containing start and enddates

    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

    Originally posted by blom0344
    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:2

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •