Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Generating quarterly reports by a weekly breakup

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
prathap.murthy

New User


Joined: 10 Jan 2007
Posts: 5
Location: India

PostPosted: Thu Jan 11, 2007 1:37 pm    Post subject: Generating quarterly reports by a weekly breakup
Reply with quote

Input file: FB with LRECL 20

The input file looks like --

20060501ABCDEFGHIJKL
20060502......................
20060503......................
.
.
.
20070102......................

The first 8 bytes contains the date and the rest 12 bytes contains the data.

Requirement: I need to extract records for the last 3 months including the current month. And then group the extracted records on a weekly basis for counts. By weekly basis, I don't mean the exact calendar weeks... just in groups of 7 days. There might be multiple records for a single date or there might not be no record for a particular date (so the count for each week may be more or less than 7).

If we run the job this month, the job would extract data for Jan 2007, Dec 2006 and Nov 2006 (I've already implemented the extraction).

Now here's where I need help: The fact that 01-Nov-2006 falls on a Wednesday is immaterial to me. I just need to group the first 7 days in Nov 2006 together, count the number of records and tag the count under a label Week1. Then group the next 7 days under Week2 and so on. It's pretty straightforward till we hit the last week of November. 29th Nov 2006 and 30th Nov 2006 now need to be clubbed with 1st, 2nd, 3rd, 4th and 5th of Dec 2006 for Week5! This is where I've hit the roadblock. I need to make the job generic so that it works for any quarter in any year (including leap years).

I just need to know whether I can tailor dates from 3 months and then group them in numbers of 7 using DFSORT/ICETOOL. Given a basic output file with the intended grouping, I can generated a formatted report myself.

As far as I know, I don't think DFSORT can help me to group the dates as mentioned above. Maybe I'll have to resort to COBOL. Let me know if you think otherwise.
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Jan 11, 2007 10:26 pm    Post subject:
Reply with quote

I don't understand what you're trying to do. If you have groups of 7 days, then wouldn't the count for each week be 7 (except maybe for the last week)? So what are you counting exactly?

Please show more input records and exactly what you want the output to look like for those input records.
Back to top
View user's profile Send private message
prathap.murthy

New User


Joined: 10 Jan 2007
Posts: 5
Location: India

PostPosted: Thu Jan 11, 2007 11:55 pm    Post subject: Re: Generating quarterly reports by a weekly breakup
Reply with quote

Sorry if I was ambiguous before.

Okay... let's consider 2 months for the sake of simplicity. Here's a possible input file (I've mentioned only the date part as the data part is irrelevant to our discussion) --

20061001...
20061002...
20061002...
20061003...
20061004...
20061005...
20061006...
20061007...
20061008...
20061101...
20061103...
20061104...
20061105...
20061106...
20061107...
20061108...

There are 17 records in all for 2 months. I need to group these records by week and count them. Here's how the grouping should look like --

Week 1:
20061001...
20061002...
20061002...
20061003...
20061004...
20061005...
20061006...
20061007...

Week 2:
20061008...

Week 3:
<No records>

Week 4:
<No records>

Week 5:
20061101...
20061103...
20061104...

Week 6:
20061105...
20061106...
20061107...
20061108...

Here's how I arrived at what dates should fall under what weeks --

Oct 2006 has 31 days. Nov 2006 has 30 days. So there are 61 POSSIBLE days in all, which is what we shall consider for our grouping.

So Oct 01 is day 01, Oct 02 is day 02... Oct 31 is day 31, Nov 01 is day 32, Nov 02 is day 33... and finally Nov 30 is day 61.

Now we divide the days into groups of 7. So Oct 01 to Oct 07 fall under Week 1, Oct 08 to Oct 14 fall under Week 2... Oct 29 to Nov 04 fall under Week 5... and so on.

Now, there might me more than 1 record with the same date in the input file. Also, there's a possibility that there might be no record for a particular date in the input file.

Under Week 1, there are actually 8 records since there are two records for "20061002". Under Week 2, there's only 1 record as records for 9th, 10th, 11th, 12th, 13th and 14th of Oct don't exist in the input file. Week 3 has no records under it.

So the counts would be 8 for Week 1, 1 for Week 2, 0 for Week 3 and so on.

Please let me know if there's still some ambiguity about the problem statement.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Jan 12, 2007 12:20 am    Post subject:
Reply with quote

Well, it might be possible to do this kind of thing with DFSORT using DATEn-d parameters for the groupings, but having to figure out and use the number of days in each particular month makes it rather tricky. If we could pick a maximum DATE1-d value such as DATE1-98 and go forward from there 7 days at a time (DATE1-98, DATE1-91, ...), it might not be so bad, but you want to start from the first day three months back and then go forward 7 days at a time. So we'd have to figure out what that first day is in terms of DATE1-f and go forward from there computing each DATE1-f+n*7 as DATE1-x. We could then use IFTHENs with each date range in the WHEN condition to get the records for a group. This is quite tricky and I don't have the time to work it out.

Perhaps COBOL is better for this kind of very specific logic.
Back to top
View user's profile Send private message
prathap.murthy

New User


Joined: 10 Jan 2007
Posts: 5
Location: India

PostPosted: Fri Jan 12, 2007 1:34 am    Post subject: Re: Generating quarterly reports by a weekly breakup
Reply with quote

Hey... no problem at all.

Initially, I started playing around with the DATEn-d parameters too. But then the problem of leap year hit me and I realized that it wasn't worth the time. So I came here.

Anyway, the new COBOL program is already in place and the report is almost done! icon_smile.gif

Thanks for your time, Frank.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Produce Mainframe Dash Board Reports ... sdcarlson53 All Other Mainframe Topics 2 Thu Jan 05, 2017 11:01 pm
No new posts Generating an o/p file with dummy i/p... wiprov DFSORT/ICETOOL 2 Wed Sep 10, 2014 6:16 pm
No new posts Validation of reports generated in As... deb_parthas PL/I & Assembler 3 Wed Jul 30, 2014 3:18 pm
No new posts Generating and Viewing JCL vinaysetlur TSO/ISPF 15 Thu Jan 23, 2014 4:45 am
No new posts Generating test Data by Recursive SQL Jimy Carol DB2 2 Thu Dec 26, 2013 3:00 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us