client data and get max submitted date

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)

client data and get max submitted date

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2004-06-08 : 08:00:43

hi,

i have to tables clients and fileinfo. client contains clientid and file info contain data uploaded by client with respect to date.
i have to clist clientid and last data uploaded date by client.

result should be like that

client id : last date
11 01-jun-2004
14 01-mar-2004
15 01-dec-2003
16 01-jan-2004

i have tried query.
select caclientsloginid, (select max(creationdate) from fileinfo where caclientsloginid=caclientsloginid) from caclientslogin order by caclientsloginid

but fail to catch clientid whihc is selected first and put it to where clause.

any help will be appreciated.

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2004-06-08 : 08:19:22

Not sure I understand 100% what your question is, but I will give it a shot anyways.

SELECT a.caclientsloginid, max(b.creationdate) lastdate
FROM caclientslogin a
INNER JOIN fileinfo b
ON a.caclientsloginid = b.caclientsloginid
GROUP BY a.caclientsloginid
order by a.caclientsloginid

Let me know if this isn't what you are looking for.

Jeremy W. Oldham

imughal
Posting Yak Master

192 Posts

Posted - 2004-06-09 : 05:40:59

thanks now my requirements has little bit added. now my result should be like that.
Client last month jan feb mar apr ..... Dec
11 01-jun-2004 y
14 01-mar-2004 y
15 01-dec-2003 N
16 01-jan-2004 y

now i have to check on each month if data is available on that month against clientid then i have to mark that month field.

can you pls tell me how to do that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources