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?

Datediff needs to deliver month AND days

with datediff all I can get it to return is months or a total of the
days...

so if the difference in dates is 12 months 4 days how do I adjust the
SQL to accommodate both?thrilled wrote:
> with datediff all I can get it to return is months or a total of the
> days...
> so if the difference in dates is 12 months 4 days how do I adjust the
> SQL to accommodate both?

So in February/March you want to return 1 month 2 days some years
and 1 month 1 day every fourth?

You'll need to write it yourself.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@.x.washington.edu
(replace x with u to respond)|||you're the best|||thrilled (decramer@.cox.net) writes:
> with datediff all I can get it to return is months or a total of the
> days...
> so if the difference in dates is 12 months 4 days how do I adjust the
> SQL to accommodate both?

As DA Morgan pointed out, the question needs further clarification.
For instance, how many months + days are there from 2005-08-27 to
2005-04-03? From 2004-02-27 to 2005-09-03?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

DateDiff

Hi,
I am using this formula and I want to reset (Zero) the formula on change of group and grand total it. Can't work out how to do it! Probably something really simple.
DateDiff ("s",Previous ({Order.DateReceived}) ,{Order.DateReceived})
Many ThanksYou can have two formulas,

first one will be group wise total

second one will have the entire total (grand total)|||Crystal won't allow me to summarise this field based on the time calcuation! DOH!