-1

I have very limited knowledge of creating SQL scripts for SQL Server. I need to create a pretty simple script that inserts downtime records to be posted for future dates within a database. We have daily maintenance so I'll need to post a notice for every individual day from mon-sat. There is a fixed time period of when it occurs midnight to 2AM. I'd like to create all the entries necessary for a year.

I also need to create a second script that posts notices about downtime for 1 day weekly (Sunday) which has a different backup time frame. The time frame of this weekly backup is midnight to 7AM. I'd like to create all the entries necessary for a year for this also.

The basic format of the database table I need to insert is noticetime, begintime, endtime, msgtext. Table name is downtime. All date fields except for the msgtxt which is a a text field.

How do I create a script that would increment the dates to valid days that meet the criteria as well and terminates when a specific day has been reached? Same for the weekly insert?

Mike
  • 419
  • 1
  • 6
  • 22
  • I've updated my question. Sorry I guess I assumed people could read what I needed and got that out of it. I thought the question was quite detailed actually. – Mike Nov 03 '14 at 19:26
  • Here is the challenge. We can't see your screen, we don't know what your table structures are like and we have no idea of what your project is trying to do. Things like "How do I create a script that would increment the dates to valid days that meet the criteria as well and terminates when a specific day has been reached?" don't mean anything without the details of what that means. What is the criteria? What would be the rules for a specific day? – Sean Lange Nov 03 '14 at 19:31
  • Do you want to **INSERT** new rows (then you need to use the `INSERT` statement), or do you want to **UPDATE** existing rows (with the `UPDATE` statement)? You're not entirely clear on this - once you talk about inserting, then you mentioned *updating* those columns .... – marc_s Nov 03 '14 at 19:37
  • I've removed the word "update". Its inserting these records. As for logic there is no special logic. I need to enter a record for every day (except sunday) for daily backup and a sunday record. There are no special rules. – Mike Nov 03 '14 at 20:31
  • Please vote this up if you found this helpful. – Mike May 02 '16 at 15:48

1 Answers1

1

if I understood you correctly you would need something like this, see pseudo code below haven't tested it though:

declare 
    @l_maxdate datetime
   ,@l_date datetime

select @l_date = getdate(), @l_maxdate = '2014-12-31'

while @date < @l_maxdate
begin
    if datepart(dw,@l_date) = 1 -- sunday
        insert .... values .... using date variable

    if datepart(dw,@l_date) > 1 -- moday - saturday
        insert .... values .... using date variable

    select @l_date = dateadd(dd, 1, @l_date)
end
Tim
  • 26
  • 2