Counting records within a date range

[not2techie]

Hello! I am a fairly new Crystal user.
I am trying to roll up data and see items in progress for a particular date range. Q: How many items are in progress on 1/10, 1/12, 1/14, total for the month etc. If they are opened and not closed, they are in progress.

Data:
Item # Opened Date Closed Date
1234 1/10/06 1/14/06
1278 1/13/06 1/18/06
4567 1/13/06 NULL

Can someone offer assistance on a variable or incrementing date that can be placed at the row level so I compare it to the dates and count the item numbers?

This needs to be dynamic for every month/day and eventually hour.

Thanks so much !!!

 

[nemmeh]

Can you clarify a little further what you're trying to achieve?

1. Do you mean you just want a Count of the number of Item #'s that falls within a specified date range?
2. Are you trying to create a Work In Progress report or something?

If you can give a little more specifics I can probably be of more assistance.

 

[Madawc]

You can create running totals that add, or don't add, depending on a formula. This could be for a date range, maybe a date range based on the current date using DateDiff.

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

 

[not2techie]

Thanks Yes. I am trying to create a work in progress.
I was hoping that I wouldn't have to create 30 counts( one count for every day of the month).

Ex: The output should be
1/10/06 # inprogress = 1
1/11/06 # inprogress = 1
1/12/06 # in progress = 1
1/13/06 # in progress = 1
1/14/06 # in progress = 2
etc
I know in the footer, I can put 30 formulas (one for every day), but wouldn't it be easier to create a variable that increments the current day of a month to count?

Thanks for asking ! I appreciate any advice/suggestions.

 

[Madawc]

You can also group by month. Or do a crosstab that counts by month.

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

 

[not2techie]

That was my first inclination.
However, what would you group by?
If I group by opened day I miss the items that were opened the previous/next day.

Can you group by a variable? How do I set it up?
Can you group by a calendar month?

again. I've never used variables.

thanks much

 

[MrBillSC]

I have just posted a solution that I used for a similar reporting requirement. See my post for today titled "Counting Once for Each Day a Record Spans.

MrBill