Classical Complex challenging Asp and SQL problem

[Guest]

I am woring on a project that involves a common but quite challanging and
complex problem. I describe the scenario with a common example.

School Attendence website:
The school maintains records of attendence of students for each subject
periods in a table with format like

StudentName | Subject | AttendenceDate
ABC | Math | 1/1/2005
ABC | Science | 1/1/2005
BCD | Math | 1/1/2005
CDE | Science | 1/1/2005
..
..
CDE | Math | 31/12/2005

The project needs to present attandence record in the form of table on asp
webpage for different intervals:

Today
This week
This Month
This year

The format should be:

------------------------------------------------------------------
StudentName | 1/1/2005 | 2/1/2005 | ..... | 31/12/2005
------------------------------------------------------------------
ABC | 2 | 1 .....
BCD | 2 | 0 .....
..
..
..
-------------------------------------------------------------------

The data should be presented by Date (Horizontaly) and Number(count) of
attendence for that day for each student (Vertically).

The interval is selected by user and can be Today, This Month, This year

How can be this problem solved in effecient way. Performance is to be
considered for the solution as the real website will be viewed by lots of
users each day.

Thanks in advance

 

[Mark Rae]

How can be this problem solved in effecient way.

You don't mention what RDBMS you're using, but it sounds like you need a
crosstab query...
http://sqljunkies.com/WebLog/mattnunn/archive/2003/08/07/173.aspx

 

[Guest]

I am using SQL Server 2000

I looked on links you mentioned.

There are few problems with that:

1. Generating an SQL Select query by using the values in Group field will
not help as it will excede the limit of 8000 characters (for whole year
attendence)

2. If I create an new table with date as colums, then if two clients execute
the query at samt time, the temporary table will be overwritten

:?

Regards

 

[Mark Rae]

1. Generating an SQL Select query by using the values in Group field will
not help as it will excede the limit of 8000 characters (for whole year
attendence)

No it won't...

SELECT REPLICATE('0', 8000) AS Text1, REPLICATE('0', 8000) As Text2

2. If I create an new table with date as colums, then if two clients
execute
the query at samt time, the temporary table will be overwritten

No it won't...