2016-05-10 23:27 GMT+02:00 R Smith <rsmith at rsweb.co.za>:
>
>
> On 2016/05/10 11:05 PM, Cecil Westerhof wrote:
>
>> 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?
>>
>>
>
>
> Sorry I'm late to this thread, but why not use a proper CEIL function
> inline?
?Better late as never. ;-)
?
> Any of the following two will work in all circumstances and are simple
> enough to be done without the view:
>
> (COALESCE(NULLIF(CAST(v AS int),v)+1,CAST(v AS int))
>
> (CAST(v AS int) + CASE CAST(v AS int) WHEN v THEN 0 ELSE 1 END)
>
?I liked the second more, but after I really understood the first I liked
that one more. By the way: in both the first ( does not belong there.
?
> Translated to your Query:
>
> SELECT date
> , rank
> , outOf
> , (COALESCE(NULLIF(CAST((100*rank/outof) AS
> int),(100*rank/outof))+1,CAST((100*rank/outof) AS int)) AS percentage
> FROM linkedinRanking
>
> OR
>
> SELECT date
> , rank
> , outOf
> , (CAST((100*rank/outof) AS int) + CASE CAST((100*rank/outof) AS int)
> WHEN (100*rank/outof) THEN 0 ELSE 1 END) AS percentage
> FROM linkedinRanking
>
>
> If you'd like to do a view and avoid repeating the calculation, I'd
> suggest a common table expression rather, like this perhaps:
>
> WITH PC(date, rank, outOf, prcnt) AS (
> SELECT date, rank, outOf, (100*rank/outof)
> FROM linkedinRanking)SELECT date, rank, outOf,
> (COALESCE(NULLIF(CAST(prcnt AS int),prcnt)+1,CAST(prcnt AS int)) AS
> percentage
> FROM PC
>
>
> Although - I doubt this makes a significant improvement in the efficiency,
> if any.
?I do not do it for performance, but readability and maintenance.
?
> Better yet for this kind of thing would be:
>
> SELECT date, rank, outOf, (COALESCE(NULLIF(CAST(prcnt AS
> int),prcnt)+1,CAST(prcnt AS int)) AS percentage
> FROM (SELECT date, rank, outOf, (100*rank/outof) AS prcnt FROM
> linkedinRanking)
>
?I went for:
CREATE VIEW linkedinRankingPercentage AS
SELECT date
, rank
, outOf
, COALESCE(NULLIF(CAST(prcnt AS int), prcnt) + 1, CAST(prcnt AS int))
AS percentage
FROM (
SELECT date
, rank
, outOf
, (100.0 * rank / outof) AS prcnt
FROM linkedinRanking
);
?
> HTH,
>
?Certainly.
--
Cecil Westerhof
