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

[quoted text, click to view]

"Alex Nitulescu" <REMOVETHIScuca_macaii2000@yahoo.com> wrote in
message news:edKX5NP4FHA.1140@tk2msftngp13.phx.gbl...
[quoted text, click to view]

<snip>

[quoted text, click to view]

Alex 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.

[quoted text, click to view]

Hi 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


[quoted text, click to view]

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
--