Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Sun Aug 20, 2017 8:35 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 10 posts ] 
Author Message
 Post subject: SQL Help needed
PostPosted: Fri May 17, 2013 6:40 pm 
Offline

Joined: Sat Oct 01, 2005 5:19 pm
Posts: 3216
I can't figure this out for the life of me. I can do it in RB but would really prefer having the database engine do it so I don't have to walk through 28,000 students taking anywhere from 8 to 45 courses.

Using Postgres
I have 1 view and 1 table
student_grades_view(grades_student_rowid INTEGER, course TEXT, date_taken DATE) //there are more but not important to the SQL
deans_list(deans_student_rowid INTEGER, date_approved DATE)

I need the count of the courses taken after the last time the student was on the Dean's List. If they have never been on the list the result would be all the courses the student has taken since they started.

example data: student_grades
1 | b1 | 2012-01-01
1 | b1 | 2011-01-01
1 | b1 | 2010-01-01
1 | b1 | 2010-01-01

2 | b1 | 2012-01-01

3 | b1 | 2012-01-01
3 | b1 | 2011-01-01
3 | b1 | 2010-01-01
3 | b1 | 2009-01-01
3 | b1 | 2008-01-01

deans_list
1 | 2010-01-01
3 | 2008-01-01
3 | 2010-05-05

result recordset
1 | 2 //student 1 has taken 2 courses since 2010-01-01
2 | 1 //student 2 has never been on the list all his courses count
3 | 2 //student 3 we only care about after 2010-05-05 and has taken 2 course after that

Anyone have a clue how I SQL this?

TIA


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL Help needed
PostPosted: Fri May 17, 2013 7:13 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
select grades_student_rowid, count(*) from student_grades_view
where date_taken > (select max(date_approved) from deans_list where deans_student_rowid = grades_student_rowid)
group by grades_student_rowid

You'll have to deal with a null returned from the subquery. I don't know which postgres function you would use for that. mySQL has an IFNULL() function you would use. I don't know the postgres equivalent.


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL Help needed
PostPosted: Fri May 17, 2013 7:55 pm 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
SELECT grades_student_rowid,COUNT(*) AS course_cnt
FROM STUDENT_GRADES,
DEANS_LIST
WHERE grades_student_rowid=deans_student_rowid
AND date_taken>date_approved
GROUP BY grades_student_rowid
UNION
SELECT Grades_student_rowid,COUNT(*) AS course_cnt
FROM STUDENT_GRADES
WHERE NOT EXISTS(SELECT 8
FROM DEANS_LIST
WHERE grades_student_rowid=deans_student_rowid)
GROUP BY grades_student_row_id;


Tim's code didn't cover if they had NEVER been on deans list before.

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL Help needed
PostPosted: Fri May 17, 2013 7:58 pm 
Offline

Joined: Sat Oct 01, 2005 5:19 pm
Posts: 3216
Hi Tim,
Thanks I tried that one last night sometime, but tried again and it's only giving me 1 row of data. So I'm guessing the MAX is the issue, I've tried joins, and you name it, I can't figure out what's wrong.


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL Help needed
PostPosted: Fri May 17, 2013 8:01 pm 
Offline

Joined: Sat Oct 01, 2005 5:19 pm
Posts: 3216
Hi Dave, I'm going to try that in about 20 mins. does it take into consideration a student can have up to 4 dates in the deans list and I only care about the latest date?


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL Help needed
PostPosted: Fri May 17, 2013 8:19 pm 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
No I missed that requirement.... but basically take what Tim had
and the portion of mine from UNION on.... append them into one query and that should work

The part before the UNION counts the students on the Deans list... after the Union is students that were never on the list

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL Help needed
PostPosted: Fri May 17, 2013 8:22 pm 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
try this

SELECT grades_student_rowid,COUNT(*) AS course_cnt
FROM STUDENT_GRADES,
DEANS_LIST a
WHERE grades_student_rowid=deans_student_rowid
AND date_taken>date_approved
AND date_approved=(SELECT MAX(date_approved)
FROM DEANS_LIST b
WHERE a.deans_student_rowid=b.deans_student_rowid)
GROUP BY grades_student_rowid
UNION
SELECT Grades_student_rowid,COUNT(*) AS course_cnt
FROM STUDENT_GRADES
WHERE NOT EXISTS(SELECT 8
FROM DEANS_LIST
WHERE grades_student_rowid=deans_student_rowid)
GROUP BY grades_student_row_id;


notice that DEANS_LIST is used TWICE in the top part (so make sure you add the A and B alias)

This is off the top of my head... so you might need to tweek it some



and yes that is "SELECT 8" not "SELECT *" in that one line..... no need to fill a data vector if you just care if it exists or not

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL Help needed
PostPosted: Fri May 17, 2013 8:37 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
Looks like postgres supports coalesce, so

select grades_student_rowid, count(*) from student_grades_view
where date_taken > coalesce((select max(date_approved) from deans_list where deans_student_rowid = grades_student_rowid),'1900-01-01')
group by grades_student_rowid


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL Help needed
PostPosted: Fri May 17, 2013 9:10 pm 
Offline

Joined: Sat Oct 01, 2005 5:19 pm
Posts: 3216
Thank you both, both work perfectly and produce the exact same recordset. I can now get in the car and go up to the cottage where there's no internet for the rest of the long weekend and work on the GUI :)


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL Help needed
PostPosted: Mon May 20, 2013 2:59 pm 
Offline

Joined: Sat Oct 01, 2005 5:19 pm
Posts: 3216
FYI it runs 3 times faster if I use WITH instead of the actual tables.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 10 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group