Post reply

Query Req: student & attendance table

  • March 27, 2011 at 6:58 am

    #248645

    Iam a fresh here

    I have a table Attendance & student ..

    I want to fetch the records at the end of month that date with studentid which are not present on particualar days ,

    student table:

    id name

    1 waqar

    2 atif

    3 suresh

    4 dhoni

    5 afridi

    attendance table: here studentID is a Foreign key

    id Date studentID

    1 3/27/2011 10:46:04 AM 1

    2 3/27/2011 10:46:04 AM 2

    3 3/27/2011 10:46:04 AM 4

    4 3/27/2011 10:46:04 AM 5

    5 3/26/2011 10:46:04 AM 2

    6 3/26/2011 10:46:04 AM 3

    7 3/26/2011 10:46:04 AM 4

    8 3/25/2011 10:46:04 AM 1

    9 3/25/2011 10:46:04 AM 5

    10 3/24/2011 10:46:04 AM 3

    11 3/24/2011 10:46:04 AM 1

    12 3/24/2011 10:46:04 AM 4

    13 3/23/2011 10:46:04 AM 2

    14 3/22/2011 10:46:04 AM 4

    15 3/22/2011 10:46:04 AM 3

    16 3/22/2011 10:46:04 AM 5

    I could not do this

  • March 27, 2011 at 7:10 am

    #1304050

    Make a calendar table, select the range from that table and then left join on attendance and students

  • March 27, 2011 at 7:22 am

    #1304051

    fetching from 3 tables ,,i think performance issue will occurred .

  • March 27, 2011 at 7:35 am

    #1304052

    Did you try? Is there a performance issue?

    SQL is capable of retrieving data from far more than three small tables without difficulty.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 27, 2011 at 2:05 pm

    #1304114

    waqarnaeem2 (3/27/2011)

    Iam a fresh here

    I have a table Attendance & student ..

    I want to fetch the records at the end of month that date with studentid which are not present on particualar days ,

    student table:

    id name

    1 waqar

    2 atif

    3 suresh

    4 dhoni

    5 afridi

    attendance table: here studentID is a Foreign key

    id Date studentID

    1 3/27/2011 10:46:04 AM 1

    2 3/27/2011 10:46:04 AM 2

    3 3/27/2011 10:46:04 AM 4

    4 3/27/2011 10:46:04 AM 5

    5 3/26/2011 10:46:04 AM 2

    6 3/26/2011 10:46:04 AM 3

    7 3/26/2011 10:46:04 AM 4

    8 3/25/2011 10:46:04 AM 1

    9 3/25/2011 10:46:04 AM 5

    10 3/24/2011 10:46:04 AM 3

    11 3/24/2011 10:46:04 AM 1

    12 3/24/2011 10:46:04 AM 4

    13 3/23/2011 10:46:04 AM 2

    14 3/22/2011 10:46:04 AM 4

    15 3/22/2011 10:46:04 AM 3

    16 3/22/2011 10:46:04 AM 5

    I could not do this

    Since you're new here, please take a look at the article at the first link in my siganture line below. You'll get better answers much more quickly. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 27, 2011 at 3:28 pm

    #1304131

    waqarnaeem2 (3/27/2011)

    fetching from 3 tables ,,i think performance issue will occurred .

    No offense intented, but I run reports that basically read from 50% of the data in the whole freaking database. The server has 4 gb, 1 processor, dual core.

    How long to run those 49 queries???? 9 seconds, 1 M reads in ram are done during that time frame.

    At absolute worse you'll be getting 1000 reads for this query. So that could / should run in less than 10 ms assuming it's coded right.

    Like Gail said, have you actually run the query? Is it really so slow??

  • March 28, 2011 at 12:06 am

    #1304170

    Now i tried ,

    Thanks to you all

  • March 28, 2011 at 12:22 am

    #1304175

    waqarnaeem2 (3/28/2011)

    Now i tried ,

    Thanks to you all

    So show us what you tried. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply