Thanks Jeff, that worked well.

Can I ask you to explain how it works, as reading it does nto make a lot of sense.

Kind regards.

I have developed Scotts query using a loop but still not getting the resutls I need.  I have supplied better data for testing below...

CREATE TABLE Client( ID int, ClienID int,...

• This reply was modified 2 weeks, 2 days ago by  Tallboy.
• This reply was modified 2 weeks, 2 days ago by  Tallboy.

Hi Scott,

My mistake I didnt explain it has to run for all months and years after the July too!

I tired you query and it works (had to rearrange the dateadd...

• This reply was modified 2 weeks, 2 days ago by  Tallboy.
• This reply was modified 2 weeks, 2 days ago by  Tallboy.

So I tried this suggestion and it worked perfectly, except for the first row for each Client which is one less than the row above skewing all the data.

I tired...

Looks great Thank you!

Hi Vedat,

That looks great thank you, I will try it out, but is it limited to 4 conditions? as there could be many more per person!

Hi Jonathan,

Your query worked a treat, thank you!

Hi JOnathon,

I see my mistake I should have used Partion by StartDate!

Must have been sleeping!

Is there any simpler way of doing it tho or is that the best way (again...

Hi Jeff,

Good questions Jeff, its in development so not sure exactly what to do with results yet?

If Client has only one row then leave it out of the results set,...

SURE, I mean to do and forgot, here it is now...

tblClientService (ID, ClientID, StartDate, Status (1/0), ServiceType(1-100), WklyHours)

INSERT INTO tblClientServiceVALUES (100, '2021-01-01', 1, 21, 5.0);

INSERT INTO tblClientService VALUES (100, '2021-02-22',...

• This reply was modified 3 years ago by  Tallboy.

Thanks for your quick reply at the moment is on sql server 2008r2, so it doesnt have LAG I think...

Good to know, thank  you very much...

I tried looking in the tempdb database, but can see the #TmpTable. Can it be veiwed through SSMS ?

Also do I need to create...

Hi Thank you,

I was thinking that temp tables may work alright, what is the diff between #Tmp Table and ##TmpTable?

kind regards

Hi,
I replaced the recursive SELECT with a WHILE loop (which I don't like doing so if there's any other way, please let me know) and it all worked!!!...

Hi,
Jonathans answer worked for most of my query except recursive SELECT.. 

I'm now getting an error

Msg 2770, Level 16, State 1, Procedure GetMonthlyApptCrosstab, Line 33