Latest quarterly default

In Query Analyzer, I would like to design a view that would that would return
data automatically for the most recent completed quarter based on a calendar
year.
For example(from the SOP30200 table in Great Plains Dynamics 7.0):
sopnumbe soptype docdate subtotal
-- -- -- --
I would like all data from July 1 through September 30, 2006 since today is
December 27 and this current calendar quarter is still in progress.
Thank you.
If you are still interested this should work (but it's a bit convoluted).
You can replace the @.Today WITH GETDATE() is you want to use the machine date.
DECLARE @.Today DATETIME
SELECT @.Today = '12/27/2006'
SELECT
@.Today AS TodaysDate,
DATEPART(year, @.Today) - CASE WHEN DATEPART(quarter, @.Today) = 1 THEN 1
ELSE 0 END AS [Year],
CASE WHEN DATEPART(quarter, @.Today) = 1 THEN 4 ELSE DATEPART(quarter,
@.Today) - 1 END AS [Quarter],
CAST(
CASE
WHEN DATEPART(quarter, @.Today) = 1 THEN '12/31/'
WHEN DATEPART(quarter, @.Today) = 2 THEN '03/31/'
WHEN DATEPART(quarter, @.Today) = 3 THEN '06/30/'
ELSE '09/30/'
END +
CAST(DATEPART(year, @.Today) - CASE WHEN DATEPART(quarter, @.Today) = 1 THEN
1 ELSE 0 END AS VARCHAR)
AS DATETIME) AS LastQuarterEnd
Results:
2006-12-27 00:00:00.000200632006-09-30 00:00:00.000
Regards,
JayAchTee
"chas2006" wrote:

> In Query Analyzer, I would like to design a view that would that would return
> data automatically for the most recent completed quarter based on a calendar
> year.
> For example(from the SOP30200 table in Great Plains Dynamics 7.0):
> sopnumbe soptype docdate subtotal
> -- -- -- --
> I would like all data from July 1 through September 30, 2006 since today is
> December 27 and this current calendar quarter is still in progress.
> Thank you.