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
Unknown said: We use a constituency and the patron's birthday to determine their eligibility.
We use a constituency and the patron's birthday to determine their eligibility.
I have found the less time spent querying TX_CONST_CUST the better, i.e. if I were you, I'd use VX_CONST_CUST_ACTIVE for finding active constituents and whatever logic you're using to inactivate them to find inactives, e.g. "birthdate attribute > SomeDate", at. al.
I would also avoid any hardcoded dates in your code: assuming the above dates are FY based, I would always find them in TR_BATCH_PERIOD.