May 1, 2008 at 12:00 pm
I have an audit table that is populated when a user makes a change in the database, showing a Before and After snapshot of the table contents. I want to be able to run reports on changes made in a timeframe (yesterday, for example).
It should be as easy as SELECT * FROM entity_audit WHERE changetype <>'Before', but I have two problems:
1 - If the user hits the Save button without changing anything, it populates the table (not my app, I can't change the source code). This creates records that are intuitive duplicates, but have three columns that are different, a timestamp, the changetype (Before/After) and the LastUpdated field which is the same damn thing as the timestamp, no idea why it's there twice. Can't use Distince because of these columns
2 - Thought about just pulling every field except the above three in my SELECT and using Distinct, but if a user typos and corrects it after Saving, now I need the timestamp to tell which one to keep and which to discard.
My current solution is :
1 - (SELECT * FROM entity_audit WHERE changetype <> 'Before' ) and stick it in a temp table
2 - run through that table and SELECT each individual field including max(time_of_change), to get the last update.
Is this the only way to do this? Is there an easier way? Cleaner way? Seems cumbersome to me.
---SQL-----
declare @yestDate datetime,@todayDate datetime
set @yestDate = dateadd(dd,-1,CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS DATETIME))
set @todayDate = CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS DATETIME)
SELECT DISTINCT 'Demographic update',
/*
entity_audit.*
*/
--/*
entity_audit.addr1,
entity_audit.addr2,
entity_audit.attn,
entity_audit.changetype, -- 'Before/After'
entity_audit.city,
entity_audit.contact,
entity_audit.countryid,
entity_audit.county,
entity_audit.countycode,
entity_audit.creatdate,
entity_audit.creatid,
entity_audit.effdate,
entity_audit.email,
entity_audit.emerphone,
entity_audit.entid,
entity_audit.entname,
entity_audit.enttype,
entity_audit.faxphone,
entity_audit.firstname,
entity_audit.lastname,
entity_audit.lastupd, -- timestamp field
entity_audit.latitude,
entity_audit.longitude,
entity_audit.middlename,
entity_audit.mobilephone,
entity_audit.pagerphone,
entity_audit.phone,
entity_audit.phyaddr1,
entity_audit.phyaddr2,
entity_audit.phycity,
entity_audit.phycountryid,
entity_audit.phycounty,
entity_audit.phycountycode,
entity_audit.phypostalcode,
entity_audit.phyprovince,
entity_audit.phystate,
entity_audit.phyzip,
entity_audit.planactionsecid,
entity_audit.postalcode,
entity_audit.province,
entity_audit.secfaxphone,
entity_audit.secphone,
entity_audit.state,
entity_audit.termdate,
entity_audit.time_of_change, -- timestamp field
entity_audit.title,
entity_audit.updid,
entity_audit.zip
--*/
FROM entity_audit
WHERE (entity_audit.creatdate > @yestDate AND entity_audit.creatdate < @todayDate)
AND entity_audit.enttype = 'PROVIDER'
AND changetype <> 'BEFORE'
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 2, 2008 at 8:55 am
Hi,
What a great app!! Poor you... :crazy:
Try this:
SELECT {all fields except 3 mentioned}
FROM entity_audit
GROUP BY {all fields except 3 mentioned}
HAVING COUNT(DISTINCT entity_audit.changetype) > 1
This should display all of the "saved no changes" records. If a user does correct a typo it'll be caught by the GROUP BY and not be displayed as a duplicate.
HTH,
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply