--Set up pagesize parameters SET NEWPAGE 0 SET PAGESIZE 55 --Set the linesize, which must match the number of equal signs used SET LINESIZE 61 --Set up page headings and footings TTITLE CENTER "The Fictional Company" SKIP 3 - LEFT "I.S. Department" - RIGHT "Project Hours and Dollars Report" SKIP 1 - LEFT "=============================================================" BTITLE LEFT "=============================================================" - SKIP 1 - RIGHT "Page " FORMAT 999 SQL.PNO --Format the columns COLUMN employee_name HEADING "Employee Name" FORMAT A20 WORD_WRAPPED COLUMN project_name HEADING "Project Name" FORMAT A20 WORD_WRAPPED COLUMN hours_logged HEADING "Hours" FORMAT 9,999 COLUMN dollars_charged HEADING "Dollars|Charged" FORMAT $999,999.99 --Turn off feedback and set TERMOUT off to prevent the --report being scrolled to the screen. SET FEEDBACK OFF SET TERMOUT OFF --Execute the query to generate the report. SPOOL proj_hours_dollars.lst SELECT e.employee_name, p.project_name, SUM(ph.hours_logged) hours_logged, SUM(ph.dollars_charged) dollars_charged FROM employee e INNER JOIN project_hours ph ON e.employee_id = ph.employee_id INNER JOIN project p ON p.project_id = ph.project_id GROUP BY e.employee_id, e.employee_name, p.project_id, p.project_name; SPOOL OFF EXIT The Fictional Company I.S. Department Project Hours and Dollars Report ============================================================= Dollars Employee Name Project Name Hours Charged -------------------- -------------------- ------ ------------ Marusia Churai Data Warehouse 20 $3,380.00 Maintenance Mykhailo Hrushevsky Corporate Web Site 9 $1,215.00 Mykhailo Hrushevsky Accounting System 15 $2,025.00 Implementation Mykhailo Hrushevsky Data Warehouse 4 $540.00 Maintenance Pavlo Chubynsky Accounting System 24 $5,280.00 Implementation Pavlo Chubynsky Data Warehouse 17 $3,740.00 Maintenance Taras Shevchenko Data Warehouse 20 $2,000.00 Maintenance Mykhailo Verbytsky Enterprise Resource 16 $4,800.00 Planning System Marusia Churai Accounting System 24 $4,056.00 Implementation Lesia Ukrainka Accounting System 24 $1,080.00 Implementation Mykhailo Hrushevsky Enterprise Resource 8 $1,080.00 Planning System Pavlo Virsky VPN Implementation 12 $1,188.00 Pavlo Virsky Enterprise Resource 8 $792.00 Planning System Pavlo Virsky Data Warehouse 4 $396.00 Maintenance Mykola Leontovych Corporate Web Site 16 $1,936.00 Mykola Leontovych Accounting System 16 $1,936.00 Implementation Mykola Leontovych VPN Implementation 16 $1,936.00 Mykola Leontovych Data Warehouse 9 $1,089.00 Maintenance ============================================================= Page 1 The Fictional Company I.S. Department Project Hours and Dollars Report ============================================================= Dollars Employee Name Project Name Hours Charged -------------------- -------------------- ------ ------------ Igor Sikorsky Corporate Web Site 4 $280.00 Igor Sikorsky Enterprise Resource 8 $560.00 Planning System Mykhailo Verbytsky Data Warehouse 16 $4,800.00 Maintenance Marusia Churai VPN Implementation 28 $4,732.00 Marusia Churai Enterprise Resource 24 $4,056.00 Planning System Lesia Ukrainka Corporate Web Site 20 $900.00 Lesia Ukrainka VPN Implementation 28 $1,260.00 Pavlo Chubynsky VPN Implementation 28 $6,160.00 Pavlo Chubynsky Enterprise Resource 23 $5,060.00 Planning System Ivan Mazepa Corporate Web Site 9 $756.00 Ivan Mazepa Enterprise Resource 11 $924.00 Planning System Ivan Mazepa Data Warehouse 9 $756.00 Maintenance Taras Shevchenko Corporate Web Site 20 $2,000.00 Mykhailo Verbytsky Corporate Web Site 16 $4,800.00 Mykhailo Hrushevsky VPN Implementation 13 $1,755.00 Ivan Mazepa VPN Implementation 13 $1,092.00 Mykola Leontovych Enterprise Resource 11 $1,331.00 Planning System Lesia Ukrainka Enterprise Resource 24 $1,080.00 Planning System Taras Shevchenko Enterprise Resource 24 $2,400.00 Planning System Igor Sikorsky Accounting System 8 $560.00 Implementation Igor Sikorsky VPN Implementation 12 $840.00 Mykhailo Verbytsky VPN Implementation 16 $4,800.00 Marusia Churai Corporate Web Site 20 $3,380.00 Pavlo Virsky Accounting System 8 $792.00 ============================================================= Page 2 The Fictional Company I.S. Department Project Hours and Dollars Report ============================================================= Dollars Employee Name Project Name Hours Charged -------------------- -------------------- ------ ------------ Implementation Lesia Ukrainka Data Warehouse 20 $900.00 Maintenance Pavlo Chubynsky Corporate Web Site 20 $4,400.00 Pavlo Virsky Corporate Web Site 4 $396.00 Ivan Mazepa Accounting System 15 $1,260.00 Implementation Taras Shevchenko Accounting System 24 $2,400.00 Implementation Taras Shevchenko VPN Implementation 28 $2,800.00 Igor Sikorsky Data Warehouse 4 $280.00 Maintenance Mykhailo Verbytsky Accounting System 16 $4,800.00 Implementation ============================================================= Page 3