Thanks in advance for any help you might offer.
I've been wracking my brain for some time now. Here's the two tables I'm dealing with.
SSN CHAR(9) NOT NULL
SCAN_CODE CHAR(6) NOT NULL
DATE_DUE DATE NOT NULL
TODAYS_DATE DATE NOT NULL *this is a fixed date of 06JAN00*
My issue is that I need to provide the SSN, total number of books checked out (which is any scan_code in the CIRC table), total number of books overdue (which is any scan_code in CIRC table that has a DATE_DUE that is before TODAYS_DATE), and figure up the total fine for each patron (which is the SSN). The formula for the fine's is the days overdue * .15.
Here is what I have thus far....
SELECT SSN, COUNT(SCAN_CODE) "BOOKS OUT"
GROUP BY SSN
*this gives me each patron with a count of all books checked out for
SELECT SSN, NULL, COUNT(SCAN_CODE) "BOOKS OVERDUE",
(SUM(TODAYS_DATE - DATE_DUE) * .15) "TOTAL FINE"
FROM CIRC, CURRENT_DATE
WHERE TODAYS_DATE > DATE_DUE
GROUP BY SSN
*this gives me each patron with a count of overdue books and the total *fine for that patron
However, I need to put these together somehow into one nice piece of output. I've tried doing a union, and was told the NULL's would hold the place for the other values, but I guess that won't work with aggregates. I am at a loss. Can anyone help me? Even just some info on how to better use aggregates in a union would help. Hell, encouraging words would help at this point :-D
[b]Discipline[/b] - The desire to do the right thing in the absence of both leadership and consequence.