CODEWITHSUNDEEP
c#sql-serverwinforms
user2590980
user2590980

Reputation: 53

Rounding in sql server

When I execute this query in SQL Server:

SELECT
   Vt.Id, Vt.Description, 
   abs(Vt.Rate)as VRate,
   Sum(((itemprice * Qty) * (abs(Vt.Rate) / 100))) as VatAmount,
   Sum(itemprice * Qty) as NetAmount 
FROM
   BillItem1 as B1 
LEFT JOIN
   bill b ON b.orderid = b1.orderid
LEFT JOIN 
   ItemDescription ItD ON ItD.Id = B1.itemId 
LEFT JOIN
   VatType Vt on Vt.Id = ItD.TaxId 
WHERE 
   B1.IsActive = 1 
   AND B1.IsDelete = 0 
   AND b.date BETWEEN '11/09/2013 10:43:31 AM' AND '11/09/2013 10:43:31 AM' 
GROUP BY 
   Vt.Id, Vt.Rate, Vt.Description 
ORDER BY 
   SUM((b1.ItemPrice*Qty) - b1.NetAmount)DESC

the output is

enter image description here

and when I bind with data grid view its display following output

enter image description here

In vatAmount columns display lots of zero after decimal, so i want to reduce zero from grid view.

Upvotes: 1

Views: 167

Answers (6)

NoChance
NoChance

Reputation: 5752

You can format the data displayed using the following code. This code causes truncation not rounding.

dataGridView1.DataSource=sometable; //bind to datasource

DataGridViewCellStyle style = new DataGridViewCellStyle();
style.Format = "N2";
this.dataGridView1.Columns["Price"].DefaultCellStyle = style;

Upvotes: 1

Szymon
Szymon

Reputation: 43023

The underlying value is the same anyway. How you display it is a matter of formatting. Formatting in SQL is usually unnecessary. Formatting should be done on the client that receives the data from the database system.

Don't change anything in SQL. Simply format your grid to display the required number of digits.

Upvotes: 2

Sudhakar Tillapudi
Sudhakar Tillapudi

Reputation: 26209

You can use Round function to get only required number of digits after decimal point.

double myValue = 23.8000000000000;
double newValue=Math.Round(myValue, 2);

Result : newValue = 23.8

Upvotes: -2

Sachin
Sachin

Reputation: 2148

Convert number in code side.

ex. : 

string.Format("{0:0.##}", 256.583); // "256.58"
string.Format("{0:0.##}", 256.586); // "256.59"
string.Format("{0:0.##}", 256.58);  // "256.58"
string.Format("{0:0.##}", 256.5);   // "256.5"
string.Format("{0:0.##}", 256.0);   // "256"
//===============================
string.Format("{0:0.00}", 256.583); // "256.58"
string.Format("{0:0.00}", 256.586); // "256.59"
string.Format("{0:0.00}", 256.58);  // "256.58"
string.Format("{0:0.00}", 256.5);   // "256.50"
string.Format("{0:0.00}", 256.0);   // "256.00"
//===============================
string.Format("{0:00.000}", 1.2345);    // "01.235"
string.Format("{0:000.000}", 12.345);   // "012.345"
string.Format("{0:0000.000}", 123.456); // "0123.456"

In your case :

<asp:TemplateField HeaderText="VatAmount">
    <ItemTemplate>
        <%# string.Format("{0:0.00}", Eval("VatAmount").ToString()); %>
    </ItemTemplate>
</asp:TemplateField>

Upvotes: 0

splakard nanards
splakard nanards

Reputation: 54

enclose your VatAmount with round(,2): round(Sum(((itemprice*Qty)*(abs(Vt.Rate)/100))),2)

Upvotes: 0

Tilak
Tilak

Reputation: 30698

You can convert to decimal type with 2 decimal points.

Select Vt.Id, Vt.Description, convert(decimal(20,2),abs(Vt.Rate)) as VRate,convert(decimal(20,2),Sum(((itemprice*Qty)*(abs(Vt.Rate)/100)))) as VatAmount ,convert(decimal(20,2),Sum(itemprice*Qty),)as NetAmount from BillItem1 as B1 left join bill b on b.orderid=b1.orderid
 Left JOIN ItemDescription ItD ON ItD.Id=B1.itemId Left Join VatType Vt on Vt.Id = ItD.TaxId where B1.IsActive=1 and B1.IsDelete = 0 and b.date between '11/09/2013 10:43:31 AM' and '11/09/2013 10:43:31 AM' Group By Vt.Id,Vt.Rate,Vt.Description Order By SUM((b1.ItemPrice*Qty) - b1.NetAmount)DESC

Upvotes: 2

Related Questions