Create a copy of a month's data, and inserting into next month while respecting Days of the week.
Posted on 2011-02-24
I'm looking for a bit of guidance, if not some code. Here is the premise I'm trying to work out.
I have a transaction table in SQL that holds a StartDate. I want to offer users of my webapp a button on a monthly calendar that says: "Copy Last Month". When they click that button, I want to run a script that examines the current month the person is looking at (passed in parameter), selects all the records from the immediately previous month's data, and inserts new records into the current month respecting these rules:
1. All records from the previous month will be copied based on the Day Of The Week rather than on the actual date - so, dateadd(m,startdate,1) won't do it.
2. If an item toward the end of a month would be placed outside of the "target" month, then it will be ignored.
3. [I have more logic for whether to insert the record, but that is not relevant to this question]
So, let's say Tuesday February 8, 2011 has an item in it, and the user wants to copy that item to March. I would expect this magic query to Insert a new item into Tuesday March 8, 2011. But, that's too easy since Feb usually has 28 days and therefore March has matching Dates/Days.
What about something like Tuesday, April 12, 2011. I would expect this query to create an item for Tuesday, May 10 2011.
This might be a bit insane, but I'm locked in on this concept and want to play it out a bit. Any thoughts?