SQLTeam.com | Weblogs | Forums

Max min functions relating to date in case when statement please help


#1

Ideally I'm trying to modify the below case when to function properly and substract the sums from each other:

SUM(
(CASE WHEN
o.closedate >= a.activity_date, other filter condition, other filter condition
THEN
SUM(max(case when(coalesce(s.asoffield, s2.asoffield) that comes before a.activitydate ) then s.balanceonthatday else null end)

SUM(min(case when(coalesce(s.asoffield, s2.asoffield) that is closest date after a.activity date) then s.balancethatday))))

Starting with the basics...will the max case when select the balance on the greatest date less than the activity date?

See below:

SUM(max(case when(coalesce(s.asoffield, s2.asoffield) that comes before a.activitydate ) then s.balanceonthatday else null end)


#2

that's not valid SQL