Date Grouping

[Frank Rahman]

Hello,

I have a request to create a report that group date ranges based on weekend
and weekdays.

e.g. There is table called WorkOrders with a field WO_DATE. I need to create
a report that groups the data as follows:

WorkWeek of 3/6~3/10

Record A WO_DATE of 3/7/2006
Record B WO_DATE of 3/8/2006

Weekend of 3/11&3/12

Record C WO_DATE of 3/11/2006
Record D WO_DATE of 3/12/2006

Work Week of 3/13~3/17

Record E WO_DATE of 3/13/2006
Record F WO_DATE of 3/15/2006

Etc.....

Any ideas to get me started would be appreciated, closest is the dateDiff
function but it can only compare date ranges statically, these groups will
continue to change as the year progresses.

Thanks.
Frank

 

[Anne]

I would resolve the workweek issue in your query. Create a field for the
week. Then use that for your grouping in the report.

 

[Marshall Barton]

I have a request to create a report that group date ranges based on weekend
and weekdays.

e.g. There is table called WorkOrders with a field WO_DATE. I need to create
a report that groups the data as follows:

WorkWeek of 3/6~3/10

Record A WO_DATE of 3/7/2006
Record B WO_DATE of 3/8/2006

Weekend of 3/11&3/12

Record C WO_DATE of 3/11/2006
Record D WO_DATE of 3/12/2006

Work Week of 3/13~3/17

Record E WO_DATE of 3/13/2006
Record F WO_DATE of 3/15/2006

Etc.....

First create a group (View menu - Sorting and Grouping) on
the expression:
=Year(WO_DATE)
You will probably want a group header section with a text
box using the same expression.

Next, create another group on the expression:
=Format(DatePart("ww",[WO_DATE],2),"00") &
((DatePart("w",[WO_DATE],2)<6)+1)
For this group header, use a text box with this expression:
=IIf(DatePart("w",[WO_DATE],2)<6,"WorkWeek of " &
Format([WO_DATE]-DatePart("w",[WO_DATE],2)+1,"m/d") & "-" &
Format([WO_DATE]-DatePart("w",[WO_DATE],2)+5,"m/d")
,"Weekend of " &
Format([WO_DATE]-DatePart("w",[WO_DATE],2)+6,"m/d") & "&" &
Format([WO_DATE]-DatePart("w",[WO_DATE],2)+7,"m/d"))

Finally, add a third sorting entry to sort on the [WO_DATE]
field.