January 31, 2011 at 2:43 pm
I'm not sure if this is going to make sense, but I have a refresh log table of people with first name, last name, isstudent and isemployee fields. People can be of any status (just student, just employee, or both). The refresh log lets me see what actions were taken on the people (Additions, Deletions, and Updates). There should be one record per addition and deletion, and two records for updates to a person (before and after).
I've been trying to isolate only those who have had any involvement with being an employee. The way I've been doing that is WHERE IsEmployee = 1. That's fine for brand new employees to the database, termed employees, (employees+students), and updated employees because they all have the isEmployee flag set. The scenario that's not accounted for is when a student (who is already in the database and has IsStudent = 1 and IsEmployee = 0) becomes an employee. The before update record for that student is never returned because it doesn't meet the WHERE criteria. However, the after update record does meet the condition, and therefore is returned. I end up with a partial update log.
Both records (the before update and after update) are still in the database, and I'm trying to get them to appear in my record set, so that my query/summary looks complete. Here is my code. Does anyone know how I can do this relatively easily?
IF OBJECT_ID('RefreshStatus','U') IS NOT NULL BEGIN
DROP TABLE RefreshStatus
END
GO
CREATE TABLE RefreshStatus(
ID int IDENTITY(1,1) NOT NULL,
PersonNo varchar(7) NULL,
LastName varchar(50) NULL,
FirstName varchar(50) NULL,
IsStudent bit NULL,
IsEmployee bit NULL,
[Action] varchar(50) NULL
) ON [PRIMARY]
GO
INSERT INTO RefreshStatus
SELECT '1111111', 'Smith', 'John', 0, 1, 'ADD' UNION ALL
SELECT '2222222', 'Jones', 'Jill', 0, 1, 'UPDATE: Before' UNION ALL
SELECT '2222222', 'Jones-Johnson', 'Jill', 0, 1, 'UPDATE: After' UNION ALL
SELECT '3333333', 'Gates', 'Bill', 1, 0, 'UPDATE: Before' UNION ALL
SELECT '3333333', 'Gates', 'Bill', 1, 1, 'UPDATE: After' UNION ALL
SELECT '4444444', 'Mills', 'Mike', 1, 0, 'ADD'
--All rows from the table
SELECT *
FROM RefreshStatus
--This query is the way I'm isolating
--actions that happen only to employees
--PROBLEM: Only captures people who were employees prior to updates
--Example: Bill Gates was once a student and became an employee,
-- but only the UPDATE: After record was returned for him
SELECT *
FROM RefreshStatus
WHERE IsEmployee = 1
--Ideally, I'd like to have all records for any involvement with
--being an employee (either becoming an employee, ceasing to be an
--employee, or staying an employee and having a name updated, for example).
--Here's what it should look like:
/*
IDPersonNoLastNameFirstNameIsStudentIsEmployeeAction
11111111SmithJohn01ADD
22222222JonesJill01UPDATE: Before
32222222Jones-JohnsonJill01UPDATE: After
43333333GatesBill10UPDATE: Before
53333333GatesBill11UPDATE: After
*/
Mike Scalise, PMP
https://www.michaelscalise.com
January 31, 2011 at 4:08 pm
Celko, the last week or two of you was pleasant. Did they take you off the meds?
Anyway, no, there is no simple way of getting the before/after rows that are associated since you don't have a connecting audit_id/stamp of some kind connecting the two rows to being associated.
You can do some tricks with ROW_NUMBER() or other methods, but there isn't a simple way. When you setup an audit table for double row entry with I/D instead of single row I/U/D (where the previous U was the last known state of the record), you want to make sure you use an auditstamp (I typically grab a GUID via NEWID()) to connect up the rows during the process.
The I/D audit method is also a bit heavy handed datawise, just as an fyi. I understand why you might do it, it's just painful from a data load perspective. Every edit is double the data. But if you don't mark the pairs in the I/D method, you've lost half the value of that technique, unfortunately.
Now, to fix this up, might I recommend you add a column to your table that can handle uniqueidentifiers, assign a NewID() to every Update:After, and use Row_NUMBER() in a oneshot method to find each singular Update:Before? Also, you will probably want to add a default: GETDATE() column, or your audit trail becomes less usable.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 31, 2011 at 4:27 pm
Craig,
Thanks for your explanation. Your recommendations make sense, so I'll dig a little deeper with those.
CELKO,
Thanks for the input, but I think you are looking too far into this. My table is quite a bit different from the one I posted and does not actually have "flags." My intention with the SQL script I posted was to convey my problem as simply and clearly as possible.
Mike Scalise, PMP
https://www.michaelscalise.com
January 31, 2011 at 4:39 pm
mikes84 (1/31/2011)
Craig,Thanks for your explanation. Your recommendations make sense, so I'll dig a little deeper with those.
Ping back here with the actual DDL and some sample data if you get stuck getting the audit-trail back on track, and we can probably hack you out a quick one-off query to get you fixed up.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply