If anyone is interested. I solved it by creating the following query. I will loop this through each quarter and populate another table.

with res

as

(

select

...

I gather its a marketing tool.

So you get a group of customers who bought in Year 2010 Q1 and you track the same groups spent through the years.

2010 Q2 -...

Great. Will give that a try and report back

many thanks

Scott

Yes. Confirmed. Was a datatype I was capturing to take care of zeros. Was set to int therefore 0.9 came out as 0.

Had we worried for a minute! Thanks again...

I had one of those thoughts dropping off last night. I think maybe it might be due to the datatype.

I will try it out and if not then post my...

This function has been working 100% in production for some time. Now I need to factor down as well i.e. 0.9

Currently this actually makes the result 0.

I will be looking...

Hi Jeff. No I currently do not have that.

Are you suggesting I need to do this to progress this as a solution?

I'll be back on this tomorrow so will start...

No. its lost some days.

201249102012-12-03 00:00:002012-12-09 00:00:001212

201250102012-12-10 00:00:002012-12-16 00:00:001212

201251202012-12-17 00:00:002012-12-23 00:00:001212 --Stops 23

201252102011-12-26 00:00:002012-01-01 00:00:00121 -- Starts 26

20131102012-12-31 00:00:002013-01-06 00:00:00121

Fixed. It was due to me not using ISOWK in the datepart function. Excellant.

Let me check all details and I'll respond

Thats great. Thanks for your input. Here are the results.

I am using a Monday as the first day. Just going to try and work that out from your code.

Also...

Thanks for the additional information. It's helpful to receive and be able to look further into the process.

Many thanks scott.

Thanks so much. Works perfectly. Scott

Wonderful. That answers that

Many thanks Scott

Yes..I only want to add another record if the specific code exists. This is due to us sending out a book but the pack actually contains two books. So I...

many thanks. thats a great help

Scott