select based on either or

[hamking01]

I trying to add up values of a column based on whether another column has a date entered as such:

SELECT
P.Plant
, ISNULL(SUM(CASE WHEN F.dt_Sign BETWEEN '01/01/00'AND
'12/31/00' THEN M.AppvdAmount END), 0) AS CurrentTotal
, ISNULL(SUM(CASE WHEN F.dt_Sign BETWEEN '01/01/00' AND
'12/31/00' THEN M.AppvdAmount END), 0) AS YTDTotal
LEFT JOIN tbl_Feedback F (NOLOCK)
ON M.tbl_MainId = F.tbl_MainId
WHERE F.IdPerson IN (8, 9)

This will work add the AppvdAmount to both the CurrentToal and YTDTotal if either F.IdPerson 8 or 9 has a Date value in dt_Sign. However, if there is a Date value for BOTH 8 and 9, it will add the AppvdAmount twice.

I only want to have it added once, so long as either or both have Data value, but not to add when both field are blank.

How would I do this?

 

[amrita418]

How about dividing by a count value?

SUM(CASE WHEN F.dt_Sign BETWEEN '01/01/00'AND
     '12/31/00' THEN M.AppvdAmount END) /
sum(case when F.dt_Sign BETWEEN '01/01/00'AND
     '12/31/00' THEN 1 else 0 end) END AS CurrentTotal

Regards,
AA