Deadline Tomorrow - Limiting the zero value in averaging a row of data
-
I need to average a row of data, while checking for zero values, however, I only want to eliminate zero values from the calculation if it is a starting zero. The data is based on a updated table where the data is static and I will need to do the calculation and then updaae this table.
Example:
SkuNumber, Description, Field1 = 0, Field2 = 0, Field3 = 11, Field4 = 12, Field5=0, field6=15, etc.
I need the calucation to not include Field1 and field 2, and Average( Field3, Field4, Field5 and Field6).
Keep in mind the fields = zero can vary so I need to account for the variance, however, include all zero values after the first field that returns an actual value.
I must resolve this issue immediately my deadline is tomorrow.
I thought about a if statement with a case, check for zero values, something like this.
if field1 > 0 then
Average(Field1+Field2+field3 etc...)
elseif field 2>0 and Field 1 =0
Average(Field2+field3 etc...)
elseif Field3>0 and field1=0 and field2=0
Average(Field3+field4+field5+etc...)
End if
I also thought of you some kindaof function to check for zero, but unsure on how to proceed.
Any and all assistance is greatly appreciated.
Karen
-
There's many ways to do this but most will involve a loop or a (yeeeeech!) Cursor (Haaaaaack... Patooooooiiiii!), a temp table, or maybe some extra columns added to your existing table... loops are always slower than straight code, we won't even talk about cursors or dynamic SQL, temp tables are not always the way to go (especially if you have lots of rows), table variables are OK but you have to watch memory with lot's of rows, and most balk at adding extra columns to existing tables (or adding extra tables for a join).
With that in mind, the following brute-force monstrosity works nasty fast for up to 64 columns. You can double that number of columns by adding one more REPLACE with 64 "0"s as the "search for" in the replace... and it also works if there is a field sum of 0...
SELECT SkuNumber, [Description], Field1+Field2+Field3+Field4+Field5...Field64 --up to 64 fields / CASE WHEN Field1+Field2+Field3+Field4+Field5...Field64 = 0 THEN 1 ELSE LEN( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( '-' +STR(Field1,1) +STR(Field2,1) +STR(Field3,1) +STR(Field4,1) +STR(Field5,1) . . . +STR(Field64,1) --up to 64 fields ,'-00000000000000000000000000000000','-') ,'-0000000000000000','-') ,'-00000000','-') ,'-0000','-') ,'-00','-') ,'-0','-') ,'-0','-') ,'-','') ) END AS myAverage
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Hello Karen,
Here's another alternative that counts the number of preliminary zeros. This assumes no null values in the fields. I hope you really don't have 64 or 99 fields! Change the value '99' to the actual number of numeric fields in your table.
UPDATE yourtable SET your_avg =
Field1+Field2+Field3+Field4+Field5...Field99 -- UP TO 99 fields
/ CASE WHEN prelim_ct = 99 THEN 1 ELSE 99-prelim_ct END
FROM yourtable
, (SELECT SkuNumber, prelim_ct
= CASE WHEN Field1<>0 THEN 0 ELSE 1 +
CASE WHEN Field2<>0 THEN 0 ELSE 1 +
CASE WHEN Field3<>0 THEN 0 ELSE 1 +
-- etcetera: paired CASE/END statements for each field
-- UP TO 99 fields
END
END
END
FROM yourtable
) AS tb_with_ct
WHERE tb_with_ct.SkuNumber = yourtable.SkuNumber
Before you update, test it with this code:
------------------------------------------
SELECT yourtable.SkuNumber, AVG =
Field1+Field2+Field3+Field4+Field5...Field99 -- UP TO 99 fields
/ CASE WHEN prelim_ct = 99 THEN 1 ELSE 99-prelim_ct END
FROM yourtable, (
SELECT *, prelim_ct
= CASE WHEN Field1 <>0 THEN 0 ELSE 1 +
CASE WHEN Field2<>0 THEN 0 ELSE 1 +
CASE WHEN Field3<>0 THEN 0 ELSE 1 +
-- etcetera: paired CASE/END statements for each field
-- UP TO 99 fields
END
END
END
FROM yourtable
) AS tb_with_ct
WHERE tb_with_ct.SkuNumber = yourtable.SkuNumber
Regards,Bob Monahon
-
Karen,
Bob and I both made the same boo-boo... in the code where the fields get added up, you must include all of that addition in parentheses or you'll get the wrong answer. The division between the additions and the CASE statement takes precedence so you must force the addition to occur first (before the division).
Sorry about the rookie mistake...
Here's my corrected code...
SELECT SkuNumber, [Description], (Field1+Field2+Field3+Field4+Field5...Field64) --up to 64 fields / CASE WHEN Field1+Field2+Field3+Field4+Field5...Field64 = 0 THEN 1 ELSE LEN( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( '-' +STR(Field1,1) +STR(Field2,1) +STR(Field3,1) +STR(Field4,1) +STR(Field5,1) . . . +STR(Field64,1) --up to 64 fields ,'-00000000000000000000000000000000','-') ,'-0000000000000000','-') ,'-00000000','-') ,'-0000','-') ,'-00','-') ,'-0','-') ,'-0','-') ,'-','') ) END AS myAverage
Bob's UPDATE and SELECT test code may be corrected in the same simple manner except that the SELECT test code will give you an "ambiguous column name error" for each field. To fix it, just replace the "*" in the nested SELECT statement with "SkuNumber" (without the quotes, of course).
As a side note, for large numbers of rows, Bob's may actually run faster because he doesn't do any string manipulation in his code. Nice job, Bob.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Thanks for the fix, Jeff. A second pair of eyes always helps.
Regards,Bob Monahon
-
Hello Karen.
Maybe like this?
create table #table(field1 int, field2 int, field3 int, field4 int, field5 int, field6 int)
insert into #table values(0,0,1,0,3,9)
insert into #table values(0,1,1,0,3,9)
insert into #table values(0,0,0,1,3,9)
insert into #table values(0,0,0,0,1,9)
select
average =
case
when field1 > 0 then (field1 +field2 +field3 +field4 +field5 +field6)/6
when field2 > 0 then (field2 +field3 +field4 +field5 +field6)/5
when field3 > 0 then (field3 +field4 +field5 +field6)/4
when field4 > 0 then (field4 +field5 +field6)/3
when field5 > 0 then (field5 +field6)/2
when field6 > 0 then (field6)
else 0
end
from #table
drop table #table
Regards,
Leon Bakkers -
thanks for the suggestion, however, I must have been thinking the same thing at the same time as you guys,
Here is my solution.
Update dbo.FCDataEntry
Set FC_UserQty6=mth7Qty.mth7
From
(
Select mth7.FCHdrID, Round(Sum(mth7.Mth7Avg*Mth7.strctmthFC8),0) as Mth7
from
(
Select MTHQTY.FCHdrID,StrCtMthFC8,--Checking for Zero values
Cast(Sum(M1+M2+M3+M4+M5+M6+M7+M8+M9+M10+M11) AS DECIMAL(10,2))/
CASE WHEN M1 <> 0 THEN 11 WHEN M2 <> 0 THEN 10 WHEN M3 <> 0 THEN 9
WHEN M4 <> 0 THEN 8 WHEN M5 <> 0 THEN 7 WHEN M6 <> 0 THEN 6
WHEN M7 <> 0 THEN 5 WHEN M8 <> 0 THEN 4 WHEN M9 <> 0 THEN 3
WHEN M10 <> 0 THEN 2 ELSE 1 END as Mth7Avg
From
(
Select dbo.FCDataEntry.FCHdrID,strctmthFC8,
(CASE WHEN ShipHistory6 <> 0 THEN ShipHistory6/strctMth4 ELSE 0 END) as M1, --JUL04
(CASE WHEN ShipHistory5 <> 0 THEN ShipHistory5/strctMth3 ELSE 0 END)as M2, --AUG04
(CASE WHEN ShipHistory4 <> 0 THEN ShipHistory4/strctMth2 ELSE 0 END) as M3, -- SEP04
(CASE WHEN ShipHistory3 <> 0 THEN ShipHistory3/PrevStrCtMth1 ELSE 0 END) as M4,--OCT04
(CASE WHEN ShipHistory2 <> 0 THEN ShipHistory2/StrCtMthCurr ELSE 0 END) as M5, --NOV04
(CASE WHEN ShipHistory1<> 0 THEN ShipHistory1/StrCtMthFC1 ELSE 0 END) as M6, --DEC04
(CASE WHEN FC_UserQty1 <> 0 THEN FC_UserQty1/StrCtMthFC2 ELSE 0 END) as M7, --JAN05
(CASE WHEN FC_UserQty2 <> 0 THEN FC_UserQty2/StrCtMthFC3 ELSE 0 END) as M8, --FEB05
(CASE WHEN FC_UserQty3 <> 0 THEN FC_UserQty3/StrCtMthFC4 ELSE 0 END) as M9, --Mar05
(CASE WHEN FC_UserQty4 <> 0 THEN FC_UserQty4/StrCtMthFC5 ELSE 0 END)as M10, --APR05
(CASE WHEN FC_UserQty5 <> 0 THEN FC_UserQty5/StrCtMthFC6 ELSE 0 END) as M11 --May05
from dbo.FCDataEntry,dbo.TempStoreCt
Where dbo.FCDataEntry.DemandSrcID = dbo.TempStoreCt.DemandSrcId
Group by dbo.FCDataEntry.FCHdrID, strctmthFC8,
ShipHistory6, ShipHistory5, ShipHistory4, ShipHistory3, ShipHistory2, ShipHistory1, FC_UserQty1, FC_UserQty2,
FC_UserQty3, FC_UserQty4, FC_UserQty5, strctMth4, strctMth3, strctMth2, PrevStrCtMth1, StrCtMthCurr,
StrCtMthFC1, StrCtMthFC2, StrCtMthFC3, StrCtMthFC4, StrCtMthFC5, StrCtMthFC6) AS MTHQTY
GROUP BY MTHQTY.FCHdrID,StrCtMthFC8,
M1,M2, M3, M4, M5, M6,M7,M8,M9,M10,M11) Mth7
group by mth7.FCHdrID,mth7.Mth7Avg,mth7.strctmthFC8) as Mth7Qty
Where dbo.FCDataEntry.FCHdrID=mth7Qty.FCHdrID
thanks,
Kare
-
Looks good. It's a good thing you didn't have 64 or 99 fields.
Regards,Bob Monahon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply