At BSO (Bata), data is usually grouped by we usually group by weeks, too. To do this:

If any week data is saved, it is saved as a date - the date of the Monday the week starts on.

I have a UDF that takes a date and returns the previous monday (unless it is already a Monday) - we start on Mondays too, so that Sunday is actually the seventh day - which is not exactly the standard definition. A simple loop, resting one day at a time, works for this.

For thet case that data is stored for individual dates (i.e., not only Monday), this function can then be used in a SELECT statement, e.g.:

select Field1, Field2, AdjustToMonday(MyDate) as MyWeek ...

Of course, once you do this, you can easily GROUP BY MyWeek, to get totals by week, etc.

HTH,

Hilmar.

>hi all,
>I have a problem which i am finding hard to explain but here goes. a customer wants to get a report automatically generated from a web page and emailed to them, this report has to be an excel sheet. so far so good, i use a simple select statement and copy to type excel, pass in the name of the file to an email dll that i have and everyone is happy. BUT this report now has to be expanded. what they need is for the report to take all transactions with a required by date either today or in the future and then group them by week, which starts on a monday. so if the report is run on a wednesday i have to group the next few days into one week, total that, then get the next 7 day starting from the following monday and total that and so on. this can go as far forward as a year but many weeks would be empty. is there a neat way to do this?
>Slán
>~M