Week Number Before Certain Week

[Anonymous]

So we have an event that starts on 9/16/2018

We want to bucket a DATEFIELD by the week it falls in leading up to the event

For example, the week of 9/9/2018 (and any date that falls in that week) would = 1 because it is one week before the week of the event

We want to be able to build this all the out to 50+ weeks before event

Any suggestions?

[sunny_talwar]

Are you looking to generate these dates or are looking to flag them somehow?

[Anonymous]

Just looking to assign a number to it

For instance, the number should = the week before the event date like ..

1 week before date

2 weeks before date

3 weeks before date

etc ..

[sunny_talwar]

Is this something you need in the script? In a chart? I am not entirely sure I understand the requirement well enough to suggest anything right now... may be sample or sample data might help understand the issue better

[Anonymous]

I am trying to do it in the load script

Right now it is manual ... see below

IF(NewResponseDate >= '9/4/2016' AND NewResponseDate < '9/11/2016', 1, 0) as WeekBefore

And I have to build an IF statement with 50 lines if I want to go back 50 weeks

I think there should be an easier way, but maybe I am wrong