April 8, 2008 at 6:35 am
Isn't there a facility to schedule a job in SQL Server 2005? That way the timing of the job could be taken care of and the logic simplified.
April 8, 2008 at 7:08 pm
If you're going to do it that way, Bob, you need to understand that you may have a fundamental flaw in your code... remember the comment in one of your previous examples?
/*If the TOTAL_POINTS = 0 for the 14 day period, insert a record in to the attendance
table which deducts .5 points for the UID in question*/
Also, there's no need for a temp table... a simple join will work.
And, you need to straighten out the definition of 14 days ago as I have so no days missed except today which isn't over yet.
AND, none of this works if the Attendance table has no entry for a give user...
Soooooooo..... that brings us to this...
INSERT INTO dbo.Attendance
(UID, ENTRY_TYPE, DATE, POINTS, NOTES)
SELECT u.UID, '3', GETDATE(), '-0.5', 'SYSTEM GENERATED DEBIT'
FROM dbo.Attendance a
RIGHT OUTER JOIN
dbo.Users u
ON a.UID = u.UID
WHERE ( Date >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)-14 --Midnight 14 days ago NOT including today
AND Date < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)) --Midnight early today which is not part of the true 14 days
OR a.Date IS NULL --Remove this if there's always an entry for every user even if it's 0
GROUP BY u.UID
HAVING SUM(a.Points) = 0
... details are in the comments...
--Jeff Moden
April 8, 2008 at 8:51 pm
Jeff,
First off, thank you very much for the education on all this. There is one part of the code I don't understand / question. You are suggesting a left outer join and I'm not totally sure why.
My only goal is to perform the insert in to the attendance table based on the other criteria being true *** for each UID that exists in the Users table ***
So, What is the purpose of the left outer join on the attendance and users tables given what the goal is? there will never be multiple entries for users in the users table and I want the insert logic to be applied to EVERY occurance of a UID in the users table where the ENABLED flag is set to true (which I've updated in my code). So, couldn't I simply use the folloiwng? Or am I still missing something regarding the join?
Not trying to be difficult, just trying to understand.
-current code--
INSERT INTO dbo.Attendance
(UID, ENTRY_TYPE, DATE, POINTS, NOTES)
SELECT u.UID, '3', GETDATE(), '-0.5', 'SYSTEM GENERATED DEBIT'
FROM dbo.Users
WHERE u.ENABLED = 'True'
AND (Date >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)-14 --Midnight 14 days ago NOT including today
AND Date < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)) --Midnight early today which is not part of the true 14 days
GROUP BY u.UID
HAVING SUM(a.Points) > 0
April 8, 2008 at 8:59 pm
Ah, let me take a stab at this.. (studying a bit more)
The join of the attendance and users table provides teh ability to NOT require a tmp table and is required so we can access both the UID field of the users table and the Points (having sum) field of the attendance table within our querry, correct?
Ok, I'm going to go read about this further but specifically, why a right outer join, if I may ask.
Thanks,
Bob
April 8, 2008 at 9:06 pm
Bob...
Don't need the join at all if only the Attendance table is involved and the join has nothing to do with getting rid of the Temp table. Were inserting into the Attendance table directly from the Attendance table.
The RIGHT outer join to the USERS table is so that if a user is not in the attendance table, we can still get a sum of 0 for that user.
--Jeff Moden
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply