>>Hi Nancy,
>>
>>I need to print a monthly purchase report distributed among the weeks(4/5/6 weeks for that month) Format is like
>>
>>Item 1Wk 2Wk 3Wk 4Wk 5Wk& 6Wk (if exists)
>>Code (1-2) (3-10) (11-17) (18-24) (25-31)
>>
>>I001 10 02 03 04 05
>>I002 01 03 06 07 04
>>
>>so I am calculating sum based on week and print if it is the same week.
>>I hope it is clear( I tried my best to give the scenario)
>>
>
>It's quite clear, and something I have to do for sales analysis routinely in our in-house systems. I've found that the easiest thing to do is to create a cursor in the denormalized form of the report, extract the data into the cursor (in some cases, I've found that a small block of procedural code is easier than trying to construct a single SQL Select statement to do the job) and then run my report against the cursor I've created. A simple example, using the report you show above:
>
>I have a detail line invoice table INVDETL with the following structure. I've omitted lots of fields, but none that are neded for this report:
>
>(DL_PKEY, INHD_FKEY, INVLINENO, ITEM_NO, QTY, LIST, DISC)
>
>And an Invoice table INVHEAD:
>
>(IN_PKEY,_CUST_FKEY, SLSMN_FKEY, INVDATE)
>
>Before my report, I might grab the first and last target dates into date variables and do the following:
>
>
SELECT ITEM_NO, INT((INVDATE - dtFirst)/7) AS WEEKNO , QTY ;
> FROM INVDETL, INVHEAD ;
> WHERE IN_PKEY = INHD_FKEY ;
> AND INVDATE BETWEEN dtFirst AND dtLast ;
> ORDER BY ITEM_NO ;
> INTO CURSOR Temp1 NOFILTER
>* At this point, we have all the applicable items regardless of customer
>* with the item#, qty, and the week (the week estimate isn't what I'd do,
>* but it matches what you laid out
>SELECT ITEM_NO, SUM(QTY), WEEKNO ;
A correction to the line above; you need an AS clause for the SUM, since otherwise, VFP will assign a name to the column on its own, and I can never remember what the new name is going to be; and since I reference the field later, i'll try to make the name consistent:
SELECT ITEM_NO, SUM(QTY) AS QTY, WEEKNO ;
> FROM Temp1 ;
> INTO CURSOR Temp2 ;
> GROUP BY ITEM_NO, WEEKNO NOFILTER
>USE IN Temp1
>* Now we have at most a record per item per week containing the number sold
>CREATE CURSOR Temp3 (ITEM_NO C(4), WK1 I, WK2 I, WK3 I, WK43 I, WK5 I, WK6 I)
>* make sure that the definition of ITEM_NO matches the field in your detail
>* table
>SELECT Temp2
>GO TOP
>INSERT INTO Temp3 (ITEM_NO) VALUES (Temp2.ITEM_NO)
>LOCAL lcFldName && build the target field name using this variable
>SCAN
> IF Temp3.ITEM_NO # Temp2.ITEM_NO
> INSERT INTO Temp3 (ITEM_NO) VALUES (Temp2.ITEM_NO)
> ENDIF
> lcFldName = 'Temp3.WK' + STR(Temp2.WEEKNO + 1,1)
> REPLACE (lcFldName) WITH EVAL(lcFldName) + Temp2.QTY
>ENDSCAN
>USE IN Temp2
>* Temp3 now contains the data that you want to report, sorted by ITEM_NO
