Possible more elegant way of doing this? (1 Viewer)

[BlueIshDan]

So i have developed an application that schedules lockout / kick times for maintenance / updates for our Access databases.

I have a query that collects the general duration each user is spending on each application, on each day. In my efforts to gather the information, I believe I may have went a bit overboard with the use of functions and what not.

I'm simply requesting for help in making this query work in a more elegant and maybe even more effective way.

qryActivity:

SELECT 
	Sum(IIf(IsNull([end]),Now(),[end])-[start]) AS general_duration, 
	DatePart("m",[activity].[start]) & "/" & DatePart("d",[activity].[start]) & "/" & DatePart("yyyy",[activity].[start]) AS [Day], 
	users_list.username & "_" & GetAppNameByID(activity.app_id) AS username

FROM 
	activity 
	INNER JOIN users_list 
		ON activity.user_id=users_list.id

GROUP BY 
	DatePart("m",[activity].[start]) & "/" & DatePart("d",[activity].[start]) & "/" & DatePart("yyyy",[activity].[start]), 
	users_list.username, 
	activity.app_id;

Tables:

Thank you kindly!
Dan

 

[BlueIshDan]

Here is an example of the current output.

and its use:

 

[CJ_London]

not sure why you can't use this

format(activity.start,"m/d/yyyy")


rather than
DatePart("m",[activity].[start]) & "/" & DatePart("d",[activity].[start]) & "/" & DatePart("yyyy",[activity].[start]) AS [Day],

 

[BlueIshDan]

Thank you I'll give it a shot!

 

[namliam]

Or use Datevalue function, assuming you simply want to remove the time part.
Will also keep the date functionality of your field in graphs and other stuffs.

 

[BlueIshDan]

Thank you namliam, this more compact suggestion worked great.

CJ, your suggestion also worked! Thank you

 

[BlueIshDan]

Do you see anything that would make it slow to process?
This information will inflate quickly.

 

[CJ_London]

Do you see anything that would make it slow to process?

ensure activity.user_id,users_list.id, users_list.username and activity.app_id are all indexed

 

[vbaInet]

One little one:

Sum(Nz([end], Now())-[start]) AS general_duration

 

[vbaInet]

Also test to see whether performing a UNION on two queries that have criteria set to "[end] Is Null" and "[end] Is Not Null" respectively, would run quicker. The advantage here is that it gets rid of the record-level Nz() or IIF() function. If you'll need to sort the resulting data then it's not worth the effort.

 

[CJ_London]

end is a reserved word - clearly working here but probably better to change the name

and GetAppNameByID is presumably a function you have written - it may be more efficient to incorporate into your query rather than using vba - assuming you can

On reflection, use namliam's datevalue function rather than my format function, it is likely to be more efficient in the use of indexes

 

[BlueIshDan]

ensure activity.user_id,users_list.id, users_list.username and activity.app_id are all indexed

all buy username is indexed, though there is a users.id that is

 

[BlueIshDan]

Thank you all for the suggestions, I'll apply them all and let you know how things work out!

 

[BlueIshDan]

Its been awhile, but I've gone through many iterations of this n figured I'd post the most elegant way of doing this that I have found.

Int(CSng((NZ(sessions.session_end,Now())-sessions.session_start)*24*3600)) AS Duration

This calculates the duration in seconds.

Found in this:

[user_session_durations]

SELECT 
	users.id AS user_id, 
	users.username, 
	sessions.session_start, 
	NZ(sessions.session_end,Now()) AS session_end, 
	Int(CSng((NZ(sessions.session_end,Now())-sessions.session_start)*24*3600)) AS Duration

FROM 
	users 
	INNER JOIN sessions 
	ON users.ID=sessions.user_id

WHERE 
	sessions.force_stopped=False;

[user_daily_activity]

SELECT 
	user_session_durations.user_id, 
	user_session_durations.username AS [User Name], 
	DateValue(session_start) AS [Day of Activity], 
	Sum(user_session_durations.duration) AS [Duration of Day]

FROM user_session_durations

GROUP 
	BY user_session_durations.user_id, 
	user_session_durations.username, 
	DateValue(session_start);

Which comes out to look nice and clean for me... finally lol