Returnung data with a running date timeframe

In Query Analyzer, I would like to design a view that would that would return
data automatically on a running annual, quarterly, monthly or weekly basis.
For example(from the SOP30200 table in Great Plains Dynamics 7.0):
sopnumbe soptype docdate subtotal
-- -- -- --
I would like all data from today going back for either one of:
one year, quarter, month or week.
Thanks.
Hi Hugo...I am not familiar with the Decalre statement. I work in Query
Analyzer exclusively with Select statements. Will Declare and Set update,
delete or change any live data in the tables.
Thank you.
Charlie
"Hugo Kornelis" wrote:

> On Wed, 27 Dec 2006 11:45:02 -0800, chas2006 wrote:
>
> Hi chas2006,
> I'm not familiar with Great Plains, but here's a generic form of a query
> that calculates subtotals for current month, quarter, and year. You can
> adapt this to your specific needs.
> DECLARE @.BeginMonth datetime,
> @.BeginQuarter datetime,
> @.BeginYear datetime;
> SET @.BeginMonth = DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0);
> SET @.BeginQuarter = DATEADD(qq, DATEDIFF(qq, 0, CURRENT_TIMESTAMP), 0);
> SET @.BeginYear = DATEADD(yy, DATEDIFF(yy, 0, CURRENT_TIMESTAMP), 0);
> SELECT Product,
> SUM(CASE WHEN DATEDIFF(mm, @.BeginMonth, SaleDate) = 0
> THEN Amount ELSE 0 END) AS MonthSales,
> SUM(CASE WHEN DATEDIFF(qq, @.BeginQuarter, SaleDate) = 0
> THEN Amount ELSE 0 END) AS QuarterSales,
> SUM(CASE WHEN DATEDIFF(yy, @.BeginYear, SaleDate) = 0
> THEN Amount ELSE 0 END) AS YearSales
> FROM SalesTable
> WHERE SaleDate >= @.BeginYear
> GROUP BY Product;
> --
> Hugo Kornelis, SQL Server MVP
>