>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 ;
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
