Check Previous Value

[FunkyBunch]

Hello All,

I am running Crystal Reports 9 with SQL Server 2005.

The problem i am having is trying to pull the last unit number that a piece of equipment was attached to. I am not sure where to even start with this process.

select top 1 equipment.vehicle_Id
from equipment
where equipment_id = <record in Crystal Reports>
order by date_equipment_removed desc

I am using an equipment history table which holds the information for the equipment, as well as the units it has been assigned to.

I know this post is not that descriptive, but it is all i have.

Thanks,

 

[synapsevampire]

Group by the equipment.

Use Report->Selection Formula->Group and place:

{table.unitnumber} = maximum({table.unitnumber},{table.equipment})

This will suppress values other than the maximum unit number for each piece of equipment.

-k

 

[FunkyBunch]

I believe that logic would work if the equipment is being transferred to a new unit each time. But will this same logic work if the equipment is being transferred from unit 100 to 4?

 

[synapsevampire]

No, then you would have to have a date stamp, or use the natural order of the database.

For a date stamp:

{table.date} = maximum({table.date},{table.equipment})

For natural order, place the fields in the gropup footer, and suppress the details and group header.

It's generally best to post example data and the expected output rather than using descriptions.


-k