Advertisement

05.01.2007 at 10:38AM PDT, ID: 22545556 | Points: 500
[x]
Attachment Details

Query Help

Asked by dinesh_bali in SQL Server 2005

Hi,

I am working on MSSQL 2000

I want query help

Table: Modlog

Columns and values are

userName       dateAction                         action             


david            2007-04-23 17:23:57.280                  5            
david            2007-04-23 17:24:50.873                  3            
david            2007-04-23 17:25:17.980                  2            
david            2007-04-24 15:15:10.230                  2            
lessoneditor1      2007-04-24 16:40:32.027                  1      
admin            2007-04-24 17:19:27.383                  1            
admin            2007-04-24 17:24:10.013                  1            
BetaUser1      2007-04-24 17:24:50.327                  2      
admin            2007-04-24 20:35:04.653                  1            
admin            2007-04-24 20:50:43.510                  1            
tony            2007-04-25 13:04:10.590                  1            
Admin            2007-04-25 14:37:24.053                  1            
admin            2007-04-25 17:20:56.263                  1            
tony            2007-05-01 16:59:54.167                  5            
tony            2007-05-01 17:00:04.777                  5


I want

maximum action for each user with the date range passed by the user

Something like this


DECLARE
@fromDate nvarchar(20),
@toDate nvarchar(20)


SET @fromDate = '23/04/2007'
SET @toDate = '23/04/2007'

Select max(action), userName
 from modlog
where
CONVERT(datetime, dateAction , 103)  between @fromDate and @toDate

group by userName

It gives me error

Arithmetic overflow error converting expression to data type datetime.


Also I want all users with max actions

Many Thanks
Start Free Trial
[+][-]05.01.2007 at 10:41AM PDT, ID: 19010146

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.01.2007 at 10:44AM PDT, ID: 19010169

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.01.2007 at 10:48AM PDT, ID: 19010196

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.01.2007 at 11:15AM PDT, ID: 19010381

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.02.2007 at 02:13AM PDT, ID: 19014341

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.05.2007 at 05:03AM PDT, ID: 19036067

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32