(Rick Rutt is a system architect living and working in Midland, Michigan.)
this is relatively straightforward: there exists a SQL function called
months_between which determines the number of months between two dates,
e.g. "select months_between(a.hire_date,sysdate) from my_table a"
Hope this is what you are looking for.
-- These are my own opinions, not Oracle's
This will calculate the months a person has worked. You may wish to
round or truncate this value depending on your needs.
Need to get column headers in SQL for months regardless of whether
DB is public school data system for large city - 55 schools. Columns
are Sep through Jun, taken from transaction date (using Oracle
function) for a student. Rows are various categories. Body of table
is student count for that school and category by month but there is
not necessarily a student count for every category for every school
every month. However, school board wants all tables to line up
visually vertically, even if no data. Result table being used by
another tool (Business Objects) for display.
Thought of using in line table, e.g.
(Select 'Jan','Feb','Mar', etc from SYS.DUAL) etc.
table1.school_year = 'YY', -- where YY is school year,
-- e.g. Sep 2000 - Jun 2001, YY = '00'
What do I need here? I understand outer joins but how do I outer join
an in line table?
Note that the selection for school year is NOT based on the
transaction date. school_year is column in source table. All that is
necessary is that the months taken from transaction date can be
matched to the in line table, or whatever is used.
Any ideas? I am not SQL guru, so this may seem simple to some.