Show every week number between a start date and end date 3

[kernal]

I'm using version 9 with csv files.

CSV File
Start_Date End_Date Term_Start_Date Term_End_Date
01/08/2008 02/29/2008 01/07/2008 05/24/2008

I don't know if you need this information to help me but I found this formula for the week #. I'm using this formula because I need Start_date 01/08/2008 to be week# 1 because I want the week#s to start counting depending on the date in the Term_Start_Date and not the first day of the calendar year 01/01/2008:

if datepart("ww",{START DATE})-datepart("ww", {TERM START DATE}) < 0 then
datepart("ww",{START DATE})+ 53 - datepart("ww", {TERM START DATE})+1 else
datepart("ww",{START DATE})-datepart("ww", {TERM START DATE})+1

=====================

I need the report to list the week#s that are between the Start_Date and End_Date because I need to do some running totals per week.

Week#
1 (this is the week# for Start_date 01/08/2008)
2
3
4
5
6
7
8 (this is the week# for End_date 02/29/2008)

=====================

Thanks for your help

 

[lbass]

There is a difference between listing dates and using them as separate rows or groups. What kind of running totals do you need to do? You could still do running totals without creating separate week groups.

-LB

 

[kernal]

I hope I can explain what I'm trying to do. I'm going to try to figure out how many IDs are at a specific location per week # per day of the week per hour so eventually I'm going to be showing something like:

Week# Day_of_the_Week 8am 9am 10am etc.
1 Monday 1 5 1
1 Tuesday 0 5 0
1 Wednesday 0 0 0 etc for week#1 then

2 Monday 1 5 1
2 Tuesday 0 5 0
2 Wednesday 0 0 0 etc for week#2

until for the example in my first message, I get to week#8.

========================

Here is all of the fields in the csv file:

Mon Tues Wed Thurs Fri Sat Sun Start_Time End_Time Start Date End Date Term_Start_Date Term_End_Date Location ID
N N Y N N N N 09.00.00 17.00.00 04/02/2008 04/02/2008 01/07/2008 05/24/2008 BT 1
Y N Y N N N N 16.35.00 17.45.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 2
N N Y N N N N 18.00.00 21.00.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 2
N N Y N N N N 18.00.00 21.00.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 3
N Y N N N N N 18.00.00 20.30.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 4
N Y N Y N N N 19.10.00 21.00.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 5
N Y N Y N N N 18.00.00 19.20.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 6
Y N Y N N N N 18.00.00 21.15.00 03/17/2008 04/14/2008 01/07/2008 05/24/2008 BT 7
N Y N N N N N 18.00.00 21.00.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 8
N Y N N N N N 18.30.00 19.30.00 01/29/2008 03/04/2008 01/07/2008 05/24/2008 BT 9
Y N Y N N N N 18.30.00 20.30.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 10
N N N Y N N N 19.00.00 21.00.00 04/24/2008 04/24/2008 01/07/2008 05/24/2008 BT 10
Y N Y N N N N 18.00.00 21.30.00 01/28/2008 02/06/2008 01/07/2008 05/24/2008 BT 11
N Y N Y N N N 14.00.00 15.20.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 12
N N Y N N N N 18.00.00 21.00.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 12
N Y N Y N N N 07.30.00 08.20.00 01/07/2008 02/26/2008 01/07/2008 05/24/2008 BT 13
N N N N N Y N 09.00.00 10.30.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 13
N Y N Y N N N 15.30.00 17.00.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 13
Y N N N N N N 18.00.00 20.00.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 14
Y N Y N N N N 17.45.00 19.15.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 15
N N Y N N N N 18.30.00 20.30.00 01/30/2008 03/05/2008 01/07/2008 05/24/2008 BT 16
N Y N Y N N N 18.00.00 21.15.00 02/05/2008 03/04/2008 01/07/2008 05/24/2008 BT 17
N N Y N N N N 18.30.00 21.00.00 04/02/2008 04/09/2008 01/07/2008 05/24/2008 BT 18
N N N Y N N N 09.00.00 17.00.00 04/17/2008 04/17/2008 01/07/2008 05/24/2008 BT 20
N Y N Y N N N 18.00.00 21.30.00 02/19/2008 02/21/2008 01/07/2008 05/24/2008 BT 21
N N N N N Y N 12.00.00 13.30.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 22
N N N N N Y N 12.30.00 16.30.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 23
N N N N Y N N 08.00.00 16.00.00 02/01/2008 02/01/2008 01/07/2008 05/24/2008 BT 24
N N N N Y N N 09.00.00 17.00.00 02/15/2008 02/15/2008 01/07/2008 05/24/2008 BT 24
N N N Y N N N 19.00.00 21.00.00 02/07/2008 02/07/2008 01/07/2008 05/24/2008 BT 25
N N N N N Y N 09.00.00 12.00.00 02/02/2008 02/09/2008 01/07/2008 05/24/2008 BT 25
N N N N N Y N 10.00.00 12.00.00 02/16/2008 02/16/2008 01/07/2008 05/24/2008 BT 25
N N Y N N N N 19.00.00 21.00.00 01/23/2008 02/20/2008 01/07/2008 05/24/2008 BT 25
N N N N N Y N 09.00.00 12.00.00 02/23/2008 03/01/2008 01/07/2008 05/24/2008 BT 25

Thanks Lbass (also thank you for the formula that I listed in my initial message because you posted it on 08/31 helping someone else).

 

[lbass]

I'm not sure I can help. Anyway, I can't tell how the start time and end time relate to the start date and end date--do the times apply to each day within the period or does the start time indicate the beginning of the period (along with the date) and then the period continues across times until it ends at the ending date and time?

-LB

 

[kernal]

The first record:

Mon Tues Wed Thurs Fri Sat Sun Start_Time End_Time Start Date End Date Term_Start_Date Term_End_Date Location ID
N N Y N N N N 09.00.00 17.00.00 04/02/2008 04/02/2008 01/07/2008 05/24/2008 BT 1

This class being held Wednesday (Y in Wed field) at 9:00 to 5:00 pm on 04/02 (only 1 day).

The next record:

Y N Y N N N N 16.35.00 17.45.00 01/07/2008 04/23/2008 01/07/2008 05/24/2008 BT 2

This class is being held every Monday and Wednesday at 9:00 to 5:00 pm beginning on 01/07/2008 and ending on 04/23/2008.

Thanks for helping me.

 

[Madawc]

Running totals should do the trick. What problem are you having?

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[lbass]

Do you have a table containing all possible dates? You really need that for the week group. You could create one in Excel or Access and then link it to term start and end date fields, using a >= and <= join. Then after grouping on week number, you could use running totals per dayofweek per hour as Madawc suggested.

-LB

 

[kernal]

Sorry it's taking me sometime to reply. As soon as I can (crazy busy), I'll try your suggestions and post a message. Thanks again.

 

[DjangMan]

Having a table with dates can help drive these types of reports:

http://www.builderau.com.au/program...th-a-Dates-table/0,339028455,339288411,00.htm

 

[kernal]

Thanks everyone. I finally had time to work on this report again. Creating a calendar table and running totals worked great.