Select last dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select last dates

Author

Topic

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-11-11 : 09:38:04

In my query below, I need to filter the results to show only the rows that correspond to the most recent date [Date]. The [Date] column is a varchar datatype.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-11 : 09:47:06

Why did you use varchar datatype to store Dates?

Try this

Select columns from yourTable where cast(DateCol as DateTime)=(Select max(cast(DateCol as DateTime)) from yourTable)

Madhivanan

Failing to plan is Planning to fail

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-11-11 : 10:21:56

I didn't choose the Datatype. It was in place before I started working here. The table gets its data directly from other tables and the person who designed it chose varchar.

Thanks for your response, I will try it when I get back to my desk and let you know the results.

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-11-11 : 11:36:24

I tried this and got the following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Here is my query:

select distinct AIMCall.dbo.AC_CLIENT_PERSONKEYIDS.FullName AS 'Sales Manager Name',
AIMCall.dbo.AC_CLIENT_PERSONKEYIDS.PersonKeyId, LBMM.dbo.Timesheets.SMID, LBMM.dbo.Timesheets.[date]

from AIMCall.dbo.AIM_AGENT_TEAM_FACT left join AIMCall.dbo.AC_CLIENT_PERSONKEYIDS
ON AIMCall.dbo.AIM_AGENT_TEAM_FACT.ParentKeyID = AIMCall.dbo.AC_CLIENT_PERSONKEYIDS.PersonKeyId
INNER JOIN LBMM.dbo.Timesheets
on AIMCall.dbo.AC_CLIENT_PERSONKEYIDS.PersonKeyId = LBMM.dbo.Timesheets.SMID
AND cast([date]as DateTime) = (Select max(cast([Date]as DateTime)) from LBMM.dbo.Timesheets)
Order by 'Sales Manager Name'

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-11 : 11:41:41

quote:
Originally posted by p.shaw3@ukonline.co.uk

I tried this and got the following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Take this error message to whoever designed this table and explain to them "this is why datatypes were invented."

You have invalid dates in your table; or you are not converting from the proper format.

You can use the IsDate() function around a varchar to determine if it holds a valid date in it.

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-11-12 : 08:54:30

OK, I feel I am nearly there but am not quite returning the records I want. Let me give a deeper insight into what is going on:

If I use this query:
select distinct PERSONKEYIDS.FullName AS 'Sales Manager Name',
Timesheets.SMID, Timesheets.[date]
from AGENT_TEAM_FACT left join PERSONKEYIDS
ON AGENT_TEAM_FACT.ParentKeyID = PERSONKEYIDS.PersonKeyId
INNER JOIN Timesheets on PERSONKEYIDS.PersonKeyId = Timesheets.SMID
Order by 'Sales Manager Name'

I get these results

Sales Manager Name SMID Date
david hall 10334 11/11/2005
david hall 10334 08/11/2005
ian lowe 12446 11/11/2005
debbi tomms 12211 10/11/2005
debbi tomms 12211 09/11/2005
debbi tomms 12211 06/11/2005
steve vine 13429 07/11/2005

Whereas if I add the following line to the query:

select distinct PERSONKEYIDS.FullName AS 'Sales Manager Name',
Timesheets.SMID, Timesheets.[date]
from AGENT_TEAM_FACT left join PERSONKEYIDS
ON AGENT_TEAM_FACT.ParentKeyID = PERSONKEYIDS.PersonKeyId
INNER JOIN Timesheets on PERSONKEYIDS.PersonKeyId = Timesheets.SMID
where convert(DateTime,[date],103) = (Select max(convert(DateTime,[Date],103 )) from Timesheets)
Order by 'Sales Manager Name'

I of course get this:

Sales Manager Name SMID Date
david hall 10334 11/11/2005
ian lowe 12446 11/11/2005

Although I can see that both these result sets are correct in relation to the query, These are not what I want. I need a result set that returns the details of the Sales Managers for the most recent date they submitted a Timesheet only. So, for example the result set should look like this:

Sales Manager Name SMID Date
david hall 10334 11/11/2005
ian lowe 12446 11/11/2005
debbi tomms 12211 10/11/2005
steve vine 13429 07/11/2005

returning the details for the Sales Managers for the last date on which they submitted a Timesheet ONLY. I hope this makes sense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources