Hi,

My original question was along the lines of I have a table that has three columns

Table Sales (HHArea, WeekEnding, SalesAmount)

There are 6 area's with 52 weeks of sales for...

Hi Jacob ,

Tried it out and its nearly there.

Here the test data I've been using

INSERT INTO [TblClientPOCV2]

([ClientID],[StartDate],[ApptTime],[Duration],[Frequency],[EndDate],[StaffID])

VALUES

(2528,'04/01/2016','09:00',20,1,'29/01/2016', 123),

(2528,'04/01/2016','10:00',30,2,'29/01/2016', 123),

(1111,'04/01/2016','10:00',35,3,'29/01/2016', 123),

(2528,'10/01/2106','17:00',15,2,'31/01/2106', 123),

It correctly processes the first three row, but...

Hi Jacob, you are correct in your thinking.

The output from your sample data would be...

222, '2016-01-07','13:00',30,222

222, '2016-01-14','13:00',30,222

222, '2016-01-21','13:00',30,222

222, '2016-01-29','13:00',30,222

222, '2016-01-08','13:00',30,222

222, '2016-01-22','13:00',30,222

Also max days between dates will be 28.

Hope this...

Hi Jacob, thanks for your interest, I did leave out the PK on my initial table definitions to try tokeep it as simple looking as possible.

One more correction ClientID should...

Hi Guys, thank you so muchagain.

Apologies to Lynn I thought all ms sql server databases had default dates as 'yyyy-mm-dd', also I should have mentioned there are only 2...

Thanks guys looks good..

Tried and appears to Work Brilliant !!!!:-D:-D:-D:-D

HI,

Here is the new test data in the correct format.

INSERT INTO [TblAppointments]

([ClientID],[ApptDate],[ApptTime],[StaffID],[Duration])

VALUES

(2528,'10/01/2016','10:00',22011063, 30),

(2528,'11/01/2016','10:00',22011063, 30),

(2528,'12/01/2016','10:00',22011063, 30),

(2528,'13/01/2016','10:00',22011063, 30),

(2528,'14/01/2016','10:00',22011063, 30),

(2528,'15/01/2016','10:00',22011063, 30),

(2528,'16/01/2016','10:00',22011063, 30),

(2528,'17/01/2016','10:00',22011063, 30),

(2528,'10/01/2016','15:00',22011063, 45),

(2528,'12/01/2016','15:00',22011063, 45),

(2528,'13/01/2016','15:00',22011063, 45),

(2528,'14/01/2016','15:00',22011063, 45),

(2528,'15/01/2016','15:00',22011063, 45),

(2528,'16/01/2016','15:00',22011063, 45),

(2528,'17/01/2016','15:00',22011063, 45),

(2528,'10/01/2016','10:00',22016486, 30),

(2528,'11/01/2016','10:00',22016486, 30),

(2528,'12/01/2016','10:00',22016486, 30),

(2528,'13/01/2016','10:00',22016486,...

Hi Folks;

Jeffs code ran brilliantly until changed my test data and added two appointment rows for the same client with same date and times data but with different StaffID's.

The result...

Hi Hugo,

Your right, but every row from the source table could and will have different dates , therefor I need to use a cursor to iterate over them! Which...

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