using a converted date on an inner join

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

SQL Server Development (2000)

using a converted date on an inner join

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-21 : 02:14:21

i am making a select in which i want to bring all the rows for the last date that a certain user types in mesage - can be 0,1, or 10 even
the thing is that ii make a select with an inner join of a table on itself to get the max(date) ===lastdate but withoguth dependency f the hours only the date!
i use this code to ocnvert to a date structure of type dd/mm/yyyy


CAST(FLOOR(CAST(@pInputDate AS DECIMAL(12, 5))) AS DATETIME)

and this is the quesy itself


SELECT     COUNT(tblUsersMesag.client_id) AS countStatus, tblUsersMesag.client_id, tblUsersMesag.CompanyID, 
                      tblUsersMesag.CreateDate
FROM         tblUsersMesag INNER JOIN
                          (SELECT     client_id, CompanyID, MAX(CAST(FLOOR(CAST(tblUsersMesag.CreateDate AS DECIMAL(12, 5))) AS DATETIME)) 
                                                   AS maxcreatedate
                            FROM          tblUsersMesag
                            GROUP BY client_id, CompanyID) lwcs1 ON tblUsersMesag.CompanyID = lwcs1.CompanyID AND 
                      tblUsersMesag.client_id = lwcs1.client_id AND 
                      tblUsersMesag.MAX(CAST(FLOOR(CAST(tblUsersMesag.CreateDate AS DECIMAL(12, 5))) AS DATETIME)) 
                      = lwcs1.maxcreatedate
WHERE     (tblUsersMesag.CompanyID = '511192601') AND (tblUsersMesag.client_id = '19559')
GROUP BY tblUsersMesag.client_id, tblUsersMesag.CompanyID, tblUsersMesag.CreateDate

the problem is in the inner join in the "ON" part where id o


tblUsersMesag.MAX(CAST(FLOOR(CAST(tblUsersMesag.CreateDate AS DECIMAL(12, 5))) AS DATETIME))

that is not acceptable!
how can i sove this
thnaks inadvamnce
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-02-21 : 02:21:05

I'm having a tough time understanding what you are asking. Do you want to join on the date portion only of the datetime columns?

If so, see this thread for how to eliminate the time component of your datetime column for use w/joins and other comparisons.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62097

-ec

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-21 : 02:48:54

execlly i need only the date part withought the time
but i need also to get the max date (bacuse i need the last date that a user leftaa message)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-21 : 02:57:52

"execlly i need only the date part withought the time"
See the sticky on date time http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

----------------------------------
'KH'
It is inevitable

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-21 : 03:20:40

What is the data type for tblUsersMesag.CreateDate ?
This looks a bit weird

     MAX(CAST(FLOOR(CAST(tblUsersMesag.CreateDate AS DECIMAL(12, 5))) AS DATETIME))

Maybe you can post your table's DDL some sample data and the expected result. We should be able to help you to rewrite this query.

----------------------------------
'KH'
It is inevitable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources