Results 1 to 10 of 10

Thread: Need Query

  1. #1
    Join Date
    Aug 2009
    Posts
    37

    Unanswered: Need Query

    Hi I need query

    I have table like

    Sid day intime outtime
    1 Monday Apr 14 2010 3:40PM Apr 14 2010 5:40PM
    1 Tuesday Apr 15 2010 5:00PM Apr 15 2010 7:00PM
    1 Wednesday Apr 16 2010 5:00PM Apr 16 2010 7:00PM

    I want ouput like this

    sid Monday Tuesaday wednsday thursday friday saturday sunday
    1 P P P A A


    please can any one help me

    Thanks in advance
    Ramu
    Last edited by bandiramireddy; 04-14-10 at 08:31.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    select mon=sum(case ...), tue=sum(case ...)
    group by sid

  3. #3
    Join Date
    Aug 2009
    Posts
    37
    hi PDreyer
    can u give me query clearly

    thanks
    Rami Reddy

  4. #4
    Join Date
    Apr 2010
    Location
    Charlotte, NC
    Posts
    11
    Try this:

    SELECT
    [sid]
    , CASE WHEN [day]='Monday' THEN 'P' END AS Monday
    , CASE WHEN [day]='Tuesday' THEN 'P' END AS Tuesday
    , CASE WHEN [day]='Wednesday' THEN 'P' END AS Wednesday
    , CASE WHEN [day]='Thursday' THEN 'P' END AS Thursday
    , CASE WHEN [day]='Friday' THEN 'P' END AS Friday
    , CASE WHEN [day]='Saturday' THEN 'P' END AS Saturday
    , CASE WHEN [day]='Sunday' THEN 'P' END AS Sunday
    FROM tableName
    ---
    Shannon Lowder
    Database Engineer
    http://toyboxcreations.net

  5. #5
    Join Date
    Apr 2010
    Location
    Charlotte, NC
    Posts
    11
    Wait, That will not combine it into one query. But this should:

    SELECT
    [sid]
    , MAX(CASE WHEN [day]='Monday' THEN 'P' ELSE 'A' END) AS Monday
    , MAX(CASE WHEN [day]='Tuesday' THEN 'P' ELSE 'A' END) AS Tuesday
    , MAX(CASE WHEN [day]='Wednesday' THEN 'P' ELSE 'A' END) AS Wednesday
    , MAX(CASE WHEN [day]='Thursday' THEN 'P' ELSE 'A' END) AS Thursday
    , MAX(CASE WHEN [day]='Friday' THEN 'P' ELSE 'A' END) AS Friday
    , MAX(CASE WHEN [day]='Saturday' THEN 'P' ELSE 'A' END) AS Saturday
    , MAX(CASE WHEN [day]='Sunday' THEN 'P' ELSE 'A' END) AS Sunday
    FROM tableName
    GROUP BY
    [sid]

    What this will do is if there is ever a Monday with an entry, the record will show a P for that sid. If there never is a Monday record, then you will get an A.

    Now, I see you have two DATETIME fields. The trick for you will be, do you want this do go week by week and show you Present or Absent? If so, then the query I posted won't do what you need. If you need that, you'll also have to create a group for each week.

    Does that make sense?
    ---
    Shannon Lowder
    Database Engineer
    http://toyboxcreations.net

  6. #6
    Join Date
    Aug 2009
    Posts
    37
    Hi
    Thanks Shannon Lowder,that query was very help full to me.

    Thanks
    rami Reddy

  7. #7
    Join Date
    Apr 2010
    Location
    Charlotte, NC
    Posts
    11
    Just let me know if I can be of any further assistance!
    ---
    Shannon Lowder
    Database Engineer
    http://toyboxcreations.net

  8. #8
    Join Date
    Aug 2009
    Posts
    37
    Hi ,
    ya I need one more help from you,
    how to achieve aboue problem with the keyword 'PIVOT' key and also at last i need to display how many days student present and student absent in that week.'

    Thanks
    Rami Reddy.B

  9. #9
    Join Date
    Apr 2010
    Location
    Charlotte, NC
    Posts
    11
    To use the PIVOT command, check this out: Using PIVOT and UNPIVOT I'm not 100% sure how you want to implement it.

    As for how to display how many students are absent in that week, you'll need to be able to group by the weeks. Have you found out how to do that yet? If not, check out DATEPART (DATEPART) and use the wk option for week.

    Then instead of
    MAX(CASE WHEN [day]='Monday' THEN 'P' ELSE 'A' END) AS Monday
    you would use
    count(*) as PresentStudents
    and then you'll need to use a variable to store the number of students.
    DECLARE @studentCount int
    SELECT @studentCount = COUNT(DISTINCT sid) FROM tableName
    Then in your query you can have the column
    7 * @studentCount - count(*) as AbsentStudents
    .

    Hopefully that sends you down the right path. If not, let me know!
    ---
    Shannon Lowder
    Database Engineer
    http://toyboxcreations.net

  10. #10
    Join Date
    Aug 2009
    Posts
    37
    Hi
    thank U Shannon Lowder

    Ragards
    Rami Reddy.B

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •