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? 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)
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. 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)
HTH,
Ryan
