huh, good question
it is little more complex then that; I have more then 400.000 events in the database and I have to sort it out not only by date, but also by customer, distribution channel and product as well
also, it might happen that you have campaign just weekdays and not over the weekend, so dates might not be consequtive and my idea was to try to find gap between campaigns by playing with number of days between two ads
Great thinking, this was not entirely what I wanted but I ended up with following solution which proved to be good enough:
If(Dates=Peek(Dates)+1 OR Dates=Peek(Dates) OR Dates=Peek(Dates)+2 OR Dates=Peek(Dates) + 3, Peek(CampaignID), ProductName & ' ' & Dates ) as CampaignID
Resident Old Order by AdvertiserName, ProductName, Dates asc;
Drop table Old;
Now, the thing is that we need to somehow recognize continuous campaign and I do this by my IF statement where I look for all ads which were aired either today, yesterday or up to 3 days before (there are some troubles with this approach but they can also be resolved).
Then, if codintions are met, then I take vale from Campaign ID from the previous row and copy it to current row.
If we do not meet criteria, then it means that this is first ad in our new campaign and we create unique ID by using product name which is advertised and first date of ad airing (also not fool proof but good enough).
(this is fun)
p.s. note that special problem is sorting of my data, and for this I load my data into memory and reload the table as resident in proper order so I have aligned my rows in sequence which best follows my intentions (loading from file does not allow sorting so I had to reload from already resident table)