Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Record Count with most Recent Date 1

Status
Not open for further replies.
monoone

monoone

Programmer
May 24, 2002
219
0
0
US
Hello;

I am trying to do a record count (how many times a user has logged on) and what was the most recent date.

Here is the code that gives me the information but I can't figure out how to do a record count and have the most recent date the user has logged on:

CODE
-----------------------------------------------
<CFQUERY NAME=&quot;GetUsers&quot; DATASOURCE=&quot;officeDB&quot; DBTYPE=&quot;ODBC&quot;>
SELECT *

From tblAdmins, Log

WHERE tblAdmins.User_ID = Log.User_ID

ORDER BY Stamp DESC



</CFQUERY>



<!DOCTYPE HTML PUBLIC &quot;-//W3C//DTD HTML 4.01 Transitional//EN&quot;>
<link href=&quot;forAll.css&quot; rel=&quot;stylesheet&quot; type=&quot;text/css&quot;>

<html>
<head>
<title>USERS REPORT FOR HEINE INTRANET VER. 2.0</title>
</head>

<body>
<TABLE WIDTH=&quot;600&quot; CELLSPACING=&quot;2&quot; CELLPADDING=&quot;2&quot; BORDER=&quot;0&quot;>
<TR>
<TD class=&quot;forTexts&quot;>
<DIV ALIGN=&quot;center&quot;><FONT SIZE=&quot;4&quot;>USERS REPORT FOR HEINE INTRANET VER. 2.0</FONT></DIV>
<BR>
<BR>
<CFOUTPUT QUERY=&quot;GetUsers&quot;>
#User_Name# #Stamp#
<br>
</CFOUTPUT>
</TD>
</TR>
</TABLE>
-------------------------------------------------
OUTPUT

Eric 10/16/03 8:33:41 AM
Eric 10/15/03 4:21:23 PM
Eric 10/15/03 3:40:39 PM
-------------------------------------------------

What I want to see
----------------------------------------------------

Eric 3 - 10/16/03 8:33:41 AM

-----------------------------------------

Any help would be appreciated.

-Eric



 
Sort by date Sort by votes
A easiest but kind of inefficient method its to use 2 queries~

======== Get the number of log in============
SELECT count(tblAdmins.User_ID)
From tblAdmins, Log
WHERE tblAdmins.User_ID = Log.User_ID
=============================================

======== Get the latest date============
SELECT top 1 Stamp
From tblAdmins, Log
WHERE tblAdmins.User_ID = Log.User_ID
order by Stamp desc
=============================================

monoone, think this method its for meeting tight deadlines, else i could reply to you with a neater solution tomorrow morning while in office~ There is a way to to do it in 1 SQL, something to do with &quot;group by&quot;...
Its nite time here in singapore~ <<brain dying>> :p
 
Upvote 0 Downvote
Thank yo, I will try it.

Best regards,
 
Upvote 0 Downvote
This did not work - Anybody have any other ideas?
 
Upvote 0 Downvote
<CFQUERY NAME=&quot;CountUser&quot; DATASOURCE=&quot;officeDB&quot; DBTYPE=&quot;ODBC&quot;>
SELECT count(tblAdmins.User_ID) as NumCount
From tblAdmins, Log
WHERE tblAdmins.User_ID = Log.User_ID
</CFQUERY>

<CFQUERY NAME=&quot;LastDate&quot; DATASOURCE=&quot;officeDB&quot; DBTYPE=&quot;ODBC&quot;>
SELECT top 1 Stamp
From tblAdmins, Log
WHERE tblAdmins.User_ID = Log.User_ID
order by Stamp desc
</CFQUERY>

<html>
<head>
<title>USERS REPORT FOR HEINE INTRANET VER. 2.0</title>
</head>

<body>
<TABLE WIDTH=&quot;600&quot; CELLSPACING=&quot;2&quot; CELLPADDING=&quot;2&quot; BORDER=&quot;0&quot;>
<TR>
<TD class=&quot;forTexts&quot;>
<DIV ALIGN=&quot;center&quot;><FONT SIZE=&quot;4&quot;>USERS REPORT FOR HEINE INTRANET VER. 2.0</FONT></DIV>
<BR>
<BR>
<CFOUTPUT QUERY=&quot;CountUser&quot;>
#NumCount#
</CFOUTPUT>
<CFOUTPUT QUERY=&quot;LastDate&quot;>
#Stamp# <br>
</CFOUTPUT>

</TD>
</TR>
</TABLE>

What errors did it give u monoone~? Syntax errors?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Mark Freeberg
  • Locked
  • Question
Most Recent Version of DW w/ (true) WYSIWYG?
Replies
2
Views
132
Mark Freeberg
Mark Freeberg
FALCONSEYE
  • Locked
  • Question
ColdFusion microservices with REST
Replies
0
Views
110
FALCONSEYE
FALCONSEYE
mdvp
  • Locked
  • Question
Intermittent X-ref problem with figures in FM 12
Replies
0
Views
106
mdvp
mdvp
oshinwa
  • Locked
  • Question
Which Call collector should I use and which one is compatible with my Ericsson BP250?
Replies
0
Views
106
oshinwa
oshinwa
Kjonnnn
  • Locked
  • Question
What is is called when you end a domain with a folder name.
Replies
2
Views
59
Kjonnnn
Kjonnnn

Part and Inventory Search

Sponsor

Back
Top