INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Syntax

Gathering data by quarters by jimoo
Posted: 1 Sep 03 (Edited 2 Jan 04)

#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


Back to Microsoft: Visual FoxPro FAQ Index
Back to Microsoft: Visual FoxPro Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close