Day of Week Report with Total and Average

I have a database that collects equipment useage on a daily basis. I need to generate a report that tallys useage for the day of the week rolled up by the week so that the report looks like:

Mon Tue Wed Thu Fri Sat Sun

Week Ending Date: 123 123 123 123 123 123 123 Total Ave

Any suggestions, recommendations or references? Thanks.

If the table you are storing these values into, contains a datetime column. You can use something like this in reporting services:

select DateName(dw, dateColumn) WeekDay, someNumber from Table

Then do a group by the column WeekDay.

|||Thank you. I think I will do a query that groups on weekDay and by LastDayofWeek then use a matrix report. I could identify which day is the last day of the week and add this as a column for all dates based on DayOfWeek - 7. What do you think?

Date_Time Convert(24) to DateTime Format

Please help me modify the sql statement so that I average wly values for
FullScan and CPU.
My Date_Time and samples are written to a table with the
char(24) format.
I need to convert the Date_Time field from char(24) to datetime format.
Please help me with this task.
Thanks,
Date_Time char(24)
2005-01-24 16:06:48.966
2005-01-24 16:07:48.966
2005-01-24 16:08:48.966
select
dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20050202') as HourStart,
avg([Full_Scan_Sec]) as FullScan,
avg([CPU_Processor_Time]) as CPU
from Server_Data
where date_time BETWEEN '20040802' and '20050202'
group by dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20040802')
order by dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20050202')Joe
Have you tried CONVERT system function?
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:42A097A1-85E1-4252-A39E-5A7320E65268@.microsoft.com...
> Please help me modify the sql statement so that I average wly values
for
> FullScan and CPU.
> My Date_Time and samples are written to a table with the
> char(24) format.
> I need to convert the Date_Time field from char(24) to datetime format.
> Please help me with this task.
> Thanks,
>
> Date_Time char(24)
> 2005-01-24 16:06:48.966
> 2005-01-24 16:07:48.966
> 2005-01-24 16:08:48.966
> select
> dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20050202') as
HourStart,
> avg([Full_Scan_Sec]) as FullScan,
> avg([CPU_Processor_Time]) as CPU
> from Server_Data
> where date_time BETWEEN '20040802' and '20050202'
> group by dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20040802')
> order by dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20050202')
>|||I tried to convert the Date_Time from char(24) to smalldatetime received
syntax error.
select counterDateTime from CounterData (NOLOCK)
where convert(smalldatetime,counterdatetime)
Line 2: Incorrect syntax near ')'.
Please help me resolve this problem.
Thank You,
"Uri Dimant" wrote:

> Joe
> Have you tried CONVERT system function?
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:42A097A1-85E1-4252-A39E-5A7320E65268@.microsoft.com...
> for
> HourStart,
>
>|||> select counterDateTime from CounterData (NOLOCK)
> where convert(smalldatetime,counterdatetime)
Abive is not a valid WHERE clause. It is similar to saying:
WHERE colname
A WHERE clause need some predicate, like:
WHERE colname = 23
What do you want to achieve? Return only the rows in where you have a string
in the column that can
be converted to datetime? If so, try:
WHERE ISDATE(counterdatetime) = 1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:84F6AF1A-E00B-416D-95CB-F5A3C05054BD@.microsoft.com...
> I tried to convert the Date_Time from char(24) to smalldatetime received
> syntax error.
> select counterDateTime from CounterData (NOLOCK)
> where convert(smalldatetime,counterdatetime)
> Line 2: Incorrect syntax near ')'.
> Please help me resolve this problem.
> Thank You,
>
>
> "Uri Dimant" wrote:
>|||I'm trying to convert the DateTime column format from char(24) to
smalldatetime in a sql query. When it's in smalldatetime format then use the
average function to calculate average full_scan and CPU values.
The sql statements listed below.
Thank You,
"Tibor Karaszi" wrote:

> Abive is not a valid WHERE clause. It is similar to saying:
> WHERE colname
> A WHERE clause need some predicate, like:
> WHERE colname = 23
> What do you want to achieve? Return only the rows in where you have a stri
ng in the column that can
> be converted to datetime? If so, try:
> WHERE ISDATE(counterdatetime) = 1
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:84F6AF1A-E00B-416D-95CB-F5A3C05054BD@.microsoft.com...
>
>|||I'm not sure exactly what your problem is. You posted a query in the origina
l post, but you didn't
say what happens when you run the query. Are you saying that below part fail
s? If so, what error
message do you get? Or incorrect results?
dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20050202')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:4D4CBFAA-70AB-498A-BBE4-3F9497793250@.microsoft.com...
> I'm trying to convert the DateTime column format from char(24) to
> smalldatetime in a sql query. When it's in smalldatetime format then use t
he
> average function to calculate average full_scan and CPU values.
> The sql statements listed below.
> Thank You,
>
> "Tibor Karaszi" wrote:
>