max(date) question 1

[oakpark5]

I'm not sure how to do this exactly but this what i have so far:
Select event,max(date) from tctest where event=@newEvent and userid=@contentUserid

What i am trying to do, is retrieve the event from the most recent date for a certain user....if someone could just steer me int he right direction that would be nice, thanks in advance!

Software Engineer
Team Lead
damn bill gates, he has everything

 

[mrdenny]

You are almost there. You just need to add a group by clause to the end of the query. Like this:

select event, max(date)
from tctest
where event = @NewEvent
   and userid = @contentUserID
group by Event

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[oakpark5]

Ok, now i need to get a bit more advanced, i need to pull an entry from a user. I need the enrty that occured most recently and i need to look it up by date and time. Here is my first attempt, i know what i'm doing wrong but i dont know how to do it right:
select event, max(date)
from tctest
where event = @NewEvent
and userid = @contentUserID
group by Event

I need the most recent event by a certain user by looking up time and date....thanks is advance!

Software Engineer
Team Lead
damn bill gates, he has everything

 

[mrdenny]

I'm not quite clear on what you are asking for. the code you posted is the same code I sent to you.

Are you trying to pull the most recent event for a user, for each user, etc?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[oakpark5]

Sorry, let me see if i can explain better, its a timeclock program, and i need to look at an event, in or out, onh todays date or the now date, and the most recent time. So if there were 3 events on 11/22/04 one at 9am, one at 10am, and one 1 pm, i would need to lookn at the event from 1pm. And i need to pull this data for the user who is logged in which is based on the @contentUserid. Thanks for the help.

Software Engineer
Team Lead
damn bill gates, he has everything

 

[agoeddeke]

How about:

select event, userid, max(date)
from tctest
where event = @NewEvent
   and userid = @contentUserID
group by event, userid

Andy

 

[SQLBill]

If I understand you, I think you need a subselect for this.

First you need to get the most recent date and time:

SELECT MAX([date])
FROM tctest

Then you need to find the Event and UserID for that date/time. So here's the final script....

SELECT Event, UserID
FROM tctest
WHERE [Date] = (SELECT MAX([DATE]) FROM tctest)

BTW-you use the term NOW date? You ARE using SQL Server aren't you? (If not, this is the wrong forum).

-SQLBill

 

[oakpark5]

OK,i get that, i forgot to explain this, i have two columns, one with the date and one with the time. One column has just the date, and another column has just the time. So when a use punches in the date goes into one column and the time into another. So i need the most recent time of the most recent date for a certain user. And yes i am using sql server.

Software Engineer
Team Lead
damn bill gates, he has everything

 

[SQLBill]

That's inefficient. Dates and times should be stored as DATETIME datatype. But oh well, it's your database

They are probably VARCHAR values. VARCHAR has a different sort order than DATETIME, so that's going to cause a problem. Is there a primary key on each row?

Can you give examples of the values? Is the Time value 24hour or AM/PM? What format is the Date value?

-SQLBill

 

[oakpark5]

Yes, i know the database is horrible, but i didnt design it, i've been trying to change it for a while. I have the date column which is stored as a smalldatetime, but no time is stored. The time coleumn is stored as a char(10). There is no primary key. Bad I know! Basically its a timeclock program where a user punches in and out, so there are multiple userids, as well as events(in, out). Basically its just a table to track what the employees punching in and out, and i need to get the event from the most recent time of the most recent date, they should e stored as datetimes, your right, but its a little late to change that. As numerous other programs enter data into this table and use the data out of the table, huge project to change everything.

Software Engineer
Team Lead
damn bill gates, he has everything

 

[oakpark5]

Time is on a 24 hour scale as well. and the date format is

11/23/2004

Software Engineer
Team Lead
damn bill gates, he has everything

 

[SQLBill]

First, SMALLDATETIME does have a time. The difference is it's accuracy (it's only to the minute).

Run this in Query Analyzer:

SELECT [Date} FROM tctest

That should return a date and time. If the user is only entering a date, the time will be 00:00.

So, you could change the method of entering the date and time to put both into the one column.

However, that's not the way it is now. This might work:

SELECT Event, UserID
FROM tctest
WHERE [Time] = (SELECT MAX([Time]) FROM tctest
                WHERE [Date] = (SELECT MAX([Date]) FROM tctest))

I think if you get the MAX Date value first, then get the MAX Time value from that set of values you can then get the values you want.

-SQLBill

 

[ESquared]

Assuming your varchar time field is in proper format for SQL server to convert it to a date, you can do it this simply:

SELECT
      UserID,
      Event,
      Max(Convert(datetime,DateField)
          + Convert(datetime,TimeField))
   FROM
      EventsTable
   GROUP BY
      UserID,
      Event

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[oakpark5]

Worked! Perfect, of course it only seems to work with data entered in from a c#.net website and not an internal foxpro program. But thats a whole other issue, thanks for your help.

Software Engineer
Team Lead
damn bill gates, he has everything

 

[ESquared]

Which answer, exactly, worked?

I think the best method is to add a calculated column in your table, RealDateTime, which has the expression:

Convert(datetime,DateField) + Convert(datetime,TimeField)

Then you can just do queries on it as usual. Converting and adding each time you query or doing nested Max() subqueries can be expensive.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[oakpark5]

SQLBills statement worked, i didnt think about using a conversion but it might be useful as this only seems to work for data entered by a c#,net program and not a foxpro program.

Esquared, I tried your statement and it gave me this error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The time field is being stored as char(10) in sql server.

Software Engineer
Team Lead
damn bill gates, he has everything

 

[ESquared]

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

This is one very compelling reason to not use varchar to store times or dates... you can end up with bad data. Some of your times are not proper syntax for SQL server to interpret as a time. Run this query to see them:

SELECT TimeColumn
   FROM tctest
   WHERE IsDate(TimeColumn) = 0

You may want to add a constraint to that column:

IsDate(TimeColumn) = 1

to prevent further entry of incorrect times.

On the other hand, if your time column is intentionally not in proper SQL date format, you're out of luck. But in that case, I wonder if a straight sort of that column is actually going to result in proper ordering...

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[oakpark5]

Our sql server databse is terrible (built before i arrived) they built it based on free tables from a foxpro db we use to use. No relationships, no keys. We are now starting to add these (needed) things. So I've really had to dance around this to make reports, and other features work. time is stored as a character because the foxpro program that does the timecards reads that. I'm just making a web-based way to clock in and clock out, and i need to make sure they dont try and clock in or out twice in a row. Thanks for all the help, it seems to be workable. Thanks!!!

Software Engineer
Team Lead
damn bill gates, he has everything

 

[SQLSister]

ou might consider if you can use a proper datetime column and then on data entry sned the dat to the existing columns as well. Then the old program can read it, but you have the proper data available to fix the new program correctly. Sure you'll denormalize and have time data inthere twice, but it will allow you to keep things running until you can convert over totally to a redesigned system.

Questions about posting. See faq183-874

 

[ESquared]

What SQLSister said!

I'm curious to know what the bad time entries look like...

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)