Gathering data by quarters

#IF .F.
Here is a sample I did for a customer during a training session. It selects quarterly data based on the year month.

We will build a key that looks like this: YYYY+QTR

Examples
200101
200102
200103
200104
200201

#ENDIF
* Step 1 - Create a Cursor to Select From
CREATE Cursor sales (sales_dt D, salesAmt N(10,2))

* start the current date as current month minus 1
ldDate = GOMONTH(DATE(),-1)
FOR lni = 1 TO 25
ldDate = GOMONTH(ldDate,1)
APPEND BLANK
REPLACE sales.sales_dt WITH ldDate
REPLACE sales.salesAmt WITH RAND() * 1000
ENDFOR

* Step 2A - The Query
SELECT SUM(salesAmt), ;
ALLTRIM(STR(YEAR(sales.sales_dt))), +;
PADL(ALLTRIM(STR(QUARTER(sales.sales_dt))),2,"0") AS qtr ;
FROM sales ORDER BY 2 GROUP BY 2

#IF .F.
Please note: the 2a example uses the QUARTER function, which was not available in VFP until version 7.0.

If you are using a version prior to 7.0, or prefer more control, or want the ability to modify this for any period (every 2 months, semi-annual, etc.) use Step 2b instead of Step 2a.

With slight modifications you can make it work for any period of time.
#ENDIF

* Step 2b - The Query
SELECT SUM(salesAmt), ;
ALLTRIM(STR(YEAR(sales_dt))) +;
IIF(MONTH(sales_dt) < 4,"01", ;
IIF(MONTH(sales_dt) < 7,"02", ;
IIF(MONTH(sales_dt) < 10,"03","04"))) AS qtr ;
FROM sales ORDER BY 2 GROUP BY 2


#IF .F.

Jim Osieczonek
Delta Business Group, LLC
www.deltabg.com

#ENDIF