Wednesday, 29 August 2018

The result of the query does not return the values ​​in decimal

Why does the following query not returning values in decimal? I wanted the CustRating and Rating Difference column to return the values in decimal.

SELECT
     a.CustNo AS CustNo,
     a.CustRating AS CustomerRate,
     a.RatingDate AS RatingDate,
     (a.CustRating-b.CustRating) AS [Rating Difference]
FROM
     MasterTable a
          JOIN MasterTable b ON b.CustNo = a.CustNo
ORDER BY
     CustNo

This is the original table [MasterTable]
CustNo  CustRating  RatingDate
101AAA  0.000500000 "2016-01-08 00:00:00.000"
101AAA  0.000000750 "2016-01-12 00:00:00.000"
101AAA  0.000000550 "2016-01-22 00:00:00.000"
101AAA  0.000000000 "2016-01-27 00:00:00.000"

This is the query result:
CustNo  CustRating  RatingDate                  Rating Difference
101AAA  0           "2016-01-08 00:00:00.000"   0
101AAA  0           "2016-01-12 00:00:00.000"   0
101AAA  0           "2016-01-22 00:00:00.000"   0
101AAA  0           "2016-01-27 00:00:00.000"   0


I assume your column is an integer? You can easily use convert for this.
CONVERT(decimal(9,2), a.CustRating) AS CustomerRate,
CONVERT(decimal(9,2), a.CustRating - b.CustRating) as RatingDifference

0 comments:

Post a Comment