rounding up values

[Question on Rounding up to nearest value]

Has anyone done round up of values to the nearest dollar.

For example I want to give a 10% of the price to my customers but if the
result is other than .00 then I wanted to round up to the nearest dollar
amount.

My calculation using sql has been price * percent and then subtract the
value from the price, then what do I need to do to roundit up??

Thanks for your suggestion.

Also I have a problem with my customers that I am extracting and the query
does return all the values from 2004 and 2006 that are equal except for the
price I have given them, how do I get only the latest ones in 2006 and not
the same customers name and addresses if they are duplicated 3 times??

Thanks so much for your reply, I know that someone else might have ask this
questions before.

 

[Ken Sheridan]

The following expression should compute and round up the net price to the
next dollar where the discounted price to two significant decimal places is
not an integer amount:

IIF(VAL(FORMAT([GrossPrice]-[GrossPrice]*[Discount],"#.00"))=INT([GrossPrice]-[GrossPrice]*[Discount]),VAL(FORMAT([GrossPrice]-[GrossPrice]*[Discount],"#.00")),INT([GrossPrice]-[GrossPrice]*[Discount])+1)

where the Discount is expressed as a fractional value, e.g. 0.1 for 10
percent.

I'm not quite clear exactly what you are asking with your second question,
but when you want to return the latest (by date) row for each value in a
group you'd use a subquery which returns the latest date per grouped value,
e.g. to return the last orders per year per customer:

SELECT CustomerID, OrderID,
OrderDate, OrderAmount
FROM Orders AS O1
WHERE OrderDate =
(SELECT MAX(OrderDate)
FROM ORDERS AS O2
WHERE O2.CustomerID =
O1.CustomerID
AND YEAR(O2.OrderDate) =
YEAR(O1.OrderDate))
ORDER BY CustomerID, OrderDate DESC;

The outer query and subquery are correlated by CustomerID and the year of
the order, distinguishing the two instances of the Orders table by the
aliases O1 and O2.

Ken Sheridan
Stafford, England