I need some help with the sql behind a really simple dashboard I'm building.
We have a program for college-aged patrons that allows them to purchase discounted tickets and attend some events just for them. This program has been active since 2011. We use a constituency and the patron's birthday to determine their eligibility. Once they've reached a certain age they are no longer able to participate in the program. I've been asked to put together a couple of dashboards that show how many people signed up each each, how many aged out each year and how many were active (not aged out) in any given year.
The first two were no issue, but the third is giving me problems. I need to know cumulatively who was active in each year. I can't seem to figure out how to get my sql query to count anyone who has a NULL constituency end date and who might have aged out in that fiscal year. So if I'm looking at FY1314 and someone signed up in FY1112 and they aged out during the FY1314 year I can't see to get them counted even through they were considered active in FY1314. I know it's not working as it should because I took everyone with this constituency and dumped them into excel, did a little data manipulation and created a pivot table to get the correct count.
This is what I'm working with to build my dashboard:
SELECT cast((select count(customer_no) FROM TX_CONST_CUST WHERE constituency=50 and start_dt between '2011/09/01' And '2012/08/31 23:59:59' and (isnull (end_dt, '') = '' or end_dt >= '2012/08/31 23:59:59')) as int) as '1112 Ignite Active',
cast((select count(customer_no) FROM TX_CONST_CUST WHERE constituency=50 and start_dt between '2011/09/01' And '2013/08/31 23:59:59' and (isnull (end_dt, '') = '' or end_dt >= '2013/08/31 23:59:59')) as int) as '1213 Ignite Active',
cast((select count(customer_no) FROM TX_CONST_CUST WHERE constituency=50 and start_dt between '2011/09/01' And '2014/08/31 23:59:59' and (isnull (end_dt, '') = '' or end_dt >= '2014/08/31 23:59:59')) as int) as '1314 Ignite Active',
cast((select count(customer_no) FROM TX_CONST_CUST WHERE constituency=50 and start_dt between '2011/09/01' And '2015/08/31 23:59:59' and (isnull (end_dt, '') = '' or end_dt >= '2015/08/31 23:59:59')) as int) as '1415 Ignite Active',
cast((select count(customer_no) FROM TX_CONST_CUST WHERE constituency=50 and start_dt between '2011/09/01' And '2016/08/31 23:59:59' and (isnull (end_dt, '') = '' or end_dt >= '2016/09/01 23:59:59')) as int) as '1516 Ignite Active'
Thanks for any help you can give me!
Melissa
Hi Melissa,
I think your problem is that you are comparing to the end of your date range; so you are only counting people that were active at the end of the FY, not people that were active during the FY. If you use the FY (date range) start date for your comparison, I think you will then count everyone that aged out during that year.
Also, I would use relative date ranges based on TR_BATCH_PERIOD so that you don’t have to go change your query each year. I would also try and use generic field names like Current Yr Ignite Active, Last Yr Ignite Active, Prev Yr Ignite Active, Lapsed 2 Ignite Active, and Lapsed 3 Ignite Active. Then you wouldn’t need to change your field names either.
See if this works for you….
SELECT cast((select count(customer_no)
FROM TX_CONST_CUST cc
join (select fyear,min(start_dt) as start_dt,MAX(end_dt) as end_dt from TR_Batch_Period bp group by bp.fyear) as fy on 1=1 and GETDATE() between fy.start_dt and fy.end_dt
WHERE constituency=50 and cc.start_dt between DATEADD(yyyy,-4,fy.start_dt) And DATEADD(yyyy,-4,fy.end_dt) and coalesce(cc.end_dt,DATEADD(yyyy,-4,fy.end_dt)) >= DATEADD(yyyy,-4,fy.start_dt)) as int) as '1112 Ignite Active',
cast((select count(customer_no) FROM TX_CONST_CUST cc join TR_Batch_Period bp on 1=1 and GETDATE() between bp.start_dt and bp.end_dt
WHERE constituency=50 and cc.start_dt between DATEADD(yyyy,-4,fy.start_dt) And DATEADD(yyyy,-3,fy.end_dt) and coalesce(cc.end_dt,DATEADD(yyyy,-3,fy.end_dt)) >= DATEADD(yyyy,-3,fy.start_dt)) as int) as '1213 Ignite Active',
WHERE constituency=50 and cc.start_dt between DATEADD(yyyy,-4,fy.start_dt) And DATEADD(yyyy,-2,fy.end_dt) and coalesce(cc.end_dt,DATEADD(yyyy,-2,fy.end_dt)) >= DATEADD(yyyy,-2,fy.start_dt)) as int) as '1314 Ignite Active',
WHERE constituency=50 and cc.start_dt between DATEADD(yyyy,-4,fy.start_dt) And DATEADD(yyyy,-1,fy.end_dt) and coalesce(cc.end_dt,DATEADD(yyyy,-1,fy.end_dt)) >= DATEADD(yyyy,-1,fy.start_dt)) as int) as '1415 Ignite Active',
WHERE constituency=50 and cc.start_dt between DATEADD(yyyy,-4,fy.start_dt) And fy.end_dt and coalesce(cc.end_dt,fy.end_dt) >= fy.start_dt) as int) as '1516 Ignite Active'
Best,
Lisa
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Melissa ScottSent: Tuesday, March 22, 2016 11:34 AMTo: llindvall@cfl.rr.comSubject: Re: [Tessitura Technical Forum] SQL help for a dashboard
I have a lot with Null end dates because they haven't aged out of the program yet. The constituency is updated monthly and I put an end date on anyone who's birthday was the month before and meets the top of the age requirement so the number of active members change pretty regularly. If someone ages out during the fy, I want to count them as an active member during that fy year. Since the constituency is based on a birth date recorded in attributes we will hopefully not have any "forever 21's".
From: Gawain Lavers <bounce-gawainlavers9512@tessituranetwork.com>Sent: 3/22/2016 11:14:42 AM
Oh, and you want to use AND.
So:
[start date during or before fiscal year] and [end date is null or before the end of fiscal year]
So, do you have any with null end dates? What does that mean about a person, and how do you want them counted? Seems like you'd want a separate bucket for "customer seems to be forever 21".
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!