Hi. I have the following updates to make in a table:

a. I have to update SalesBrut to (Sales - [Returns]) where Sales and
[Returns] are fields in my table:
    SalesBrut = (Sales - Returns)
b. Then I have to find out the total of SalesBrut for the whole table, so
that I can calculate percentages:
    Select Sum(SalesBrut) from MyTable
c. Then I have to calculate some more data, like:
    VsSalesTY = (ReturnsTY/SalesBrutTY)
d. And finally calculate the percentages, as in:
     [%AFF] = SalesTY/@TotalSalesBrutTY
where @TotalSalesBrutTY is the sum calculated at point b.

Occasionaly there are 0 (zeroes) in my table, so therefore I get errors. All
these fields are of type DECIMAL.

How can I write a sproc to do all that ?
I have tried the following:
________________________________________________________
CREATE PROCEDURE Test AS

    Declare @TotalSalesBrutTY    [decimal](18, 0)
    Declare @TotalSalesBrutLY    [decimal](18, 0)
    Declare @TotalReturnsTY       [decimal](18, 0)
    Declare @TotalReturnsLY       [decimal](18, 0)

    Update MyTable set
        SalesBrutTY = SalesTY - ReturnsTY,
        SalesBrutLY = SalesLY - ReturnsLY

     Set @TotalSalesBrutTY = (Select Sum(SalesBrutTY) from MyTable)
     Set @TotalSalesBrutLY = (Select Sum(SalesBrutLY) from MyTable)
     Set @TotalReturnsTY = (Select Sum(ReturnsTY) from MyTable)
     Set @TotalReturnsLY = (Select Sum(ReturnsLY) from MyTable)

    Update _ReportRetoursVsVentes3 set
        VsLS = (ReturnsTY - ReturnsLY)/ReturnsLY,
        VsSalesTY = ReturnsTY/SalesBrutTY,
        VsSalesLY = ReturnsLY/SalesBrutLY

    Update _ReportRetoursVsVentes3 set
         [%AFF] = SalesTY/@TotalSalesBrutTY,
         [%RetTY] = ReturnsTY/@TotalReturnsTY,
         [%RetLY] =ReturnsLY/@TotalReturnsLY
GO
________________________________________________________
but because of the zeroes I get errors in some of the divisions. I only I
had something like IIf in VB, to write IIf(fieldX<>0, division formula,
something else)..... I have also tried to write something like

..... as above
Declare @ReturnsLY [decimal](18, 0)
Set @ReturnsLY = (Select Sum(SalesBrutTY) from MyTable)
If @ReturnsLY <> 0
    Begin
        VsLS = (ReturnsTY - ReturnsLY)/@ReturnsLY,
    end
Else
    begin
        VsLS = 100
    end

But it does not "compile"....

Please help.
Thank you very much
Alex.

PS. I'm, learning, so please bear with me - I think I just asked something
elementary....
Thanks again.

You can use CASE...WHEN...END statement. It is similar to VB IIF Statement.


Regards,

Peri

Show quoteHide quote

<snip>

Show quoteHide quoteAlex Nitulescu,

Ok, this is more in the nature of a question of my own.

Why is there a comma at the end of the statement in the first
begin-end block?


Sincerely,

Chris O.

On Thu, 3 Nov 2005 22:54:18 -0500, Alex Nitulescu wrote:

Show quoteHide quoteHi Alex,

Unless you're working on a datawarehouse or other reporting-oriented
database, it's not recommended to store the results of calculations in a
table. You'll find yourself constantly struggling to keep the values
correct after each change to the table. There are better ways to do
this.

The SalesBrut could be calculated on the fly in a query, defined in a
view, or added as a computed column (note that computed columns are NOT
permanently stored, but are calculated on the fly - unless they are also
indexed).

The same goes for VsSalesTY.

You can't use a computed column for the percentages (computed column
expressions can only use values from the same row). But you can still
calculate this in a view or in a stored procedure that selects the data
(as opposed to a stored procedure that calculates and permanetly stores
the data in the table).

Based on the code you posted in the stored procedure, you could do this,
as one of many options:

CREATE TABLE MyTable
           (-- Various other columns,
            SalesTY decimal(12,2) NOT NULL,
            ReturnsTY decimal(12,2) NOT NULL,
            SalesLY decimal(12,2) NOT NULL,
            ReturnsLY decimal(12,2) NOT NULL,
            SalesBrutTY = (SalesTY - ReturnsTY),
            SalesBrutLY = (SalesLY - ReturnsLY),
            VsLS = ((ReturnsTY - ReturnsLY) / ReturnsLY),
            VsSalesTY = (ReturnsTY / SalesBrutTY),
            VsSalesLY = (ReturnsLY / SalesBrutLY)
           )

This table definition takes care of most of your calculations.

For the percentages, you can use the following view:

CREATE VIEW MyView
AS
SELECT      -- Various other columns,
            a.SalesTY, a.ReturnsTY, a.SalesLY, a.ReturnsLY,
            a.SalesBrutTY, a.SalesBrutLY,
            a.VsLS, a.VsSalesTY, a.VsSalesLY,
            a.SalesTY   / b.TotalSalesBrutTY AS [%AFF],
            a.ReturnsTY / b.TotalReturnsTY   AS [%RetTY],
            a.ReturnsLY / b.TotalReturnsLY   AS [%RetLY]
FROM        MyTable AS a
CROSS JOIN (SELECT SUM(SalesBrutTY) AS TotalSalesBrutTY,
                   SUM(ReturnsTY)   AS TotalReturnsTY,
                   SUM(ReturnsLY)   AS TotalReturnsLY) AS b


As an example, let's focus on the computation of VsSalesLY.It should not
result in a "divide by zero" error when SalesBrutLY is 0.

The basic fix is to change
            VsSalesLY = (ReturnsLY / SalesBrutLY)
to either
            VsSalesLY = CASE WHEN SalesBrutLY <> 0
                             THEN (ReturnsLY / SalesBrutLY)
                             ELSE NULL
                        END
or to
            VsSalesLY = (ReturnsLY / ISNULL(SalesBrutLY, 0))

They are exactly the same - the latter is a shorthand form, but the
former is easier to understand. Both will result in VsSalesLY being NULL
if SalesBrutLY is 0.

However, I understand that you want the result to be 100 if divide by 0
would occur. In that case, you'll have to change the code to either
            VsSalesLY = CASE WHEN SalesBrutLY <> 0
                             THEN (ReturnsLY / SalesBrutLY)
                             ELSE 100
                        END
or to
            VsSalesLY = COALESCE ((ReturnsLY/ISNULL(SalesBrutLY,0)),100)

Note that the ISNULL still results in a NULL if SallesBrutLY is 0, but
the COALESCE changes the end result back from NULL to 100. The CASE
expression is much more straightforward in this case.

Best, Hugo

Deleting large amount of data on sql server 2000
Order problem
Dynamic SQL
SQL Server 2005 & Visual Studio 205 RC
grouping and showing concatenated varchar column?