Attendance System Query
  • Hello i am working on attendance system using c# and sql server for the first time i am facing a problem mentioned below

    i have some records like this


    Studen_Name  |  Attendance  |  Class

    Jones                   P                 Grade 1
    Smith                   P                 Grade 1
    Raven                   A                 Grade 1
    Jash                     L                  Grade 1


    now i want this retrive this record


    Class      |    NoOfStduent  |    NoOfPresents  |     NoOfAbsents     |      NoOfLeaves

    Grade 1             4                            2                       1                             1


    Is this possible using case ?


    hussainroyal
  • 2 Comments sorted by
  • Hello Waqas Thanks for your Help i make this Query by Using case and its working


    SELECT  A.ID,A.Class,A.Date, Count(StudentName) NoOfStduent ,SUM(Presents) NoOfPresents ,SUM(Absents) NoOfAbsents ,SUM(Leaves) NoOfLeaves
    FROM
    (
        SELECT
            A.ID,FS.Class,AA.StudentName,A.Date,
            1 as Students,
            CASE WHEN Status = 'P' THEN 1 ELSE 0 END AS Presents,
            CASE WHEN Status = 'A' THEN 1 ELSE 0 END AS Absents,
            CASE WHEN Status = 'l' THEN 1 ELSE 0 END AS Leaves
        FROM    tblAttendance A
    INNER JOIN tblAttendanceDetail AD on AD.id=A.Id
    INNER JOIN tblAdmission AA on AA.id=AD.Student
    INNER JOIN tblFeeStatus FS on FS.id=A.Class
    ) as a
    GROUP BY Class,A.ID,A.Date
    hussainroyal

Howdy, Stranger!

It looks like you're new here. If you want to get involved, or you want to Ask a new Question, Please Login or Create a new Account by Clicking below

Login with Facebook

Popular Posts of the Week

    Optimum Creative