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.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
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.
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) --
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.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
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.
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!
