Real Software Forums
http://forums.realsoftware.com/

SQL Help needed
http://forums.realsoftware.com/viewtopic.php?f=3&t=47910
Page 1 of 1

Author:  Jym [ Fri May 17, 2013 6:40 pm ]
Post subject:  SQL Help needed

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

Author:  timhare [ Fri May 17, 2013 7:13 pm ]
Post subject:  Re: SQL Help needed

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.

Author:  DaveS [ Fri May 17, 2013 7:55 pm ]
Post subject:  Re: SQL Help needed

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.

Author:  Jym [ Fri May 17, 2013 7:58 pm ]
Post subject:  Re: SQL Help needed

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.

Author:  Jym [ Fri May 17, 2013 8:01 pm ]
Post subject:  Re: SQL Help needed

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?

Author:  DaveS [ Fri May 17, 2013 8:19 pm ]
Post subject:  Re: SQL Help needed

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

Author:  DaveS [ Fri May 17, 2013 8:22 pm ]
Post subject:  Re: SQL Help needed

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

Author:  timhare [ Fri May 17, 2013 8:37 pm ]
Post subject:  Re: SQL Help needed

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

Author:  Jym [ Fri May 17, 2013 9:10 pm ]
Post subject:  Re: SQL Help needed

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 :)

Author:  Jym [ Mon May 20, 2013 2:59 pm ]
Post subject:  Re: SQL Help needed

FYI it runs 3 times faster if I use WITH instead of the actual tables.

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/