Summing quantity based on date range

[BIGGY]

Not sure how easy it is to do it in VB, but before I try it in the SQL I thought I'd see if there's an easy way to sum a quantity for a date range. The data is layed out like this:

Date Quantity
4/1/2006 1
4/1/2006 5
4/2/2006 3
4/4/2006 8
4/4/2006 5
4/10/2006 15

So for example, on the above data I would want to make sure that from 4/1/2006 through 4/4/2006 are summed up, but 4/10 is excluded. I wrote what I think should keep the 4/10 date out as follows:
******
numbervar DaysToEnd;
datevar FirstDay; //this is a date which is already set manually
datevar ToDate; //Used to sum from 'FirstDay' through 'ToDate'

DaysToEnd := 7 - dayofweek(firstday);
ToDate := firstday + DaysToEnd;
******

Now I don't know if I do a 'whileprintingrecords' or what. I need it to go through every record and sum up 'Quantity' starting with FirstDay, then check all of the FirstDay + 1, etc...until it reaches ToDate. Using the above sample data the sum should come out to 22.

Hope that's somewhat clear for anyone that can help?

[BIGGY]

I've gotten a bit further...here's a snippet more that I wrote which works correctly, but the number of records seems way off. Did I miss anything here:

While (FromDate < ToDate)
Do(
if FromDate = {INTran.TranDate} then
TempDaySum := sum ({INTran.Qty});
week1 := Week1 + TempDaySum;
FromDate := FromDate + 1;
)
**************************

I get the feeling that the quantity is being summed without the date range limit that I'm trying to impose.

[brucevde]

First, when you say data from 04/10 is excluded, do mean it is excluded from the entire report or just from "Week 1" and it will be included in Week 2.

Are you just trying to create a 7 day report starting from a user specified date. Or are you trying to create a Weekly report(which is very easy in Crystal)?

[BIGGY]

The report runs as follows...I pull all of the data for 2006. If it's January, Feb, or any other month that we're not in, then it shows the summed quantities sold. However once we hit a month that we're actually in, then I need to break it down by week. But not just the first week, but each week ends on saturday no matter what.

Let's say we're in March...March 1st started on a Wednesday, so that week ends on Saturday and I need the quantity from Wed through Sat. Then week 2 will be that first Sunday through Sat, and so on.

Ends up being that
Week1: 3/1/2006 - 3/4/2006
Week2: 3/5/3006 - 3/11/2006
Week3: 3/12/2006 - 3/18/2006
Week4: 3/19/2006 - 3/25/2006
Week5: 3/26/2006 - 3/31/2006

[brucevde]

This might work for you.

1)Insert a Group Section based on your date field. On the Group Options screen, set "The Section Will Be Printed" dropdown box to "For each Month".

2)Insert another Group Section based on your date field. On the Group Options screen, set "The Section Will Be Printed" dropdown box to "For each Week".

3)On the Format Section screen, Click on the Group 2 Header in the list. Click the Formula button beside the Suppress Option (don't check the option). Enter in this formula using crystal syntax. This will suppress Group 2 for every month but the current month.

Month ({YourTableName.YourFieldName}) <> Month(CurrentDate)

4)Add the Quantity field to the Details section. Right Click on this field and select the Insert -> Summary menu item. On the summary options screen, keep the defaults and check the box "Insert Summary for all Groups".