• Replies 4 replies
  • Subscribers 336 subscribers
  • Views 57 views
  • Users 0 members are here
Related

Custom Ticket Criteria for 14 month giving

Former Member
Former Member $organization

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

 

 

 

  • $organization

    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 today

    SELECT 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



    [edited by: Travis Armbuster at 12:57 PM (GMT -6) on 26 Aug 2013]
  • $organization

    When you say it errors out when you type anything into the where column, can you give an example of what you are typing in?  

    A good example of a dated element (assuming you are meaning query element here rather than flex header) would be the contribution last year giving (if you have it).

    The where in that case is cont_dt > dateadd(yy,-1,getdate())

    So if you wanted 14 months, you could theoretically use the above and do cont_dt > dateadd(mm, -14, getdate())

  • Former Member
    Former Member $organization

    Ryan

     

    Figured out why it was erroring  - had an extra character in the query.  I am going to look at both your answer and Travis’ and see which one works best for our development department.  Yes, this is for a list criteria and not a custom header.

     

    Thanks

    Margaret

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ryan McFadden
    Sent: Monday, August 26, 2013 2:14 PM
    To: Margaret Stockton
    Subject: Re: [Tessitura Technical Forum] Custom Ticket Criteria for 14 month giving

     

    When you say it errors out when you type anything into the where column, can you give an example of what you are typing in?  

    A good example of a dated element (assuming you are meaning query element here rather than flex header) would be the contribution last year giving (if you have it).

    The where in that case is cont_dt > dateadd(yy,-1,getdate())

    So if you wanted 14 months, you could theoretically use the above and do cont_dt > dateadd(mm, -14, getdate())

    From: Margaret Stockton <bounce-margaretstockton4471@tessituranetwork.com>
    Sent: 8/26/2013 12:10:30 PM

    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

     

     

     




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • Former Member
    Former Member $organization in reply to Former Member

    Thank you both for all of your input.  I used part of what each of you suggested to create the view.  The code I used is this:

    USE [impresario]
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE VIEW [dbo].[LVS_CONT_14_MONTH]
    AS
    SELECT distinct
    customer_no,
    max(cont_dt) as last_gift_dt,
    sum(cont_amt) as gifts 
    FROM t_contribution a
    WHERE a.cont_dt  >=dateadd(mm,-14,getdate())    --today minus 14 months
    GROUP BY a.customer_no

    GO

    GRANT REFERENCES, SELECT on [LVS_CONT_14_MONTH] to impusers
    GO

     

    It took me forever to get back to this but finally a spare bit of time.