|
|
Posted by rdraider on 09/21/07 18:01
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.
Navigation:
[Reply to this message]
|