Aleniko29139


Hi;

I need to create a cursor with multiple columns listing sums of sales. For example:

Product# 2006 Sales 2005 Sales 2004 sales 2003 sales 2002 sales


Lets assume the table structure is simply ProductId, SoldDate and SoldAmount. This table obviously lists individual sales.

Before I start xbasing my way into this, can I do this using a SQL select command

Thanks.



Re: Using SQL to create a multi-periods sum result.

Lakshmi N


  1. product id    sales   sales_date
  2. 0001      1000   01/01/2006
  3. 0002        500   12/01/2005
  4. 0003      2300   12/01/2004

 introduce one field cyear n 4

  • set date brit
  • set cent on
  • repl all cyear with year(sales_date)
  • sele distinct(cYear) as 'toyear' from product into cursor yrmast
  • select a.productid,sum(a.sales) as 'yrsales',a.cyear,b.toyear from product a,yrmast b where a.cyear = b.toyear group by cyear

 

 







Re: Using SQL to create a multi-periods sum result.

Aleniko

sorry, but this is not what I asked... I need a cursor which each row has multiple years.
Try again :-)





Re: Using SQL to create a multi-periods sum result.

AndyKr

The VFP Cross-Tab wizard will do this for you from a cursor that contains one record per year. Full details are in the help file, so take a look and come back if you need more specific help




Re: Using SQL to create a multi-periods sum result.

Lakshmi N

Sir,

Sir, how to do this multi-periods sum using just foxpro for windows 2.6 or foxpro on dos 2.6.our company is using foxpro 2.6 for windows. i dont have visual foxpro 9.0. i need to frequently give this kind of output. bcz we have branches all across india and many products. i need to give monthly sales from april to march.






Re: Using SQL to create a multi-periods sum result.

AndyKr

Use GENXTAB.PRG that ships with ForxPro 2.6.

Create a cursor that contains your data in three columns like this:

ITEM MONTH SALE

And then run the program to generate the cross-tab result set.