Classical Complex challenging Asp and SQL problem

Discussion in 'ASP .Net' started by =?Utf-8?B?cmFtYXRh?=, Apr 30, 2005.

  1. 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
    =?Utf-8?B?cmFtYXRh?=, Apr 30, 2005
    #1
    1. Advertising

  2. =?Utf-8?B?cmFtYXRh?=

    Mark Rae Guest

    Mark Rae, Apr 30, 2005
    #2
    1. Advertising

  3. 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" wrote:

    > "ramata" <> wrote in message
    > news:...
    >
    > > 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
    >
    >
    >
    =?Utf-8?B?cmFtYXRh?=, May 1, 2005
    #3
  4. =?Utf-8?B?cmFtYXRh?=

    Mark Rae Guest

    "ramata" <> wrote in message
    news:...

    > 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...
    Mark Rae, May 3, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Eduardo Rosa

    (classical ASP question) Order a xml

    Eduardo Rosa, Jun 30, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    376
    Kevin Spencer
    Jun 30, 2004
  2. pankaj tiwary

    Calculate area for a classical problem

    pankaj tiwary, May 20, 2004, in forum: C Programming
    Replies:
    3
    Views:
    632
    Rouben Rostamian
    May 20, 2004
  3. c676228
    Replies:
    6
    Views:
    170
    c676228
    Oct 24, 2006
  4. c676228
    Replies:
    5
    Views:
    176
    c676228
    Mar 9, 2007
  5. c676228

    B2B dolution in classical ASP

    c676228, Apr 10, 2007, in forum: ASP General
    Replies:
    9
    Views:
    217
    Steven Cheng[MSFT]
    Apr 26, 2007
Loading...

Share This Page