Re: Audit table, finding first change for each item
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 21 Sep 2007 22:06:08 +0000 (UTC)
rdraider (rdraider@xxxxxxxxxxxxx) writes:
I have an audit table that tracks changes to inventory items. I am
trying to find the first change in avg_cost per item. The table will
contain 1 row for the before record and another row for the changed to
record. The table tracks everything change to items, not just
avg_cost.
'B' in where clause and alias = BEFORE
'C' in where clause and alias = CHANGE
The cast on aud_dt and aud_tm are because both are datetime, but the app
stores only date in aud_dt (9/20/2007) and only time in aud_tm (1/1/1900
5:51:12 PM).
This query gives me the desired results but I just want the first change
per item based on changedate.
SELECT b.item_no, b.loc, b.aud_action,
cast((cast(b.aud_dt as float) + cast(b.aud_tm as float)) as datetime)
as
beforedate,
cast((cast(c.aud_dt as float) + cast(c.aud_tm as float)) as datetime)
as
changedate,
c.aud_action AS change,
b.avg_cost, c.avg_cost AS changecost
FROM iminvaud_sql as b INNER JOIN
iminvaud_sql as c ON b.item_no = c.item_no AND
b.loc = c.loc AND
b.avg_cost <> c.avg_cost
WHERE (b.aud_action = 'B') AND (c.aud_action = 'C')
AND (b.aud_dt IS NULL)
For SQL 2005:
WITH changes AS (
SELECT b.item_no, b.loc, b.aud_action,
cast((cast(c.aud_dt as float) +
cast(c.aud_tm as float)) as datetime) as changedate,
c.aud_action AS change,
b.avg_cost, c.avg_cost AS changecost,
rn = row_number OVER (PARTITION BY c.item_no, c.loc
ORDER BY cast(c.aud_dt as float) +
cast(c.aud_tm as float))
FROM iminvaud_sql as b
JOIN iminvaud_sql as c ON b.item_no = c.item_no
AND b.loc = c.loc
WHERE b.avg_cost <> c.avg_cost
AND b.aud_action = 'B'
AND c.aud_action = 'C'
AND b.aud_dt IS NULL
)
SELECT item_no, loc, aud_action, changedate, change, avg_cost,
changecost
FROM changes
WHERE rn = 1
Earlier versions of SQL Server:
SELECT b.item_no, b.loc, b.aud_action,
cast((cast(c.aud_dt as float) +
cast(c.aud_tm as float)) as datetime) as changedate,
c.aud_action AS change,
b.avg_cost, c.avg_cost AS changecost
FROM iminvaud_sql as b
JOIN iminvaud_sql as c ON b.item_no = c.item_no
AND b.loc = c.loc
WHERE b.avg_cost <> c.avg_cost
AND b.aud_action = 'B'
AND c.aud_action = 'C'
AND b.aud_dt IS NULL
AND cast(cast(c.aud_dt as float) +
cast(c.aud_tm as float) AS datetime) =
(SELECT MIN(cast (cast(c1.aud_dt as float) +
cast(c1aud_tm as float) as datetime
FROM iminvaud_sql as c1
WHERE c.item_no = c1.item_no
AND c.loc = c1.loc
AND c1.aud_action = 'C')
If these *untested* queries do not work out, please post:
o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result given the sample.
That is likely to give you a tested solution.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for
SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Audit table, finding first change for each item
- From: rdraider
- Audit table, finding first change for each item
- Prev by Date: Re: Do foreign keys generate implicit indexes?
- Next by Date: Re: Retrieve ONLY first/max
- Previous by thread: Audit table, finding first change for each item
- Next by thread: Do foreign keys generate implicit indexes?
- Index(es):
Relevant Pages
|
