0

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 the computed column is not showing any of the calculated averages.

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');

Here is the Query...

select * from (
    select 
    e.EditorName,
    b.BookName,
    round(
        NoOfPages/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

Results...

Melanie eRobot  0
Melanie An Uncle's Letters  0
George  Pretty flowers  0
YHapticY
  • 177
  • 11
  • Integer maths. An expression made up of integers can only return an integer. `0.5` isn't an integer. – Thom A Oct 22 '19 at 11:14

1 Answers1

3

SQL Server does integer division. So, 1/2 is 0, not 0.5.

You can easily fix this in your code. I think the simplest method is:

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

The * 1.0 converts this to a number with decimal places, getting around the integer division problem.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786