Week No / Dates Query

[Djbell]

Hi All

I have a table that looks like the following.

Name |ID|DATE |PAYCODE|HOURS|MONEY
J DOE | 3|01/01/08 |Hours |7.25 |NULL
J DOE | 3|01/01/08 |Money |NULL |26.75
J DOE | 3|01/02/08 |Hours |7.5 |NULL
J DOE | 3|01/02/08 |Money |NULL |28.95
J DOE | 3|01/03/08 |Hours |7.25 |NULL
J DOE | 3|02/03/08 |Hours |7.25 |NULL
J DOE | 3|20/03/08 |Hours |7.25 |NULL
J DOE | 3|02/03/08 |Money |NULL |53.50
J DOE | 3|20/03/08 |Money |NULL |26.75

This is how I would like the query to display the information.

Week No|Name |ID|DATE |AHours |HOURS|MONEY
1 |J DOE | 3|01/01/08 |8 |7.25 |26.75
5 |J DOE | 3|01/02/08 |8 |7.5 |28.95
9 |J DOE | 3|01/03/08 |8 |7.25 |26.75
9 |J DOE | 3|02/03/08 |8 |7.25 |26.75
12 |J DOE | 3|20/03/08 |8 |7.25 |26.75

Ok to explain

1.New column "Week No", I would like the query to work out the week no from the date.
2.New Column "AHours", Any hours value that has a decimal point will be rounded up. (I tried the round function but it only rounds up values 0.5 and above, values below 0.5 gets rounded down, I need all values rounded up.
3.I want the Paycodes "Hours" and "Money" combined so they become one record.
4. Ok, the complicated part, if the users has mutiple hours in one week, then the money value is summed and stored in the last date. see example.

J DOE | 3|01/03/08 |Hours |7.25 |NULL
J DOE | 3|02/03/08 |Hours |7.25 |NULL
J DOE | 3|02/03/08 |Money |NULL |53.50

What I want is to add up the hours in that week and divide the money by that value, then times that value by the hours in that particular day, so I would get the following.
9 |J DOE | 3|01/03/08 |8 |7.25 |26.75
9 |J DOE | 3|02/03/08 |8 |7.25 |26.75

I am not sure where to start as this is a rather complicated query for me to work on, any and all help is appreciated.

Regards

Djbell

 

[bborissov]

SELECT DatePart(wk, DATE) AS Week,
       Name,
       Id,
       Date,
       MAX(CEILING(Hours)) AS AllHours,
       MAX(Hours) AS Hours,
       MAX(Money) AS Money
FROM YourTable
GROUP BY DatePart(wk, DATE),
         Name,
         Id,
         Date

Not tested at all

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Djbell]

Hi Borislav

Thanks for the reply, I did manage to get to this point on my own, although my query is about 3 times the size of yours and I also had to use the same table twice with an alias. Needless to say I have changed to your code as it is much more efficient.

Cheers

Djbell