Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: select employeeName that are present.

    Hello gurus, am new to sql queries, but need to query some results from a view.
    i have this columns in vw_SchoolLogs view;
    EmployeeName datatype = varchar, AttendanceDate datatype = DateTime, Status datatype = Varchar(Present or absent).

    The objective is to select employeeName that are present for a particular date, and if any employeeName is absent for that date, the query should also include result of the last date the employeeeName was present.

    Kindly help, am new to sql programing

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    What have you done this far with an SQL statement. Most folks don't mind helping, as long as you put forth some effort.
    Dave Nance

  3. #3
    Join Date
    Jun 2011
    Posts
    2

    so far

    SELECT employeeName, AttendanceDate, Status
    FROM vw_SchoolLogs
    WHERE (AttendanceDate LIKE CONVERT(DATETIME, '2011-06-01 00:00:00', 102))

    this query will only return for the date/day specified, but wont show d last date/day present the employeeName was absent.
    Last edited by ofemino79; 06-27-11 at 10:00. Reason: wrong declearation

  4. #4
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    What you want to do is get the maximum date for each employee where the status was present - so your first criteria is selecting all records with the present status. Second criteria is records with an attendance date less than or equal to your search date (2011-06-01 in your example)

    What you could then do is use the MAX aggregate function to get the maximum date for each employee. For those present on the given date it will be that date, for all others it will be the maximum date value less than your given date where the status was present.

    Have a go at putting that into code.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    On top of what EngadaSQL suggested, have a look at the definition of ROW_NUMBER() OVER
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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