April 3, 2008 at 8:45 pm
I have a few things I'm trying to accomplish here.
I have 2 tables
USERS and ATTENDANCE
I want to set something up such that when a record is 'added' to the users table that it inserts data for that user in to the attendance table. I believe this would be done via a trigger but can't seem to figure it out. Note: I am working in the 2005 IDE so if it would be easier to do this via TSQL, i'm happen to do that too.
Secondly, I want to have something kick off automatically each night which will take ALL users (UID) in the users table and insert them in to the attendance table. One of the columns in the attendance table is DATE which has a default value of GetDate(), so basically I will end up with one entry, per user defined in the users table, for every day.
I think I have the query to insert all users from the USERS table in to the attendance table by using ...
insert into ATTENDANCE (UID)
select UID from USERS
This seems to work since 4 of the 5 columns in my attendance table have default values.
So, I'm guessing that I can use thsi query as the items that runs nightly to input 'that days' default values in to the table for all defined users? If so, can someone direct me to a sample of how to setup a trigger (provided that is the best method for handling this)
The next piece I'm stumbling with is when a new user is added to the DB. When this occurs, I want just the newly added UID in the users table to be inserted in to the ATTENDANCE table. I guess I'm missing how I can insert just the 'newly created' record in to the table and not ALL of them like done above.
LASTLY (sorry for the long post)
How difficult would it be to build something in SQL such that an automated process can check a collection of records in a table to see if the last 'x' number contain 0's. If so, modify a different column.
Basically, I'm trying to track 14 days worth of data and if colB for UID 2 is 0 for the past 14 days... do something
Any help with this stuff would be GREATLY appreciated.
Thank you much!
Bob
April 4, 2008 at 9:08 am
Without the table structures, I can only give a sort of pseudo code but something like this.
Here's your trigger
CREATE TRIGGER NEW_USER ON USERS
FOR INSERT
AS
INSERT INTO ATTENDANCE([UID],[DATE])
SELECT
[UID],
GETDATE()
FROM INSERTED
The other part would be a scheduled job that would look something like this
INSERT INTO ATTENDANCE([UID],[DATE])
SELECT
[UID],
GETDATE()
FROM USERS
April 4, 2008 at 9:33 am
I'd be careful of a trigger here. There might be times that I could see this not being needed.
You might want to just create a stored procedure to do this update and call it from the insert or schedule it periodically.
April 4, 2008 at 9:45 am
I'm guessing as to what this is for, but I think it's basically making an attendance record for every user for every day. When a user is entered in the system, they're apparently showing attendance on the day they are entered.
Personally, I would just put records in the attendance table on the days when people attended, not for every day.
With a table holding valid dates for attendance, the same information can be retrieved, but at less disk cost.
To get the equivalent data in a query it would be
SELECT
a.UID,
b.[DATE],
ATTENDED = CASE WHEN c.[DATE] IS NULL THEN 0 ELSE 1 END
FROM
USERS a JOIN ATTENDANCE_DATES b ON
1=1
LEFT JOIN ATTENDANCE c ON
a.UID = c.UID AND
b.[DATE] = c.[DATE]
WHERE
b.[DATE] BETWEEN @datefrom AND @dateto
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply