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
Don't you want the end date to be less than or equal to the end of the fiscal year date?
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".
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".