Re: SQL for all months in range, data or not

I would recommend creating a calendar table (i.e. with all the months of the year, days of the year). Populate it using a stored procedure. Use an outer join to display all months, whether or not they have students.

Say you had created a calendar table called CALENDAR_MONTH with all the months of the year in a format YYYY-MM.

you could use sql like this:

SELECT

1. the_month, count(*) FROM CALENDAR_MONTH A, STUDENT_SCHEDULE B WHERE to_char(b.COURSE_DATE, 'YYYY-MM') (+) = A.the_month group by A.the_month

bill milbratz

"Eischeid" <tge_at_fastrus.com> wrote in message news:8a14d259.0107251554.6547932c_at_posting.google.com...
> Hi,
>
> Need to get column headers in SQL for months regardless of whether
> data exists.
>
> 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
> data1,
> data2,
> etc.
> FROM
> table1,
> table2,
> (Select 'Jan','Feb','Mar', etc from SYS.DUAL) etc.
> WHERE
> 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.
>
> Thanks.
>
> Tom Eischeid
Received on Thu Jul 26 2001 - 16:29:52 CEST