output should be...

Output for each employee would be 1 row for each 4 week period as follows;

PersonnelNo, 4WeekEnd, TotalWorkedHours, TotalContractHours

1234, 1/10/14, 42.75, 82.00

1234, 2/9/14, 34.50, 82.00

4343, 1/10/14, 34.00, 74.00

I think it should be something like

SELECT PersNo, 4WeekEnd, TotalWorkedHours, TotalContractHours

From

WITH TblWorkedHours @WeekEnd

(Select

PersNo,

Max(WeekEnding) As 4 WeekEnd,

Sum(WorkedHours) As TotalWOrkedHours,

...

Just for clarity its not lunar month, its a four week month...

Hi guys, thanks for all those kind words.

I suppose I am not articulating what the problem is, so here goes again.

I have a table with PersNo, WeekEnding, WorkedHours and...

That would be great...

Sample data would be

ID, PersonnelNo,WeekEnding, WorkedHours, ContractHours

4, 1234, 1/10/14, 10.50, 20.00

3, 1234, 23/9/14, 11.00, 20.00

2, 1234, 16/9/14, 10.75,...

Hi,

Thanks for your reply, while I respect your opinion, the reasons you give not to use a View equally apply to Stored Proc. surely if a SP got dropped,...

Hi,

I have my stored procedure working alright, thanks to everyone who contributed, but then I tried it a different way, I created a number of different views and joined them...

Hi David,

Thank you so much for your answer, it looks like it will fit the bill alright

I just have one amendment I cant be sure that the dates in the...

Hi Guys, Thanks for all the excellent idea's.

On further thought and examination of my needs , I think I need a stored procedure in which I pass in a...

Hi Guys, thanks for your reply, it looks like something that would work the bill.

Everything was going grand until I read

DENSE_RANK() OVER( PARTITION BY WeekEndingDate ORDER BY customersID) -...

Hi MM,

Thansks for your suggestions.

Thats what I was thinking myself a calendar table with just 2 columns something like...

MonthID, WeekEndate

1 , ...

Hi,

Thanks for your prompt reply.

I think the data is very simple really.

ClientID's are Integers

WeekEndingDate is a date , there is 52 weekly dates for each Client.

WorkedHours is a Double

I want...

Sorry sturner,

perhaps i have not explained it enough, the task id and the client id must go into the same field in the hours worked table, because staff are...

Hi L' Eomot Inversé,

I agree, whenever I see null entries in a table I think 'poor design!'

A null tells you nothing, take a simle example like tblGender , with Male...

Thats what I am asking is it normal practice.

There would be more than 1 task/organisation id to find as the task that the staff member is being paid for, this...