If the duplicates are not appropriate - why not remove them prior to averaging? You're in 2005 so a CTE preceding your average should also help drop the duplicates...

Casting the number to a decimal with 2 digits after the decimal point FIRST is going to instroduce a lot of error. You really should be calculating the average...

(and lest it sounds like I was reversing myself)...

The main reason I fight them so strongly is that no matter how they start out - sooner or later, you get...

I would have recommended using XML structures (which would allow you to maintain SOME structure and access various indexing strategies) - but you're using a SQL Server 2000 example, which...

Grant Fritchey (8/14/2009)

---

Kit G (8/13/2009)

---

Does one even dare ask for a translation?...

--David beat me to it--

EAV is...awful. Besides the data quality aspect you've just run into, it also prevents you from using just about any of the other integrity rules as well (e.g....

David Portas (8/14/2009)

---

Whether you get an error or not...

cs_troyk (8/14/2009)

---

Matt Miller (8/14/2009)

---

David Portas (8/14/2009)

---

Matt, you missed the point of what I was saying. My UPDATE should not violate the constraint, that's true. But what I wanted to demonstrate...

haichells (8/14/2009)

---

Dear All,

I have a requirement where my SP will have some n number of Insert/Update statements. Some of the statements may throw error "Cannot Insert duplicate rows" due to...

David Portas (8/14/2009)

---

Matt, you missed the point of what I was saying. My UPDATE should not violate the constraint, that's true. But what I wanted to demonstrate was that the...

They don't NEED to, but they probably should be. That will help keep away from "orphans".

David Portas (8/14/2009)

---

I was talking about both the specific case and the more general situation.

Using a function as part of a constraint is a procedural solution rather than a declarative...

Gift Peddie (8/14/2009)

---

I think you may have missed the where clause in my LEFT JOIN example. All three of these queries will return the same result set. ie Customer records...

one more thing - if your online table is big, but indexed based on that date - you might have a better shot at a good index usage with:

declare @g...