Getting my seudo code to T-SQL - Need some help

Question

Getting my seudo code to T-SQL - Need some help

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bob Boursaw SSC Eights! Points: 865 More actions · Answer 3

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

Bob Boursaw SSC Eights! Points: 865 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)