CODEWITHSUNDEEP
c#asp.netsql-server-2008c#-4.0asp.net-4.0
LoneXcoder
LoneXcoder

Reputation: 2163

sql simple Query with dates

i have a time clock application that registers employes Attendance at work,

i was trying to set the visibility of "Enter" imageButton (entrance e.g "Timein" field ) to hidden based on a query

so if there was no timeout in last record that has a TimeIn for the current UserId then the Enter button will be hidden

this is the table : (tId is PK IDENTITY)

enter image description here

so based on those records for example , the last (top 1) is not signed out so the user will not get to see the Enter button, that allows user to sign in, cause there was no last action of TimeOut

working from asp.net C# code behind i was trying to work a solution using datedIff as a query :

but i guess i am missing something here

please don't take it to account if this is the wrong approach i just wanted to show.. i did try though if there's a better way to achive what i need then just disregard that query 

select isnull((SELECT TOP 1 case when [TimeOut] is null then '' else convert(nvarchar,[TimeOut]) end FROM tblTime WHERE datediff(day,TimeOut,getdate())=0 and UserId =3571 ORDER BY tId DESC),'')

Upvotes: 1

Views: 123

Answers (3)

JimmyTheOne
JimmyTheOne

Reputation: 233

Glenn, you beat me to the punch! The following will get the record with the most recent TimeIn (assuming there are no duplicates) and will provide you with a Y/N flag to determine whether to display the Enter button or not.

SELECT (CASE WHEN t1.[TimeOut] IS NOT NULL THEN 'Y' ELSE 'N' END) AS is_enter_button_displayed
  FROM tblTime t1
 WHERE t1.[TimeIn] = (SELECT MAX(t2.[TimeIn]) FROM tblTime t2 WHERE t2.UserId = t1.UserId)
   AND UserId = 3571;

Upvotes: 1

Dave Zych
Dave Zych

Reputation: 21897

If you want a query explicitly for this (Assuming the meaning of ActiveDate is when the user was last active on the site):

SELECT TOP 1 
    CASE WHEN TimeOut IS NULL THEN 0 ELSE 1 END 
FROM tblTime 
WHERE UserID = 3571
ORDER BY ActiveDate DESC

Otherwise if you're loading up the entire object into the code just check to see if the most recent record's TimeOut is null.

I would suggest though having some sort of UserActivity table which can track dates and times of when the user signed in and out. Keeping track of user activity by looking at null values isn't the most reliable.

CREATE TABLE UserActivity
(
    UserActivityID int,
    UserActivityTypeID tinyint,
    DateActivity datetime
)

With UserActivityType being another table holding Sign In and Sign Out, among other things you might want to track.

Upvotes: 1

Glenn
Glenn

Reputation: 9170

This finds the record with the highest tid for the userId.

SELECT CASE WHEN [TimeOut] IS NULL THEN '' ELSE CONVERT(NVARCHAR,[TimeOut]) END
  FROM tblTime
  WHERE tId = ( SELECT MAX(tId)
                  FROM tblTime
                  WHERE UserId = 3571
               )

but ids aren't a good choice for getting the most recent record. Probably you want something more along these lines, but it depends on the meaning of the columns and if there could be duplicates...:

SELECT CASE WHEN [TimeOut] IS NULL THEN '' ELSE CONVERT(NVARCHAR,[TimeOut]) end
  FROM tblTime
  WHERE UserId = 3571
    AND ActiveDate = ( SELECT MAX(ActiveDate)
                         FROM tblTime
                         WHERE UserId = 3571
                     )

or you could put the subquery as part of the FROM clause.

Upvotes: 1

Related Questions