CONVERT(DECIMAL,
(
(case when kpi1 <5 then kpi1 else 0 end)+
(case when kpi2 <5 then kpi2 else 0 end)+
(case when kpi3 <5 then kpi3 else 0 end)+
(case when kpi4 <5 then kpi4 else 0 end)+
(case when kpi5 <5 then kpi5 else 0 end)+
(case when kpi6 <5 then kpi6 else 0 end)+
(case when kpi7 <5 then kpi7 else 0 end)+
(case when kpi8 <5 then kpi8 else 0 end)+
(case when kpi9 <5 then kpi9 else 0 end)+
(case when kpi10 <5 then kpi10 else 0 end)
))
/
CONVERT(DECIMAL,
NULLIF(
(
(case when kpi1 <5 then 4 else 0 end)+
(case when kpi2 <5 then 4 else 0 end)+
(case when kpi3 <5 then 4 else 0 end)+
(case when kpi4 <5 then 4 else 0 end)+
(case when kpi5 <5 then 4 else 0 end)+
(case when kpi6 <5 then 4 else 0 end)+
(case when kpi7 <5 then 4 else 0 end)+
(case when kpi8 <5 then 4 else 0 end)+
(case when kpi9 <5 then 4 else 0 end)+
(case when kpi10 <5 then 4 else 0 end)
)
,0))

;WITH CTE
AS
(
  SELECT rowID, kpi, value
  FROM KPI
  UNPIVOT(value FOR kpi IN (kpi1, kpi2, kpi3, kpi4, kpi5, kpi6, kpi7, kpi8, kpi9, kpi10)) u
)
SELECT rowID, 
       SUM(CAST(value AS DEC)) / (4.0 * COUNT(*)) AS avg_value
  FROM CTE
 WHERE value < 5
GROUP BY rowID;

quote:

---

Originally posted by mystifier

Thanks RL

I really wanted something I could use as a calculated field but your query is a good idea and I may think about using it to create a view.

---