SELECT Name, SUM(Switch([Date] = DateAdd('d', - 7, DATE ()), NetAmount) )AS DAY1 FROM Customer GROUP BY Name
My question is how to return the value 0 when the date is not present (an empty field). Should I use
IIF or something else?
Take a look at this one from msdn: http://msdn.microsoft.com/en-us/library/office/aa172237%28v=office.11%29.aspx
It shows ways to handle null.
From what you explain - I'd go with IIF