Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Red face Unanswered: Complex Procedure / Query

    PAYROLL
    PID|PNAME|STARTDATE|ENDDATE
    1|BATCH1|2004-01-01|2004-01-04
    2|BATCH2|2004-01-01|2004-01-04


    TIMEENTRY
    TID|PID|USERID|DATE|HOURS
    1|1|49|2004-01-01|7
    2|1|49|2004-01-02|8
    3|1|49|2004-01-03|8
    4|1|49|2004-01-04|6
    .
    .
    .
    11|1|50|2004-01-01|5
    12|1|50|2004-01-02|2
    13|1|50|2004-01-03|8
    14|1|50|2004-01-04|8

    21|2|49|2004-01-01|4
    22|2|49|2004-01-02|2
    23|2|49|2004-01-03|2
    24|2|49|2004-01-04|8
    .
    .
    .
    31|2|50|2004-01-01|8
    32|2|50|2004-01-02|8
    33|2|50|2004-01-03|8
    34|2|50|2004-01-04|8

    - contains timeentry(HOURS) for different users(USERID) FOR different payroll batches(PID) for different dates(DATE)

    the query/procedure should return data this way

    Userid |PID |2004-01-01 |2004-01-02 |2004-01-03 |2004-01-04 '<== HEADER ROW
    1 |49 |7 |8 |8 |6
    1 |50 |5 |2 |8 |8

    2 |49 |5 |2 |8 |8
    2 |50 |8 |8 |8 |8

    Im not sure how to start cause the dates in the headers are the start date and end date of the Payroll


    Any suggestions or help is appreciated

    thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hopefully you can avoid dynamic SQL on this one. A lot depends on how the data is going to be displayed. Are you piping it to Crystal Reports or some other reporting tool?

    Are the number of payroll periods fixed, or can a maximum number be set? For example; 12 for the year.

    You should try to design your report so that instead of dates as headers you display month numbers. You then return the starting month in your dataset and have your reporting tool concoct the headers based on the month numbers and the starting date. This way you don't have different headers every time you execute, which Crystal Reports would choke on as fast as George Bush eating a pretzel.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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