No, you haven't understood what I said. You also haven't understood the T-SQL you found somewhere, obviously. Because the "script" computes new dates, it doesn't group data. A grouping would be done by either defining report groups with these new fields or by GROUP BY, if you're about to computes sums (SUM) or average values (AVG) or minimum/maximum (MIN/MAX) of the values of a group. What was most probably in the original T-SQL script and query was a GROUP BY EveryMonday. The mere computation of EveryMonday does nothing for grouping, it just computes a further column with a date, that's equal to the monday of the same week as mydatefield. To be able to group by the same week of the same year, the expression DATEDIFF(WEEK, '19000101', mydatefield) As WeeksSince19000101 would be sufficient.
What you would do in VFP is what I already said, and it looks much more natural than that DateDiff "orgy", not even that shorter expression. If you have a field dDate in your DBF you can do this SQL:
Code:
Select Count(*),Week(dDate) From Table Group By 2
Caution: That will group together data of the same week number but of other years into one group. But it demonstrates, how easy the computation of a week is. It's sufficient, if you filter the data to report to one year, eg by REPORT FORM ... FOR Year(dDate)=2012
There is another thing you could do, and that even needs no computed additional field in your DBF, nor in a query, you simply define a group band in your report and set it up to group by WEEK(dDate), you cannot only group by a field of the DBF, you can group by any expression.
Then there is another thing you might mean with a weekly report, which shouldn't group by weeks or aggregate data of a week, but simply output a list of data for a certain, this is simply done by filtering dDate for dates between yeasterday and last thursday, in general between DATE()-1 and DATE()-7, so eg REPORT FORM ... FOR dDate<=DATE()-1 and dDate>=DATE()-7 or you do a query with WHERE dDate Between DATE()-7 AND DATE()-1.
That depends on what you want to report weekly, monthly, annually. In an annual report it's more likely you want a aggregated sum, in a weekly report, you might simply want to list data of the last week for overview, without aggregating it, without summing it, for example. But you don't need to change your DBF, in no case, the source data is the source data, don't add columns you only need temporarily to a DBF, only in queries and their results.
Bye, Olaf.