Forum Moderators: open
I'm looking for some direction. I have been put to the task of creating a scheduler. However many of the items need to be consistently re-created.
I need to perform a select to find out until when the items were added till so I can use that date as a reference. But this can't be done based on when the last record in the table is because there is a chance that there are items that have been added manually in the future. I can say though that the days that need to be re-created will always have more then lets say 5 items on that day. This should help differentiate a day that needs it's items created and a day that doesn't.
Once I know the date range how do I go about making a condition to take those variables and use them in a INSERT / SELECT statement?
BTW the solution can only use MSSQL for this job no external script and I cannot alter the table either.
Thanks in advance!
Nick
I was thinking of using a count. So basically SELECT count(*), startDate FROM table1 WHERE startDate > DATEADD(DAY, -7, GETDATE()) GROUP BY startDate ORDER BY startDate DESC
I'm not aware of anyway to count the results within the same query so how would I do a "foreach" so to speak on the results to find the lastest one with more than X amount of items in that day.
I know you can use WHILE but I don't know how to go through the results one by one as you would in php with foreach in an array.
With the above solved I can find the lastDate and then do the insert / select from there.
Another issue is that unless I'm missing something, StartDate is NOT the date the task was added. By its name, it sounds like the date when the task will start.
Perhaps you should start again, gives us the schema for the table (or at least just the relevant fields), and describe exactly what you're trying to do. Maybe with some sample data as well.
