| View previous topic :: View next topic |
| Author |
Message |
vlady
Beginner
Joined: 17 Dec 2004
Posts: 8
Topics: 2
|
 Posted: Fri Dec 17, 2004 10:31 am Post subject: Summing like records within a date range |
 |
|
I'm stuck and just can't seem to think this one out. My input file consists of different record types (01, 02, 03, etc), they are always in numeric order along with a date. I want to take all of the type "02" records and if their values are within the same month range (11/01/03 - 11/30/04, 12/1/04 - 12/31/04, etc.), then I want to add all of the values together and write out just one record.
I've got all of the type "02" records loaded in a table but I can't figure out how to add all the values together for a month range and write it out.
Any help of overcoming my brain lock is greatly appreciated. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose
|
| Posted: Fri Dec 17, 2004 10:49 am Post subject: |
|
|
Vlady,
Please follow the board rules. Use meaningful topics (don't use the Forum/subject name for the topic).
A better title would be "Summing like records within a date range" . I am editing the title to reflect the requirement.
A couple of clarifications.
1. Do you have a beginning and end range for the dates?
2. can you show a sample input and output data.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
vlady
Beginner
Joined: 17 Dec 2004
Posts: 8
Topics: 2
|
| Posted: Fri Dec 17, 2004 11:04 am Post subject: |
|
|
Sorry first time posting here.
Yes, I know my beginning and ending dates - they are part of the record and will be for one year (11/1/03 - 11/1/04)
Sample input data will look like
| Code: |
begin date end date rec # Rec date value1 value2 value3
110103 110104 001 110104 5.00 10.00 15.00
110103 110104 002 110204 .30 1.00 .00
110103 110104 003 111504 1.00 .00 .00
|
Output should contain the totals of the values of the 3 records and contain the 1st occurrance of the rec# and Rec Date and everything else on the record.
| Code: |
110103 110104 001 110104 6.30 11.00 15.00
|
I need to combine all of the records that have an 11/04 date may be a better way to explain it.
Thanks for your help. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose
|
| Posted: Fri Dec 17, 2004 11:20 am Post subject: |
|
|
Vlady,
Your first post contradicts with your last post. In your first post you said
| Quote: |
I want to take all of the type "02" records and if their values are within the same month range (11/01/03 - 11/30/04, 12/1/04 - 12/31/04, etc.), then I want to add all of the values together and write out just one record.
|
In your last post you said
| Quote: |
Output should contain the totals of the values of the 3 records and contain the 1st occurrance of the rec# and Rec Date and everything else on the record.
|
But the sample data you have shown has just one "02" type record ! Why would you sum the first and third record when there record type is "01" and "03" respectively ?
| Quote: |
I need to combine all of the records that have an 11/04 date may be a better way to explain it.
|
There are 3 date fields in your sample input .
| Code: |
begin date end date Rec date
|
Now which date is to be considered having 11/04 ? Is it the end date or rec date?
What is the cobol declaration of value1 , value2 , value3?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
vlady
Beginner
Joined: 17 Dec 2004
Posts: 8
Topics: 2
|
| Posted: Fri Dec 17, 2004 12:43 pm Post subject: |
|
|
I'm obviously not doing very well at explaining this. Here are examples of the real input and output. I need to group all of the "Rec Type 02" records using the range of the "Activity Date" (month/year only - day needs to be output as day found in "End Date") if there are multiples and show the sum of each value field. There should be only one "Record Type 02" for each "Activity Date" (month/year - day is from end day) being reported.
| Code: |
INPUT:
Rec Start End Activity
Type Seq# Date Date Date Value1 Value2 Value3 Value4
01 000 11282003 11282004 ABC
02 000 11282003 11282004 11282003 0000000000{0000000000{0000000509B0000000000{
02 001 11282003 11282004 12282003 0000000000{0000000000{0000000509B0000000000{
02 002 11282003 11282004 01282004 0000000000{0000000000{0000000509A0000000000{
02 003 11282003 11282004 02252004 0000001665{0000000124I0000000000{0000000000{
02 004 11282003 11282004 02252004 0000001665{0000000124I0000000000{0000000000{
02 005 11282003 11282004 02282004 0000000000{0000000000{0000000507C0000000000{
02 006 11282003 11282004 03282004 0000000000{0000000000{0000000507C0000000000{
02 007 11282003 11282004 04282004 0000000000{0000000000{0000000507C0000000000{
02 008 11282003 11282004 05242004 0000001665{0000000124I0000000000{0000000000{
02 009 11282003 11282004 05282004 0000000000{0000000000{0000000506D0000000000{
02 010 11282003 11282004 06282004 0000000000{0000000000{0000000506D0000000000{
02 011 11282003 11282004 07282004 0000000000{0000000000{0000000506C0000000000{
02 012 11282003 11282004 08262004 0000001665{0000000124I0000000000{0000000000{
02 013 11282003 11282004 08282004 0000000000{0000000000{0000000505D0000000000{
02 014 11282003 11282004 09282004 0000000000{0000000000{0000000505D0000000000{
02 015 11282003 11282004 10282004 0000000000{0000000000{0000000505D0000000000{
02 016 11282003 11282004 11282004 0000000000{0000000000{0000000000{0000000000{
04 000 11282003 11282004 11282003 0000000000{0000000000{0000000000{0000000000{
CURRENT OUTPUT:
01 000 11282003 11282004 ABC
02 000 11282003 11282004 11282003 50.92
02 001 11282003 11282004 12282003 50.92
02 002 11282003 11282004 01282004 50.91
02 003 11282003 11282004 02252004 166.50 12.49 0.00
02 004 11282003 11282004 02252004 166.50 12.49 0.00
02 005 11282003 11282004 02282004 50.73
02 006 11282003 11282004 03282004 50.73
02 007 11282003 11282004 04282004 50.73
02 008 11282003 11282004 05242004 166.50 12.49 0.00
02 009 11282003 11282004 05282004 50.64
02 010 11282003 11282004 06282004 50.64
02 011 11282003 11282004 07282004 50.63
02 012 11282003 11282004 08262004 166.50 12.49 0.00
02 013 11282003 11282004 08282004 50.54
02 014 11282003 11282004 09282004 50.54
02 015 11282003 11282004 10282004 50.54
04 001 11282003 11282004 11282004
EXAMPLE OF DESIRED RESULT SHOWING MONTH OF FEBRUARY ONLY:
02 005 11282003 11282004 02282004 333.00 24.98 50.73
|
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose
|
| Posted: Fri Dec 17, 2004 12:54 pm Post subject: |
|
|
vlady,
One last question. What is the cobol declaration of value1 , value2 , value3?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
vlady
Beginner
Joined: 17 Dec 2004
Posts: 8
Topics: 2
|
| Posted: Fri Dec 17, 2004 1:03 pm Post subject: |
|
|
| Code: |
15 INPUT-VALUE1 PIC S9(09)V99.
15 INPUT-VALUE2 PIC S9(09)V99.
15 INPUT-VALUE3 PIC S9(09)V99.
15 OUTPUT-VALUE1 PIC ZZZ,ZZZ,ZZZ.ZZ-.
15 OUTPUT-VALUE2 PIC ZZZ,ZZZ,ZZZ.ZZ-.
15 OUTPUT-VALUE3 PIC ZZZ,ZZZ,ZZZ.ZZ-.
|
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12377
Topics: 75
Location: San Jose
|
| Posted: Fri Dec 17, 2004 3:54 pm Post subject: |
|
|
vlady,
I wrote this code on the fly with rough idea from your posts. I did not have time to test it.
In simple terms , I build an internal table of 13 occurance starting with the current month to the same month of next year. i.e dec 2003 to dec 2004. The month and year are treated as key.
We read every record and validate it against the internal table, if matched we simply add the quantities.
| Code: |
IDENTIFICATION DIVISION.
PROGRAM-ID. SAMPLE
DATE-COMPILED.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT FILEIN
ASSIGN TO IN1
ORGANIZATION IS SEQUENTIAL.
SELECT SUMFILE
ASSIGN TO OUTSUM
ORGANIZATION IS SEQUENTIAL.
DATA DIVISION.
FILE SECTION.
FD FILEIN
RECORDING MODE IS F
LABEL RECORDS ARE STANDARD
BLOCK CONTAINS 0 RECORDS
DATA RECORD IS IN-RECORD.
01 IN-RECORD.
05 IN-BASE-RECORD.
10 IN-TYPE PIC X(02).
10 FILLER PIC X(04).
10 IN-SEQ PIC 9(03).
10 FILLER PIC X(11).
10 IN-START-DATE PIC 9(08).
10 FILLER PIC X(01).
10 IN-END-DATE PIC 9(08).
10 FILLER PIC X(27).
10 IN-ACTIVE-MONTH PIC 9(02).
10 IN-ACTIVE-DAY PIC 9(02).
10 IN-ACTIVE-YEAR PIC 9(04).
10 FILLER PIC X(01).
05 IN-VALUE-1 PIC S9(09)V99.
05 IN-VALUE-2 PIC S9(09)V99.
05 IN-VALUE-3 PIC S9(09)V99.
FD SUMFILE
RECORDING MODE IS F
LABEL RECORDS ARE STANDARD
BLOCK CONTAINS 0 RECORDS
DATA RECORD IS OUT-RECORD.
01 OUT-RECORD.
05 OUT-BASE-RECORD.
10 OUT-TYPE PIC X(02).
10 FILLER PIC X(04).
10 OUT-SEQ PIC 9(03).
10 FILLER PIC X(11).
10 OUT-START-DATE PIC 9(08).
10 FILLER PIC X(01).
10 OUT-END-DATE PIC 9(08).
10 FILLER PIC X(27).
10 OUT-ACTIVE-MONTH PIC 9(02).
10 OUT-ACTIVE-DAY PIC 9(02).
10 OUT-ACTIVE-YEAR PIC 9(04).
10 FILLER PIC X(01).
05 OUT-VALUE-1 PIC ZZZ,ZZZ,ZZZ.ZZ-.
05 OUT-VALUE-2 PIC ZZZ,ZZZ,ZZZ.ZZ-.
05 OUT-VALUE-3 PIC ZZZ,ZZZ,ZZZ.ZZ-.
WORKING-STORAGE SECTION.
01 WS-CURR-DATE.
05 WS-CURR-YEAR PIC 9(04).
05 WS-CURR-MONTH PIC 9(02).
05 WS-CURR-DAY PIC 9(02).
01 WS-IN-MONYEAR.
05 WS-IN-MONTH PIC 9(02).
05 WS-IN-YEAR PIC 9(04).
01 S-INPUT-EOF PIC X(01) VALUE 'N'.
01 T-MONTH-SUM-TABLE-AREA.
05 SUM-TABLE-REC OCCURS 13 TIMES
ASCENDING KEY IS T-MONYEAR
INDEXED BY C-IDX.
10 T-MONYEAR.
15 T-MONTH PIC 9(02) VALUE ZERO.
15 T-YEAR PIC 9(04) VALUE ZERO.
10 T-INIT-RECORD PIC X(73) VALUE SPACES.
10 T-SUM-VALUE-1 PIC S9(09)V99 VALUE ZERO.
10 T-SUM-VALUE-2 PIC S9(09)V99 VALUE ZERO.
10 T-SUM-VALUE-3 PIC S9(09)V99 VALUE ZERO.
PROCEDURE DIVISION.
PERFORM 1000-INITIALIZATION
PERFORM 2000-MAIN-PROCESS
PERFORM 4000-WRAPUP
GOBACK.
1000-INITIALIZATION.
OPEN INPUT FILEIN
OUTPUT SUMFILE
PERFORM 1500-LOAD-DATE-TABLE
PERFORM 2500-READ-INPUT-FILE
.
1500-LOAD-DATE-TABLE.
MOVE FUNCTION CURRENT-DATE (1:8) TO WS-CURR-DATE
SUBTRACT +1 FROM WS-CURR-YEAR
PERFORM VARYING C-IDX FROM 1 BY 1
UNTIL C-IDX > 13
MOVE WS-CURR-MONTH TO T-MONTH(C-IDX)
MOVE WS-CURR-YEAR TO T-YEAR(C-IDX)
ADD +1 TO WS-CURR-MONTH
IF WS-CURR-MONTH = 13
ADD +1 TO WS-CURR-YEAR
MOVE 01 TO WS-CURR-MONTH
END-IF
END-PERFORM
.
2000-MAIN-PROCESS.
PERFORM UNTIL S-INPUT-EOF = 'Y'
IF IN-TYPE = '02'
PERFORM 3000-SUM-VALUES
END-IF
PERFORM 2500-READ-INPUT-FILE
END-PERFORM
PERFORM VARYING C-IDX FROM 1 BY 1
UNTIL C-IDX > 13
INITIALIZE OUT-RECORD
MOVE T-INIT-RECORD (C-IDX) TO OUT-BASE-RECORD
MOVE T-SUM-VALUE-1 (C-IDX) TO OUT-VALUE-1
MOVE T-SUM-VALUE-2 (C-IDX) TO OUT-VALUE-2
MOVE T-SUM-VALUE-3 (C-IDX) TO OUT-VALUE-3
WRITE OUT-RECORD
END-PERFORM
.
2500-READ-INPUT-FILE.
READ FILEIN
AT END
MOVE 'Y' TO S-INPUT-EOF
NOT AT END
MOVE IN-ACTIVE-MONTH TO WS-IN-MONTH
MOVE IN-ACTIVE-YEAR TO WS-IN-YEAR
END-READ
.
3000-SUM-VALUES.
SET C-IDX TO 1
SEARCH SUM-TABLE-REC
AT END
DISPLAY 'DATE BEYOND THE RANGE: ' WS-IN-MONYEAR
WHEN T-MONYEAR (C-IDX) = WS-IN-MONYEAR
ADD IN-VALUE-1 TO T-SUM-VALUE-1 (C-IDX)
ADD IN-VALUE-2 TO T-SUM-VALUE-2 (C-IDX)
ADD IN-VALUE-3 TO T-SUM-VALUE-3 (C-IDX)
IF T-INIT-RECORD (C-IDX) = SPACES OR LOW-VALUES
MOVE IN-BASE-RECORD TO T-INIT-RECORD (C-IDX)
END-IF
END-SEARCH
.
4000-WRAPUP.
CLOSE FILEIN
SUMFILE
.
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
vlady
Beginner
Joined: 17 Dec 2004
Posts: 8
Topics: 2
|
| Posted: Fri Dec 17, 2004 6:52 pm Post subject: |
|
|
Ah, I get the picture now.
Thanks you! |
|
| Back to top |
|
|
Phantom
Data Mgmt Moderator
Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
|
| Posted: Sat Dec 18, 2004 12:08 am Post subject: |
|
|
vlady,
Are u looking only for a COBOl solution ?
What if you sum the data and get the unique set of records using a SORT and then pass it to next step ?
Thanks,
Phantom |
|
| Back to top |
|
|
vlady
Beginner
Joined: 17 Dec 2004
Posts: 8
Topics: 2
|
| Posted: Sat Dec 18, 2004 6:25 am Post subject: |
|
|
Yes it has to be Cobol. I don't think that would work in this situation. I need those individual records for other parts of the program and only need them grouped together in the output.
Thanks for the suggestion. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
