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)


I have been trying select top 1, select distinct item_no from, etc. I also
have another version using group by and having but I get the same results.
Thanks in advance.

rdraider (rdraider@sbcglobal.net) 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@sommarskog.se

Books Online for
SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx