Rounding on division

[rnd]

CR 9. SQL Server 2K.
I have a calculated fields with same values (with decimal) displayed in the same row on a matrix (like a manual cross tab). I need to round up one and round down the next (alternate) and so on.
Example:
ID = 101
Table.FieldLoan value = 10
Number of Installments = 4
Table.FieldInstallment = 10/4 = 2.5
This 2.5 is shown in 4 colums (depending on the number of installments) on the same row as below.

ID 01/06 02/06 03/06 04/06 05/06 06/06 Total
101 2.5 2.5 2.5 2.5 0 0 .. 10
If I round it up it will show like
101 3 3 3 3 0 0 .. 10
I have to show this as
101 3 2 3 2 0 0 .. 10


Is there a way I can do this in the division formula itself or while I am rounding.
NOTE: All the values will be the same either 05/above or 05/below. There won't be both below & above .5 mixed in each case.
Thanks in advance.

 

[Madawc]

You could round up or round down using Truncate, adding 0.5 to the sum when you want to round up.

What I don't think you can do is blend two different sorts of sum in a crosstab. Unless maybe you made it a formula field, add 0.5 when the month number is odd (which you get by dividing by two, truncating and then multiplying by two).

There are also mock-crosstabs, in which you define each total individually. Flexible but a lot of work.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10