[sqlite] Good way for CEIL, or is there a better way

[Cecil Westerhof]

2016-05-10 22:06 GMT+02:00 Darren Duncan <darren at darrenduncan.net>:

> On 2016-05-10 12:03 AM, Cecil Westerhof wrote:
>
>> But it looks like that the following is also acceptable:
>>     ?
>> CAST(ROUND(100.0 * rank / outOf + 0.4999999) AS int) AS percentage
>> and it is a lot simpler. So probably I will go for this.
>>
>
> That might seem simpler but on edge cases it would fail.  Having an
> if-then version is more reliable in general.  Also less ambiguous as ROUND
> doesn't behave the same everywhere. -- Darren Duncan


?OK, I am back to my original version. ;-)

But I made a view:
CREATE VIEW linkedinRankingPercentage AS
WITH percentage AS (
    SELECT date
    ,      rank
    ,      outOf
    ,           100.0 * rank / outOf         AS percentage
    ,      CAST(100.0 * rank / outOf AS int) AS castedPercentage
    FROM linkedinRanking
)
SELECT date
,      rank
,      outOf
,      (CASE WHEN percentage = castedPercentage
           THEN castedPercentage
           ELSE castedPercentage + 1
       END) AS percentage
FROM percentage
;

It is probably not possible, but just to be sure. ;-)
I like to have linkedinRankingPercentage the same fields as
linkedinRanking, but with the calculated field percentage added. I now name
those fields twice in the creating of the view. Can this be done better? In
the first SELECT I could change the three fields to a *, but I like the
symmetry more.

Would the view be a lot more expensive as the table, or if I do not need
the percentage, is it better to use the table instead of the view?

-- 
Cecil Westerhof