August 21, 2020 at 7:26 pm
I need to run a stored procedure that will update all records date field (meetingDate) to the following week.
For Example:
DECLARE @myDate DATETIME
DECLARE @dayValue int
SET @dayValue = 7
/* If any records @dayValue = 7 (Monday) then update meetingDate value to next Mondays Date
If any records @dayValue = 8 (Tuesday) then update meetingDate value to next Tuesdays Date
If any records @dayValue = 9 (Wednesday) then update meetingDate value to next Wednesdays Date ETC......
*/
DECLARE @meetingDate datetime
SET @myDate = Getdate()
SET @meetingDate = (SELECT DATEADD([day], ((DATEDIFF([day], '19000108', @myDate) / 7) * 7) + @dayValue,'19000108'))
So basically using the example from above we need iterate through each record, find what the @dayValue is from column dayValue and then update the meeting date for next week's date. I don't want to use the current meetingDate value as it may change. I want to use a numeric value from @dayValue as the baseline.
This stored procedure runs on Saturday mornings and there will never be more than 10 records to update.
Here is the table
CREATE TABLE dbo.MeetingDate (
MeetingInfo nvarchar(max) NOT NULL,
dayValue int NOT NULL,
meetingDate SMALLDATETIME NOT NULL
);
GO
INSERT dbo.MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Monday Meeting 1 information', 7, '08-03-2020');
INSERT dbo.MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Monday Meeting 2 information', 7, '08-10-2020');
INSERT dbo.MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Tuesday Meeting 1 information', 8, '08-11-2020');
INSERT dbo.MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Wednesday Meeting 1 information', 9, '08-19-2020');
INSERT dbo.MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Thursday Meeting 1 information', 10, '07-30-2020');
INSERT dbo.MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Thursday Meeting 2 information', 10, '07-30-2020');
INSERT dbo.MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Friday Meeting 1 information', 11, '08-07-2020');
INSERT dbo.MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Friday Meeting 2 information', 11, '07-31-2020');
INSERT dbo.MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Friday Meeting 3 information', 11, '08-14-2020');
August 21, 2020 at 9:16 pm
>> need to run a stored procedure that will UPDATE all records [sic] DATE field [sic] (meeting_date) to the following week.<<
In SQL, a field refers to a meaningful subset of a variable or column. A row is nothing like a record. Camel case variable names have been shown to be an awful idea. If you grew up with Latin and Greek or Cyrillic alphabets, your eye has been trained to jump uppercase letters. There are very good reasons newspapers do not set names like this.
You also don't seem to know that we've had a DATE data type in SQL Server for a few years now. But you might also want to do some research on the ISO 8601 standards for dates. Since SQL is a declarative language and not a computational/procedural language, a better approach would be to create a calendar table. Besides having the standard "yyyy-mm-dd" (not the dialect you used!), the standards also specify a week day format "yyyyWww-d" where the first part (yyyy) is the year, the W is a punctuation mark, the number of the week with in the year follows (01 to 53), a dash is a second punctuation mark, followed by the day within the week (1 to 7, where a week ends on a Sunday).
>> SET @my_date = GETDATE(); <<
In declarative languages. We hate things like loops and if – then – else flow control, but we also hate local variables. Why did you create a local variable @my_date when you can call the current timestamp in your code? Also, we have had the ANSI standard CURRENT_TIMESTAMP syntax for several years now, so there's no need to use the old Sybase getdate().
Since holidays and other things could shift the meeting date, why not just do tape simple table lookup and join to a calendar table?
CREATE TABLE Meeting_Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
meeting_date DATE NOT NULL);
Please post DDL and follow ANSI/ISO standards when asking for help.
August 21, 2020 at 9:50 pm
If I've understood the requirement correctly then I think the following should work?:
update dbo.MeetingDate set meetingdate=dateadd(DAY,dayValue-5,getdate())
where dayvalue>=7 and dayvalue<=13
It works on the basis that:
The statement runs on a Saturday.
Dayvalue 7 is always Monday, Dayvalue 8 is always Tuesday e.t.c.
The logic is as follows:
Part 1
Dayvalue - 5 will give:
2 for Monday - There are two days between Saturday and Monday
3 for Tuesday - There are three days between Saturday and Monday
e.t.c.
Part 2
The number of days calculated in part 1 is added to the current date which should be a Saturday.
Sorry I may not have explained that very well?
I agree with JCelko's question about holidays. If it's necessary to make adjustment for holidays then a lookup table as he provided is one option. It should also be possible to have a table which only lists holiday days. I don't have time to test that at the moment but I can do if that would be useful? There may also be other options I haven't thought of.
Apologises if I misunderstood the requirement.
August 28, 2020 at 12:28 pm
Ok, First things first - My question is not clear or maybe somewhat clear. Second Forgot about holidays there is no business need for adjusting for them, "TRUST ME" I don't want to convolute what I'm trying to do based on holidays. For the sake, of the question, let's pretend there are no worldly holidays. EVER!!!
Next:
The values represented in the column dayValue are fixed based on the fact that these values work when using the following SQL(Keep in mind this SQL is for demonstrating purposes). SO.... If the variable @dayValue = 7 Then the date will always be next Monday, if 8 then Tuesday, if 9 then Wednesday etc... Give it a try run it based on the create table script and insert records from the original post!!
DECLARE @dayValue int
SET @dayValue = 8 -- For Example Eight (8) will always represent a TUESDAY REGARDLESS WHEN THIS SCRIPT IS RUN
DECLARE @meetingDate datetime
SET @meetingDate = (SELECT DATEADD([day], ((DATEDIFF([day], '19000108', Getdate()) / 7) * 7) + @dayValue,'19000108'))
SELECT @meetingDate
The update query I am trying to create is simply using the values in column dayValue as a means to update the column meetingDate. So I could easily do this if I wanted to update the records 1 by 1.
DECLARE @dayValue int
SET @dayValue = 7
DECLARE @meetingDate datetime
SET @meetingDate = (SELECT DATEADD([day], ((DATEDIFF([day], '19000108', Getdate()) / 7) * 7) + @dayValue,'19000108'))
UPDATE dbo.MeetingDate
SET meetingDate = @meetingDate
WHERE dayValue = 7
The results of the query would update the meetingDate to "2020-08-31" for all records with a dayValue of Seven (7)
Yes -- I could use a stored procedure and write out all the update queries individually for each day of the week and be done with it.
What I'm asking is, is there a way to write an update query that would iterate through each record, determine the next week's date based on using the dayValue in a subquery within the same update query.
This may require the use of a unique ID for each record, however, Im simply not sure if its possible but ope to thoughts and examples
Thank you!
August 28, 2020 at 12:58 pm
So if I understood right, a script to run on a Saturday, to update fields for the next Mon-Fri?
If so you need to work out the "next monday" and use that as the driving value, then case it based on the value of what your wanting to apply to increment to.
Is this the kinda thing your after?
SET DATEFORMAT MDY
CREATE TABLE #MeetingDate (
MeetingInfo nvarchar(max) NOT NULL,
dayValue int NOT NULL,
meetingDate SMALLDATETIME NOT NULL
);
GO
INSERT INTO #MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Monday Meeting 1 information', 7, '08-03-2020');
INSERT INTO #MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Monday Meeting 2 information', 7, '08-10-2020');
INSERT INTO #MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Tuesday Meeting 1 information', 8, '08-11-2020');
INSERT INTO #MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Wednesday Meeting 1 information', 9, '08-19-2020');
INSERT INTO #MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Thursday Meeting 1 information', 10, '07-30-2020');
INSERT INTO #MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Thursday Meeting 2 information', 10, '07-30-2020');
INSERT INTO #MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Friday Meeting 1 information', 11, '08-07-2020');
INSERT INTO #MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Friday Meeting 2 information', 11, '07-31-2020');
INSERT INTO #MeetingDate (MeetingInfo, dayValue,meetingDate)
VALUES ('Friday Meeting 3 information', 11, '08-14-2020');
GO
SELECT * FROM [#MeetingDate]
DECLARE @NextMonday SMALLDATETIME = DATEADD(wk, datediff(wk, 0, getdate()) + 1, 0)
UPDATE [#MeetingDate] SET [meetingDate] =
CASE [dayValue]
WHEN 7 THEN @NextMonday
WHEN 8 THEN DATEADD(DAY,1,@NextMonday)
WHEN 9 THEN DATEADD(DAY,2,@NextMonday)
WHEN 10 THEN DATEADD(DAY,3,@NextMonday)
WHEN 11 THEN DATEADD(DAY,4,@NextMonday)
WHEN 12 THEN DATEADD(DAY,5,@NextMonday)
WHEN 13 THEN DATEADD(DAY,6,@NextMonday)
END
SELECT * FROM [#MeetingDate]
August 28, 2020 at 1:01 pm
Also as a side note I would recommend reading up on the BBCodes and extra formatting here to help people out and readily consumable data, you did a great job as a starting point but missed a few of the key words out and should ideally be putting the dates in as a YMD format to avoid any ambiguity as we in the UK would have errors inserting those dates you provided hence the set dateformat mdy at the top.
There is a link on how to post the code and data for the best help below for this just gives a few tips on how we can easily consume what you give us without having to change it around to suit our environments etc.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 28, 2020 at 1:01 pm
Okay, let's get some terminology straight... Your example data has no condition where the dayValue column minus 5, does not equal the default weekday value that SQL Server could compute for the meeting date that it currently in the table, prior to update. Therefore, with your example data, you could quite easily just add 7 days to each meeting date and be done. However, as that's so incredibly simple, I have to believe that your intent was to allow for data that might NOT meet that condition, and that what you are looking for may be that the desired weekday of the "next" meeting date should be the next occurrence of the weekday specified by your dayValue column, regardless of the value of the current meeting date. Is that a correct assumption? If so, a simple math calculation can get you the required number of days to add to the meeting date for any given row, based on a comparison of the weekday of the existing meeting date and the dayValue minus 5. This all assumes that you are running SQL Server with the default setting for DAYFIRST being equal to 1 - meaning that Sunday is 1, Monday is 2, etc., etc...
Thus for what I'm assuming you are looking for, a simple math calculation in combination with a CASE statement would be sufficient to determine for any given row, what the next meeting date is.
EDIT: This would also eliminate any dependency on what day of the week the update is run.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 28, 2020 at 5:45 pm
Okay...
Dude! What's with all the SPAM in your signature line???
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2020 at 7:36 pm
@anthony.green This is exactly what needs to be done. THANK YOU!!
Yes I should have inserted code rather than used the Blockquote.
I use some other forums and they do not have the INSERT code button
To redeem myself:
SELECT * FROM MY TABLE
It would also appear the query you provided can be run on any day, not just a Saturday. That's good. Lastly, I had no idea that a CASE statement could be used in an UPDATE query. I learn something every day, and I thank you for that.
...However, as that's so incredibly simple, I have to believe that your intent was to allow for data that might NOT meet that condition, and that what you are looking for may be that the desired weekday of the "next" meeting date should be the next occurrence of the weekday specified by your dayValue column, regardless of the value of the current meeting date. Is that a correct assumption?
Your assumption is correct my friend.
Thank you all for the help, very appreciated.
August 28, 2020 at 8:07 pm
Okay, one last question - realized just now that the "next occurrence" could potentially be in the past, so can I safely assume that it's the next occurrence that is actually in the future? Also, what if today would be the new value? Would one choose one week in the future, or today?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 28, 2020 at 8:07 pm
SPAM? Is it not okay to have links to business pursuits?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 29, 2020 at 1:33 am
SPAM? Is it not okay to have links to business pursuits?
I'm thinking not... at least not non-SQL related links. If a newbie were to post such a thing, the SPAM detector would delete their post.
But, whatever.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply