I have a table that I get on the 1st of each month, and it has all the jobs scheduled for the entire month:
SELECT [plannedstartDate]
,[plannedendDate]
,[machine]
,[Job]
,[Qty]
FROM ScheduleJobs
Day 1: The table has all the jobs scheduled for the entire month (Original Table)
Days 2: I get another table that has the entire month of the job scheduled minus day 1 data
Day 3: I get another table that has the entire month of the job scheduled minus the day 1 and day two data
Day 4: I get another table that has the entire month of the job scheduled minus the day 1, day 2, and day 3
And the same pattern goes for the rest of the month, day 5, day 6, until the last day of the month.
I want to write a query that allows me to update the Original table daily to keep only the previous day's data and replace the rest of its data with the subsequent current day's data.
Any suggestion on how to accomplish this will be greatly appreciated.
Thanks.