How do I Return Result of Case as Decimal
in [Programming SQL]
|
From: Sam on 15 Jul 2008 23:33 How do I return the following as a Decimal 10.2 Sum(Balance) asBal, Case WHEN Sum(Balance) = 0 and Sum(PER) = 0 THEN 0 ELSE Sum(Balance) / Sum(PER) End as [Average] TIA Sam
From: Steve Malley on 15 Jul 2008 23:44 Hi, Sam. I suggest that you cast the variables as follows: cast(Balance as [dec](10,2)) I will often wrap the results of computations again, so that the result has the desired number of decimal places, e.g., if you substitute your "Balance" for my "25" in the statement below... select cast(25 as [dec](10,2)) , cast( cast(25 as [dec](10,2)) * cast(25 as [dec](10,2)) as [dec] (10,2)) you will see the result, as well as the numbers entered into the calculation have two decimal places. You can cast within and outside your case statement. On Jul 15, 9:33 pm, "Sam" <S...(a)nospam.com> wrote: > How do I return the following as a Decimal 10.2 > > Sum(Balance) asBal, > Case > WHEN Sum(Balance) = 0 and Sum(PER) = 0 THEN > 0 > ELSE > Sum(Balance) / Sum(PER) > End as [Average] > > TIA > > Sam
From: Sam on 16 Jul 2008 00:56 Thank you Steve Worked a treat Regards Sam "Steve Malley" <StephenP.Malley(a)gmail.com> wrote in message news:26ae4efd-eff0-4882-8502-a879ef40cd69(a)27g2000hsf.googlegroups.com... Hi, Sam. I suggest that you cast the variables as follows: cast(Balance as [dec](10,2)) I will often wrap the results of computations again, so that the result has the desired number of decimal places, e.g., if you substitute your "Balance" for my "25" in the statement below... select cast(25 as [dec](10,2)) , cast( cast(25 as [dec](10,2)) * cast(25 as [dec](10,2)) as [dec] (10,2)) you will see the result, as well as the numbers entered into the calculation have two decimal places. You can cast within and outside your case statement. On Jul 15, 9:33 pm, "Sam" <S...(a)nospam.com> wrote: > How do I return the following as a Decimal 10.2 > > Sum(Balance) asBal, > Case > WHEN Sum(Balance) = 0 and Sum(PER) = 0 THEN > 0 > ELSE > Sum(Balance) / Sum(PER) > End as [Average] > > TIA > > Sam
|
Pages: 1 Prev: build forecast Next: Strange fast and slow execution times |