March 26, 2008 at 11:13 am
I can't seem to get the syntax correct for these...
I have to date fields StartDate and EndDate. I want to set the CourseLength field to be the difference ebtween these two fields. I also have some logic to remove weekends.
I don't actually know how I can update the table in the end. Some help would be great.
CREATE TRIGGER trg_CourseLen ON Courses FOR INSERT, UPDATE
AS
DECLARE @days AS INT
IF EXISTS(SELECT * FROM inserted)
BEGIN
SET @days = DATEDIFF(d,StartDate,EndDate)
IF @days > 5
BEGIN
DECLARE @weekends AS INT
SET @days = @days - DATEDIFF(ww,StartDate, EndDate) * 2 + 1
END
ELSE
BEGIN
SET @days = @days
END
UPDATE Courses
SET CourseLength = @days
WHERE CourseId = inserted.CourseId
END
March 26, 2008 at 11:47 am
What leaps out right away are two things:
1. DECLARE @weekends INT (but this is never used, and doesn't appear to be required)
2. Is the equation giving the correct results?
Assume DATEDIFF(day, StartDate, EndDate) is 40 days over five weeks. Your equation will give 31 days as formatted (40 - 5 * 2 + 1), or do you expect 29 days (40 - (5 * 2 + 1))?
March 26, 2008 at 11:52 am
I forgot to remove the @weekends variable. You can ignore that.
So far my equation works for what I need and I can change it later, the most important part is getting the update or insert to work.
March 26, 2008 at 12:58 pm
Your trigger logic is thinking you are dealing with one record at a time. Remember that the inserted table can have multiple records so you need to do a set-based operation to be most efficient.
Make the body of your trigger (note that you don't have to use the exists this way):
UPDATE
C
SET
C.CourseLength = DATEDIFF(ww,StartDate, EndDate) * 2 + 1
FROM
Courses C
INNER JOIN Inserted I ON C.CourseID = I.CourseID
WHERE
DATEDIFF(d,StartDate,EndDate) > 5
Now, you can do the same thing with a calculated field - that may be a better option.
Your removal of weekends logic is not that great - there are postings on this site that can help you improve this.
March 26, 2008 at 1:23 pm
I'll take a look at my weekend logic but I have tested it and compared it to just counting by hand and it works fine.
If it's 40 days...
(40 - (6*2)) + 1
(40 - 12) + 1
(28 + 1)
29
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply