Hi, Sean solution is correct but I have failed to make it clear what I am looking for. Its the old adage "Thats what I asked for, but its not...

Yes Sean, my bad the test data dates should have been...

INSERT INTO [TblClientPOCV2]([ClientID],[StartDate],[ApptTime],[Duration],[Frequency],[EndDate],[StaffID]

VALUES

(2528,'2015-12-28','10:00',30,1,'2016-01-24',2200176)

(2528,'2015-12-28','15:00',45,1,'2016-01-24',2200176)

Each 28 days apart.

Hi Sean, Thank you for your solutions looks great will test later and let you...

Hi, Yes in this case Frequency is 1 which means a date for every date between the two dates and the number of new rows inserted into TblAppointment should be...

Here is my View VwAppointments1 joining TblAppointments to TblStaff.

SELECT dbo.TblAppointments.ClientID, dbo.TblAppointments.ApptDate, dbo.TblAppointments.ApptTime,

...

Hi Jeff,

Well I initially wanted to get the problem as simple as possible until 'we' got the code working, which you did.

Thats why my Appointments table had only 5 fields...

Hi,

Just ran Jeff's code! Worked Brilliantly...Genius....Thank you so much! :-):-):-):-)

Also I should have added that ClientID, ApptDate, ApptTime, StaffID are a unique index, therefor no 2 staff will be on...

H Guys, thank you for all the posts. Its great support to know that there all these experts available on this forum!

Well done, correcting the spelling error of ApptTime has...

Anyone ???

Doh!!!

I see now I need to change the final CTE to a temporary table so the dynamic sql is ab leto use it.

While the crosstab may be quicker than PIVOT...

Hi Luis, I am sure there is nothing wrong with it, but I am a SQL beginner and am finding dynamic sql quite confusing. I need something very simple...

HERE is my latest attempt to solve the problem...

while it produce a correct list of dates on my @cols string. I need the dates to be wrapped with singel...

Hi Guys, I have the above code running well now but have run into another problem with a pivot table

,AllData AS

(

SELECT A.Dates , C.ApptTime, C.Details

FROM DailyDates A

LEFT...

Hi Luis,

Thanks for your prompt reply.

I know your code works as I have tested it out before. I know it creates a cte of dates but would you mind explaining...

Hi SSCarpal,

I love CTE they make perfect sense to me and I have all of my sp done that way already...so there's no going back...

But thanks for commenting anyways...

Hi, As promised here is the code I have prepared to give me the 3 tally tables I may need.

The first Tally example uses a number of cte's that (as...