Re: Returnung data with a running date timeframe

On Wed, 27 Dec 2006 11:45:02 -0800, chas2006 wrote:

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 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
.