Solved

Need to simplify this query

Posted on 2013-06-07
19
375 Views
Last Modified: 2013-06-18
Hi, experts.

I am converting another ACE report to Argos, and this one is particularly hairy.  I am finding the average application rate of students for a three year period in each major.  Now, keeping in mind that any given year could have 0 registrations (i.e. no records) in the table.  I have come up with a working solution, but with 27 majors, this solution will easily give me 200+ lines of SQL for one report.  Can anyone simplify it for me?

case
when
((select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0)
then 
(select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')

when
((select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0)
then 
(select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')

when
((select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1)
then 
(select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')

when
((select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0)
then 
((select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')
+
(select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*'))/2

when
((select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1)
then 
((select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')
+
(select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*'))/2

when
((select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 0 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1)
then 
((select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')
+
(select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*'))/2

when
((select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1 and
(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1)
then 
((select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')
+
(select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2011 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')
+
(select (sum(b.applied_cnt)/sum(b.applied_all))*100 as appcnt from lsrecruit_rec b
where (b.plan_enr_yr = 2012 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*')
)/3

else null
end actappavg

Open in new window

0
Comment
Question by:Barry62
  • 9
  • 4
  • 4
  • +2
19 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39230308
In order for us to help you, you must post a test case as follows:

1) Create table(s) statements
2) Insert statements with the test data
3) Expected results
4) Explanation of the requirements and rules that lead to that result.

:p
0
 
LVL 8

Author Comment

by:Barry62
ID: 39230462
OK, well I am not inserting any data.  This is just a for a report, so I am only doing SELECT's.

Here is some data:

lsrecruit_rec

plan_enr_yr      plan_enr_sess       major         applied_cnt          accepted_cnt
2010                       'FA'                'ACT1'                100                       36  
2010                       'FA'                'ACT3'                  40                       18
2012                       'FA'                'ACT2'                  30                       10
2011                       'SP'                'AMT '                 150                     100    

So I am needing to find the average rate of accepted students to applied students over a three year period for each major.  

The lsrecruit_rec is populated when the report is run.  It gathers admissions data from other tables and places it in this table, summing on the status of the student(Inquired,Applied,Accepted, etc.) based on their major and application year.  If the student's status is 'applied', the report adds it to the applied_cnt for that major.

The problem is that if there are no applications for a particular year/major, there is no record.  That's  why I need to test for no records in each year/major.  If there are records for 2010, and not for 2011 or 2012, hen I just display accepted_cnt/aplied_cnt for 2010.  If there are records for 2010 and 2012, but none for 2011, I average accepted_cnt/aplied_cnt for 2010 and accepted_cnt/aplied_cnt for 2012.  And so on and so on.  There are 8 combinations for each major.

See why I want to somplify it?
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39230487
And...
. . .
3) Expected results (based on your data)
0
 
LVL 8

Author Comment

by:Barry62
ID: 39230549
Based on the data I posted, I should get 37.64% for the combined majors ACT1, ACT2 and ACT3.  I should get 66.67% for AMT.
0
 
LVL 34

Expert Comment

by:James0628
ID: 39231392
I haven't used MySQL, Oracle or Informix, so maybe it's just me, but FWIW ...

 I'm confused by something.  You have tests like this:

(select count(*) as appcnt from lsrecruit_rec b where
(b.plan_enr_yr = 2010 and b.plan_enr_sess = 'FA') and b.major matches 'ACT*') = 1

 Isn't that checking to see if the count (ie. number of rows) is 1?

 If so, in your sample data, you have two rows that match those conditions, so that test would fail, wouldn't it?


 Also, in your SQL, you use applied_cnt and applied_all, but in your sample data you have applied_cnt and accepted_cnt.

 James
0
 
LVL 8

Author Comment

by:Barry62
ID: 39231606
Sorry.  I changed a few things and posted old code.  The count queries should be testing for 0 and applied_all should be accepted_cnt.
0
 
LVL 31

Expert Comment

by:awking00
ID: 39231799
>>Based on the data I posted, I should get 37.64% for the combined majors ACT1, ACT2 and ACT3.  I should get 66.67% for AMT.<<
Is that equivalent to I should get 37.64% for plan_enr_sess = 'FA'  I should get 66.67% for plan_enr_sess = 'SP'?
0
 
LVL 8

Author Comment

by:Barry62
ID: 39231990
I am only testing for 'FA' 2010-2013 right now.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39232396
Sorry.  I changed a few things and posted old code.  The count queries should be testing for 0 and applied_all should be accepted_cnt.
And where is the NEW code?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 8

Author Comment

by:Barry62
ID: 39232481
ummm....the NEW code is the OLD code with '= 0' for all of the count(*) queries, and 'accepted_cnt' in place of all the 'applied_all'.  I thought that was apparent.
0
 
LVL 34

Expert Comment

by:James0628
ID: 39232516
Not to be difficult, but if you have " '= 0' for all of the count(*) queries", then you're just doing the same tests in each When.

 James
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 250 total points
ID: 39233327
Why not just this:
WITH mytab AS
  (SELECT 2010 plan_enr_yr,
    'FA' plan_enr_sess,
    'ACT1' major,
    100 applied_cnt,
    36 accepted_cnt
  FROM dual UNION
  SELECT 2010,'FA','ACT3',40 ,18 FROM dual  UNION
  SELECT 2012,'FA','ACT2',30 ,10 FROM dual  UNION
  SELECT 2011,'SP','AMT ',150 ,100 FROM dual
  )
SELECT plan_enr_sess ,
  SUM(applied_cnt) app_cnt ,
  SUM(accepted_cnt) acc_cnt ,
  ROUND((SUM(accepted_cnt)*100/SUM(applied_cnt)),3) kk_pct
FROM mytab
GROUP BY plan_enr_sess

PLAN_ENR_SESS APP_CNT                ACC_CNT                KK_PCT                    
------------- ---------------------- ---------------------- ---------------------- 
SP            150                    100                    66.667                 
FA            170                    64                     37.647                 

2 rows selected

Open in new window

:?
0
 
LVL 8

Author Comment

by:Barry62
ID: 39233816
@James - The code is testing for three different years per major.  So with production data, I have to test for count = 0 every time.  If I don't, I will get a divide by 0 error and the code blows up.

@Mike - Isn't your code just feeding data into the queries?  I am pulling data from the tables , not planting it.  Or maybe I'm missing your point?
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 39233849
In simplified form, the code in your first post has a Case with

when
((select count(*) ... where b.plan_enr_yr = 2010 ...) = 1 and
(select count(*) ... where b.plan_enr_yr = 2011 ...) = 0 and
(select count(*) ... where b.plan_enr_yr = 2012 ...) = 0)
then
<calculate average using only 2010>

 and then a When that checks if the count for 2010 is 0, 2011 is 1 and 2012 is 0 and, if so, calculates the average using only 2011; and so on, checking the various combinations of years with and without data.

 But then you said that you only have "= 0" now, which means that you're just doing the same tests in each When, which wouldn't make sense.  And looking for a 0 count for all 3 years wouldn't make sense either.

 So, I suspect that something got lost in translation somewhere.


 FWIW, as far as Mike's code goes, I think the first part was simply generating test data for the SELECT that starts at line 12.  Note the KK_PCT column in the results.  Are those the %'s that you're looking for?

 James
0
 
LVL 8

Author Comment

by:Barry62
ID: 39242515
yes, they are
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39243099
What is your database and version? MySQL or Oracle?
0
 
LVL 34

Expert Comment

by:James0628
ID: 39243817
If the %'s from Mike's query are correct, then you could try his query (starting at line 12) and replace mytab with your table, and see what you get.  If it gives you what you need, great.  If not, let him (us) know what's wrong and maybe he (we) can fix it.

 James
0
 
LVL 8

Author Comment

by:Barry62
ID: 39244280
@Sharath - It is actually Informix, which I guess is closest to Oracle?

@James & Mike - I will let you  (both) know how it goes.
0
 
LVL 8

Author Comment

by:Barry62
ID: 39256992
Thanks, guys.  Mike's query worked.  I had to modify it a bit to separate the majors, but all of the percentages are correct with much less code.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Creating and Managing Databases with phpMyAdmin in cPanel.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now