SQL Count, counting Halfs!

Question

I have a rather strange problem:

when certain sales are made (completed) a record is inserted with the event and ID of the sales person, currently this table is queried and the 'Count' is used (along with usual date boundaries) to calculate number of 'closed' sales. This is currently working fine.

The problem now is some sales are 'shared' sales, products are linked via leads, usually the lead and the product are always by the same sales person, however in some rare cases lead can be created by on salesperson and the product 'sold' by another in such case the 'sales' calculation needs to award 0.5 (half) a point to each sales person.

What would be the best SQL approach to solve this?

(SQL Server 2005)

Answer 1

Just use SUM() instead of COUNT(), i.e.

SELECT SUM(CASE WHEN shared=1 THEN 0.5 ELSE 1 END) FROM sales WHERE ...

Answer 2

I guess you have some way to distinguish between a "full sale" and a "half sale". Count separately the number of "full sales" and the number of "half sales". Then add full + 0.5*half.

Answer 3

SELECT
    SUM(CASE WHEN SaleUserID = LeadUserID THEN 1 ELSE 0.5)
FROM
    sales
WHERE
    (SaleUserID = @targetID OR LeadUSerID = @targetID)
    -- AND dateCriteria