We would like to be able to pull a list of all constituents who have given over a specific amount (this will change depending on who pulls the list) for the past 14 months. I looked at the header and got some ideas but am now stumped.
Detail Table: vs_contribution
Detail Col: sum(!.cont_amt)
If I leave it as is when I pull the criteria I am asked for a dollar amount and then it pulls anyone who has given cumulatively over that dollar amount ever. This is behavior that i would expect as i have entered nothing into the Where column. My problem is everything i try to enter into the where column pulls back errors.
Any help would be greatly appreciated.
Thanks
Margaret
You could create a view to sum the amount using the current date minus 14 months. example below.
declare @today datetime --set the variable set @today= GETDATE() --set it to todaySELECT distinct customer_no, max(cont_dt) as last_gift_dt,sum(cont_amt) as gifts FROM t_contribution a WHERE a.cont_dt >=DATEADD(month,-14,@today) --today minus 14 months
GROUP BY a.customer_no
You should be able to use the view to then create a new list element. Let me know if it works for you.
Travis