Re: Returnung data with a running date timeframe

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:

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

.

Relevant Pages

  • Re: Same problem with DateAdd
    ... Sara, if SaleDate is a Date/Time field in a query, then there has to be ... Are you using criteria under the calculated field? ... Is the query doing any aggegation (e.g. ...
    (microsoft.public.access.queries)
  • Re: One query which generate multi report
    ... >D but I do not wish to create a query for each company. ... totally non-normalized table design. ... SaleDate and CompanyName fields and put the criteria ... on the Criteria line under companyname and saledate respectively. ...
    (microsoft.public.access.queries)
  • Re: Cant update table from Form
    ... I now have the query and resulting form ... >>SaleDate, StoreNumber and Ignore ... >>My form is fine to bring up the OverShort records based ... >>all the data from the first form (Source is Form! ...
    (microsoft.public.access.forms)
  • Re: Same problem with DateAdd
    ... and I still get the Type Mismatch error. ... formatted it in the query as shortdate, ... >What is SaleDate? ... rather than allenbrowne at mvps dot org. ...
    (microsoft.public.access.queries)