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

thanksHopefully 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.