CODEWITHSUNDEEP
sql-serverprecisioncalculated-columns
YHapticY
YHapticY

Reputation: 195

How do you truncate decimals in a computed column?

I need to create a query that shows the average productivity for editors that have worked on more than one book except for their first book published with a precision of 0.01 pages/day.

I have the correct columns showing now but I need to reduce the number of zeros that are being displayed in the AverageProductivity column.

The columns to be displayed are

EditorName

BookName

computed column AverageProductivity

Here are the tables and their columns

AGENT  AgentID (PK,varchar(11), not null)
       AgentName (varchar(25), not null)

BOOK   BookName (PK, varchar(45), not null)
       Genre (varchar(25), not null)
       DateOfPublication (date, not null)
       NoOfPages (int, not null)
       WriterID (PK, FK,, varchar(11), not null)
       EditorID (FK, varchar(11), not null)

EDITOR EditorID (PK, varchar(11), not null)
       EditorName (varchar(25), not null)
       Mentors_EditorID (FK, varchar(11), null)

WRITER WriterID (PK, varchar(11), not null)
       WriterName (varchar(25), not null)
       AgentID (FK, varchar(11), not null)

Sample Data

insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('Valley of Heroes','10','Fiction','2010-01-12',874,'20');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('The Ruler''s Return','11','Fantasy','2012-03-14',765,'22');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('eRobot','11','Fantasy','2011-04-15',264,'20');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('An Uncle''s Letters','12','Fiction','2012-06-12',258,'20');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('Pretty flowers','13','Album','2013-01-31',148,'22');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('A Tale of Lions','12','Fantasy','2012-08-17',301,'21');
insert into BOOK (BookName, WriterID, Genre, DateOfPublication, NoOfPages, EditorID)
values ('eRobot','13','Sci Fi','2012-10-04',465,'23');

I have tried to use a format syntax to change the precision but Im not sure the correct syntax for it.

Here is the Query...

use SignumLibri_01
select * from (
    select e.EditorName, b.BookName,
       round(NoOfPages * 1.0
                 / datediff(day, lag(b.DateOfPublication)
                                   over (partition by b.EditorID
                                           order by b.DateOfPublication
                                         )
                               ,DateOfPublication
                            )
               , 2) AverageProductivity       
   from book b
   inner join editor e on e.EditorID = b.EditorID 
) x
where AverageProductivity is not null

Is there a way to truncate all the extra zeros so I have to the precision of 0.00?

Results... 

Melanie eRobot  0.580000000000
Melanie An Uncle's Letters  0.610000000000
George  Pretty flowers  0.460000000000

Upvotes: 1

Views: 573

Answers (1)

EzLo
EzLo

Reputation: 14199

You need to change the datatype to DECIMAL(X, 2). The ROUND function doesn't actually change the data type, just truncates or rounds the values from a particular place onwards.

CONVERT(
    DECIMAL(10, 2),
    round(NoOfPages * 1.0 /datediff(
            day, 
            lag(b.DateOfPublication) over(partition by b.EditorID order by b.DateOfPublication),
            DateOfPublication),
        2))

Also note that while converting to DECIMAL, the value is automatically rounded, so you don't actually have to call the ROUND function (unless you want to truncate the decimals).

;WITH ValueWithDecimals AS
(
    SELECT
        Value = 50 * 1.0 / 19
)
SELECT
    Original = V.Value,
    RoundedAt2 = ROUND(V.Value, 2),
    RoundedAt4 = ROUND(V.Value, 4),
    ConvertedToDecimal2 = CONVERT(DECIMAL(10, 2), V.Value),
    ConvertedToDecimal4 = CONVERT(DECIMAL(10, 4), V.Value)
FROM
    ValueWithDecimals AS V

Result:

Original    RoundedAt2  RoundedAt4  ConvertedToDecimal2 ConvertedToDecimal4
2.631578    2.630000    2.631600    2.63                2.6316

You can see that the rounded values and the decimal values match (not on scale and precision but on content).

Upvotes: 3

Related Questions