SQL for Hourly - Daily Sales

Posted by Jerry on June 23, 2009, 12:52 pm

Please log in for more thread options

Hi everyone -

I'm looking for sql to report the average sale per the day of the
week. So the total Monday's average sales per hour, the total
Tuesday's sales per hour for all the days of the week. Average sale
with a rollup on day with sales per hour. At first I'd like to pull
data from 1-1-09 through 6-24-09.

Your help would be appreciated.

Mon Tues Wed Thurs Fri Sat Sun
8am
9am
10am
11am
12am
1pm
2pm
3pm
4pm
5pm
6pm
7pm
8pm
9pm

Posted by Robert on June 24, 2009, 1:02 pm

Please log in for more thread options

Hi Jerry,

You can use this report off of CustomerSource http://tinyurl.com/nqu2e7
, add the code below and an average column for sales and you will have
what you want.

Begin Column
FieldName =3D "DayofWeek"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Day of Week"
VBDataType =3D vbString
Formula =3D "CASE DATEPART(weekday,[Transaction].Time) WHEN 1 THEN '1-
Sunday' WHEN 2 THEN '2-Monday' WHEN 3 THEN '3-Tuesday' WHEN 4 THEN '4-
Wednesday' WHEN 5 THEN '5-Thursday' WHEN 6 THEN '6-Friday' WHEN 7 THEN
'7-Saturday' ELSE 'Unknown' END"
ColHidden =3D False
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 2100
GroupMethod =3D groupmethodNone
ColFormat =3D ""
End Column

Make sure the Formula is all on one line with no carriage return
breaking up the code.

Robert Armstrong
www.retail-pos.com

> Hi everyone -

> I'm looking for sql to report the average sale per the day of the

> week. =A0So the total Monday's average sales per hour, the total

> Tuesday's sales per hour for all the days of the week. =A0Average sale

> with a rollup on day with sales per hour. =A0At first I'd like to pull

> data from 1-1-09 through 6-24-09.

> Your help would be appreciated.

> =A0 =A0 Mon Tues Wed Thurs Fri Sat Sun

> 8am

> 9am

> 10am

> 11am

> 12am

> 1pm

> 2pm

> 3pm

> 4pm

> 5pm

> 6pm

> 7pm

> 8pm

> 9pm

Posted by Jerry on June 25, 2009, 12:58 pm

Please log in for more thread options

> Hi Jerry,

> You can use this report off of CustomerSourcehttp://tinyurl.com/nqu2e7

> , add the code below and an average column for sales and you will have

> what you want.

> Begin Column

> =A0 =A0FieldName =3D "DayofWeek"

> =A0 =A0DrillDownFieldName =3D ""

> =A0 =A0DrillDownReportName =3D ""

> =A0 =A0Title =3D "Day of Week"

> =A0 =A0VBDataType =3D vbString

> =A0 =A0Formula =3D "CASE DATEPART(weekday,[Transaction].Time) WHEN 1 THEN=

'1-

> Sunday' WHEN 2 THEN '2-Monday' WHEN 3 THEN '3-Tuesday' WHEN 4 THEN '4-

> Wednesday' WHEN 5 THEN '5-Thursday' WHEN 6 THEN '6-Friday' WHEN 7 THEN

> '7-Saturday' ELSE 'Unknown' END"

> =A0 =A0ColHidden =3D False

> =A0 =A0ColNotDisplayable =3D False

> =A0 =A0FilterDisabled =3D False

> =A0 =A0ColWidth =3D 2100

> =A0 =A0GroupMethod =3D groupmethodNone

> =A0 =A0ColFormat =3D ""

> End Column

> Make sure the Formula is all on one line with no carriage return

> breaking up the code.

> Robert Armstrongwww.retail-pos.com

> > Hi everyone -

> > I'm looking for sql to report the average sale per the day of the

> > week. =A0So the total Monday's average sales per hour, the total

> > Tuesday's sales per hour for all the days of the week. =A0Average sale

> > with a rollup on day with sales per hour. =A0At first I'd like to pull

> > data from 1-1-09 through 6-24-09.

> > Your help would be appreciated.

> > =A0 =A0 Mon Tues Wed Thurs Fri Sat Sun

> > 8am

> > 9am

> > 10am

> > 11am

> > 12am

> > 1pm

> > 2pm

> > 3pm

> > 4pm

> > 5pm

> > 6pm

> > 7pm

> > 8pm

> > 9pm- Hide quoted text -

> - Show quoted text -

I'm familiar with sql and executing sql statements. This statement
and executing it are unfamiliar to me. If you can explain how to
execute it I'll give it a try.