What you are trying to make is called either a "pivot table" or a
"crosstab report". There is a very simple pattern to writing one. I am
assuming that all of the data is on just one table and that there can be
one or more entries per person per program per month. I know these do not
line up with your column names but you didn't post your table structure
with your question.
SELECT perid
, progid
, sum(if(month=1, 1, 0)) as m1
, sum(if(month=2, 1, 0)) as m2
, sum(if(month=3, 1, 0)) as m3
, sum(if(month=4, 1, 0)) as m4
, sum(if(month=5, 1, 0)) as m5
, sum(if(month=6, 1, 0)) as m6
, sum(if(month=7, 1, 0)) as m7
, sum(if(month=8, 1, 0)) as m8
, sum(if(month=9, 1, 0)) as m9
, sum(if(month=10, 1, 0)) as m10
, sum(if(month=11, 1, 0)) as m11
, sum(if(month=12, 1, 0)) as m12
FROM attendancetable
WHERE year=2003
GROUP by perid, progid
That query will show you how often a person attended a program during
2003. Modify it as necessary to work with your data.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
<[EMAIL PROTECTED]> wrote on 11/23/2004 12:00:13 AM:
> Hi!
>
> I am wondering if there is a way using SQL to make a pattern file (I'm
not
> sure exactly what to call it) of the sort following, which keeps track
of
> people in programs of different kinds, by months of the year. A given
file
> can be set up as below examining only 1 type of program (that is what
I'm
> doing for now) or multiple types (by using a "1" for one type of
program, a
> "2" for another type, etc.).
>
> perid m1 m2 m3 m4 m5 m6 m7 m8 . . . m12
> 023 1 0 0 1 1 1 0 0
> 0
> 045 0 1 0 0 1 0 0 1
> 1
>
> It is just a list of id numbers and then for each month (m1, m2...m12) a
1
> is placed if the individual is in the program and a 0 if they are not.
The
> "pattern file" is used to help in the analysis of how people are using
> programs and cycling in and out of them.
>
> Thanks very much.
>
> -Alex
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
