Good day,
Some users are really imaginative, believe me!
Just had a request to present a report by weekday.
I came up with a report that shows the WeedDays names, the minimum, maximum, average and total hits for all the given weekdays.
And "naturally", the little graph bar indicating the percentage vs the overall hits.
Here's the query I used as my recordsource, just in case you would be interested at implementing something similar.
============================================================
strSql="SELECT WDay, COUNT(CntPerDay), MIN(CntPerDay), MAX(CntPerDay), AVG(CntPerDay), SUM(CntPerDay) FROM ( " &_
"SELECT DATEPART(""w"",pl_datetime) as WDay, COUNT(pl_datetime) AS CntPerDay " & _
"FROM " & strInstance & "PageLog"
strSql=ApplyDateFilter(strSql)
If (userIPAddr <> "") Then strSql=ApplyIpFilter(strSql)
If blnExcludeRobots=True Then strSql=ApplyRobotFilter(strSql)
strSql=strSql & "GROUP BY DATEPART(""w"",pl_datetime), DATEPART(""y"",pl_datetime)) CntVsDays " &_
"GROUP BY WDay " &_
"ORDER BY 6 " & strSortSequence & ", WDay ASC"
============================================================
The "ApplyDateFilter(strSql)" call returns the SQL string with the proper date range specification.
The "ApplyIpFilter(strSql)" applies a filter on the IP "pl_ipaddress" field if the user entered a specific address. The routine returns the properly formatted query. (Specific to my customization)
The "ApplyRobotFilter(strSql)" applies the "Robot" filtering, the "<>'Robot'" thing...
All the above "Apply*" routines are verifying if the keyword " WHERE " is already present in the SQL statement. If not, then " WHERE " will be used with the filtering specification, otherwise " AND " will be used.
The "strSortSequence" is set based on a checkbox I added to the form, allowing the information to be listed either in the ascending or the descending ways. The "intMaxNumber" value is adjusted accordingly if the listing is in ascending sequence, so that the graph would no be impacted by the low values.
Happy coding!
P.S.: Some of the features/options mentioned above are part of the customization I did to the scripts I'm using, therefore you will need to look at your code closely before adapting the above to your needs.
Have fun.
Oooppps.
Before I forgot to mention that the above query was for Access. Some edition will obviously be required to adapt it to MsSQL.