Filter for Day of the Week

[Guest]

I'd like to return results that show calculations based on the day of the week
for example

Date Total
7/9/2007 3
7/10/2007 4
7/11/2007 3
7/12/2007 6
7/13/2007 1
7/14/2007 1
7/15/2007 0
7/16/2007 2
7/17/2007 3

Id like to return a result of

Day: Total:
Monday 5
Tuesday 7
Wednesday 3
Thursday 6
Friday 1
Saturday 1
Sunday 0

Thanks for your help.

 

[Allen Browne]

In query design view, enter an expression like this into a fresh column in
the Field row:
DOW: Weekday([MyDate])
Substitute your field name for MyDate.

Depress the Total button on the toolbar.
Group by the DOW field.
Sum (or count or whatever) your other field.

 

[Michael Gramelspacher]

I'd like to return results that show calculations based on the day of the week
for example

Date Total
7/9/2007 3
7/10/2007 4
7/11/2007 3
7/12/2007 6
7/13/2007 1
7/14/2007 1
7/15/2007 0
7/16/2007 2
7/17/2007 3

Id like to return a result of

Day: Total:
Monday 5
Tuesday 7
Wednesday 3
Thursday 6
Friday 1
Saturday 1
Sunday 0

Thanks for your help.

An example for Northwind sample database:

SELECT FORMAT(OrderDate,"ddd") AS [Week Day],
SUM([Daily Sales]) AS [Total Sales]
FROM (SELECT Orders.OrderDate,
SUM([Order Subtotals].Subtotal) AS [Daily Sales]
FROM Orders
INNER JOIN [Order Subtotals]
ON Orders.OrderID = [Order Subtotals].OrderID
WHERE Orders.Orderdate BETWEEN [Enter start date:]
AND [Enter end date:]
GROUP BY Orders.OrderDate) AS a
GROUP BY DATEPART("w",OrderDate),FORMAT(OrderDate,"ddd");

 

[Guest]

Instead fo saying Monday Through Sunday the DOW column states 1-7
Does 1 equal Sunday?

Our reporting week actually runs Monday-Sunday is there any way I can list
out Monday first?

In query design view, enter an expression like this into a fresh column in
the Field row:
DOW: Weekday([MyDate])
Substitute your field name for MyDate.

Depress the Total button on the toolbar.
Group by the DOW field.
Sum (or count or whatever) your other field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'd like to return results that show calculations based on the day of the
week
for example

Date Total
7/9/2007 3
7/10/2007 4
7/11/2007 3
7/12/2007 6
7/13/2007 1
7/14/2007 1
7/15/2007 0
7/16/2007 2
7/17/2007 3

Id like to return a result of

Day: Total:
Monday 5
Tuesday 7
Wednesday 3
Thursday 6
Friday 1
Saturday 1
Sunday 0

Thanks for your help.

 

[Allen Browne]

Yes: 1 = Sunday.

To treat Monday as the first day of the week:
DOW: Weekday([MyDate], 2)

You generally want the 1-7 approach so the days sort correctly. Not much use
having output that sorts as:
Fri, Mon, Sat, Sun, Thu, Tue, Wed

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Instead fo saying Monday Through Sunday the DOW column states 1-7
Does 1 equal Sunday?

Our reporting week actually runs Monday-Sunday is there any way I can list
out Monday first?

In query design view, enter an expression like this into a fresh column
in
the Field row:
DOW: Weekday([MyDate])
Substitute your field name for MyDate.

Depress the Total button on the toolbar.
Group by the DOW field.
Sum (or count or whatever) your other field.

I'd like to return results that show calculations based on the day of
the
week
for example

Date Total
7/9/2007 3
7/10/2007 4
7/11/2007 3
7/12/2007 6
7/13/2007 1
7/14/2007 1
7/15/2007 0
7/16/2007 2
7/17/2007 3

Id like to return a result of

Day: Total:
Monday 5
Tuesday 7
Wednesday 3
Thursday 6
Friday 1
Saturday 1
Sunday 0