Calculating Consecutive Fiscal Year Donations

Matt,

 

I’m teaching myself Common Table Expressions (and actually enjoying using them) and this is what I came up with:

 

-- Begin Code

 

;with MaxFyear (customer_no, fyear) as

(

      select

      c.customer_no,

      max(g.fyear)

      from t_contribution c with (nolock)

      join t_campaign g with (nolock) on c.campaign_no = g.campaign_no

      group by c.customer_no

),

DistinctFyear (customer_no, fyear) as

(

      select distinct

      c.customer_no,

      g.fyear

      from t_contribution c with (nolock)

      join t_campaign g with (nolock) on c.campaign_no = g.campaign_no

),   

SummarizeContribution(customer_no, fyear, num_years) as

(

      select

      d.customer_no,

      d.fyear,

      1 as num_years

      from DistinctFyear d

      join MaxFyear m with (nolock) on d.customer_no = m.customer_no and d.fyear = m.fyear

 

union all

 

      select

      d.customer_no,

      d.fyear,

      r.num_years + 1 as num_years

      from DistinctFyear d

      join SummarizeContribution r on d.customer_no = r.customer_no and d.fyear = r.fyear - 1

)

 

select

customer_no,

min(fyear) as first_year,

max(num_years) as num_years

from SummarizeContribution

where

customer_no >= 900 and customer_no <= 930

group by customer_no

order by customer_no

 

-- End code

 

Let me know if this works for you or if you have any questions.

 

 -steve carlock

Information Technology Manager

The Granada

(805) 899-3000 x 111 (phone)

(805) 899-3081 (fax)

 

 

 

Quick coding logic sort of question:  What's the most efficient way to calculate the number of consecutive years that a given customer has donated to the opera?

I'm looking at T_CONTRIBUTION, and could count years by specific campaigns or even tie it to T_CAMPAIGN and use that table's fyear column, but finding all customers who have donated for 10 consecutive years (as I am sometimes asked - though not enough to force myself to put together some sort of code I can reuse) manages to elude me from a logic standpoint.

As I sat pondering such things as "well, if I take the fyear of a customer's latest donations and subtract the fyear of the next latest fyear of their donations and the result is one, then I've got a consecutive year..." but then it occurred to me I might be missing the forest for the trees and decided to ask you clever folk.  Any suggestions?

Thankfully, I await responses.

BONUS TRIVIA:  Pigeons are the only bird that can swallow water without having to crane their heads up.

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!