Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Unanswered: SQL to output specific changing values from audit table

    Guys

    I have an audit Table (CustomerID,EmployerID,Salary,Date,etc..........)

    I would like to output a list [CustomerID,EmployeeID,Salary,Date] whenever there is a change in the salary for the latest EmployerID for a single CustomerID. Many rows have identical (salary) values repeating(except date).

    As it's a (half million row) general auditing table there are no indexes (only one clustered newsequentialid() on AuditReference, Doh), I will use a SProc so doesn't need to be done in one SQL statement, I'm (hopefully)trying to achieve usable performance (< 3 Secs) without implementing/Changing indexes

    Hopefully I've explained OK, any further info please ask

    Any general pointers would be appreciated
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    CREATE Table #audit (
    	id	int	NOT NULL,
    	CustomerID	int not null,
    	EmployerID	int	not null,
    	Salary		dec(10,2)	not null,
    	DaDate		DATE	not null
    )
    GO
    INSERT INTO #audit (id, CustomerID, EmployerID, Salary, DaDate)VALUES 
    (1, 1, 1, 100.00, '2001-01-01'),
    (2, 1, 2, 100.00, '2002-01-01'),
    (3, 1, 3, 115.00, '2003-01-01'),
    (4, 1, 3, 115.00, '2004-01-01'),
    (5, 1, 3, 90.00, '2005-01-01'),
    (6, 1, 3, 150.00, '2006-01-01'),
    
    (1, 12, 1, 10.00, '2001-01-01'),
    (2, 12, 2, 10.00, '2002-01-01'),
    (3, 12, 3, 11.50, '2003-01-01'),
    (4, 12, 4, 11.50, '2004-01-01'),
    (5, 12, 4, 11.50, '2005-01-01'),
    (6, 12, 4, 15.00, '2006-01-01')
    
    SELECT *
    FROM #audit
    GO 
    with CTE (id, CustomerID, EmployerID, Salary, DaDate, RowNum)
    AS
    	(SELECT id, CustomerID, EmployerID, Salary, DaDate,
    		ROW_NUMBER() OVER (PARTITION BY CustomerID, EmployerID, Salary ORDER BY DaDate DESC) as RowNum
    	FROM #audit
    	),
    LatestEmployer (CustomerID, EmployerID, RowNum)
    AS 
    	(SELECT CustomerID, EmployerID,
    		ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY DaDate DESC) as RowNum
    	FROM #audit
    	)
    SELECT CTE.*
    FROM CTE
    	INNER JOIN LatestEmployer AS LE ON
    		CTE.CustomerID = LE.CustomerID AND
    		CTE.EmployerID = LE.EmployerID
    WHERE LE.RowNum = 1	-- get last Employer for a Customer 
    	AND CTE.RowNum = 1
    ORDER BY CTE.CustomerID, CTE.EmployerID, DaDate
    Last edited by Wim; 03-02-11 at 09:56. Reason: Reread the question :o
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    without the index it will continue to get slower as more records are added. Keep in mind you are searching through everyone of the half million+ records each time you run. So, even if you get it running in acceptable time now, what happens when you hit 1 million+ records? Don't quite understand, why you'd be adverse to adding an index to help your response time.
    Dave Nance

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Guys

    First of all WIM, many, many apologies for not posting back and thanking you sooner. Your solution was greatly appreciated and worked like a charm (Zoom Zoom) once I had mashed it up with the other requirements, UNIONing it in with the Header table and excluding Salarys that contained the same values etc.

    dav, I hear what your saying & if performance becomes an issue I'll certainly consider Indexes, but at the moment I'm thinking this is actually a true auditing table and it's a fudge to be using it to supply this functionality anyway, god help us when someone decides to Achive off audit data, lol. Plus the table gets a very high percentage of INSERTS via triggers compared to the number of times this query is run. overall it's a Judgement call, not high priority unless performance becomes noticeable & I prefer to add Indexes later rather than sooner.

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •