Menu
▾
▴
chiphpug-discuss
|
From: jsWalter <jsW...@to...> - 2007-10-12 05:29:12
|
I have a very unusual case on my hands.
I'm working with a 3rd party AR/Time mgt system from hell!
The time sheets are being particularly difficult. And no, I can't change
the schema, change the system, use linux, or anything else in that vane. I
have to use what I have.
Here is a sample record set...
ID DATE line_num seq_num cell_num time
1 APM 2007-10-01 1 0 0 8.00
2 APM 2007-10-01 1 1 0 8.00
3 APM 2007-10-02 1 0 0 0.50
4 APM 2007-10-02 2 0 0 6.50
5 APM 2007-10-02 2 0 1 6.00
6 APM 2007-10-02 2 0 2 5.50
7 APM 2007-10-02 3 0 0 1.00
8 APM 2007-10-02 3 0 1 0.50
9 APM 2007-10-02 4 0 0 1.00
10 APM 2007-10-02 4 0 1 1.25
11 APM 2007-10-03 1 0 0 1.25
12 APM 2007-10-03 1 0 0 2.25
What I need to do is pull out it the time for each day. This example has 3
days.
If you look at line 1 and 2, I would use line #2 because it has 2 LINE_NUM
that are the same, but the second SEQ_NUM is higher. So I keep it.
Lines 3 through 12 are the same date, with 5 sets of LINE_NUMs.
- #3 we keep, it is unique
- #6 we keep, because it's CEL_NUM is highest, even though it has the
same LINE_NUM and SEQ_NUMs
- #8 & #10 we keep, same reason as #6
- we keep both 12 & 12 because the have the same numbers all across the 3
fields.
Confused!?
Steps, as I see them...
1 - collect in an array date sets
2 - collect into an array of LINE_NUM sub-sets by date
3 - collect into an array of LINE_NUM by LINE_NUM by date
4 - collect into an array of SEQ_NUM by LINE_NUM by LINE_NUM by date
5 - SEQ_NUM value has the time for that record
2007-10-01 -> 1 -> 0 -> 0 = 8.00
-> 1 -> 1 -> 0 = 8.00
2007-10-02 -> 1 -> 0 -> 0 = 0.50
-> 2 -> 0 -> 0 = 6.50
-> 2 -> 0 -> 1 = 5.00
-> 2 -> 0 -> 2 = 5.50
-> 3 -> 0 -> 0 = 1.00
-> 3 -> 0 -> 1 = 0.50
-> 4 -> 0 -> 0 = 1.00
-> 4 -> 0 -> 1 = 1.25
2007-10-03 -> 1 -> 0 -> 0 = 1.25
-> 1 -> 0 -> 0 = 2.25
Using indexed arrays I get...
2007-10-01 -> 1 -> 1 -> 0 = 8.00
2007-10-02 -> 1 -> 0 -> 0 = 0.50
-> 2 -> 0 -> 2 = 5.50
-> 3 -> 0 -> 1 = 0.50
-> 4 -> 0 -> 1 = 1.25
2007-10-03 -> 1 -> 0 -> 0 = 2.25
This is 90% of what I need.
10-01 and 10-02 give me the result I need form the data they have, but
10-03 fails. Because both SEQ_NUM and CEL_NUM are the same, I need to keep
both of them.
So, any minds greater than mine see anyway to do this? Without convoluted
conditionals?
Please?
Thx
Walter
|
|
From: Anacreo <an...@gm...> - 2007-10-12 06:10:00
|
Walter that is actually pretty easy to achieve, but I'm not sure the results
will be desired...
So if you consider your date, line_num and a calculated instance as your
key...
$data[date][line_num][instance] = dataarray
So read in your data to this variable structure:
$data['2007-10-01'][1][$instance] = array(
'row_num' => 1,
'ID' => 'APM',
'seq_num' => 0,
'cell_num' => 0,
'time' => '8.00'
);
// If we already have data for this record we must add carefully...
if ( array_key_exists($date, $data) &&
array_key_exists($line_num, $data[$date]) &&
array_key_exists(0, $data[$date][$line_num]) ) {
foreach ($data[$date][$line_num] as $instance => $instancedata) {
// Date and Line Num are the same, and seq_num is less we replace...
if ($data[$date][$line_num][$instance]['seq_num'] < $seq_num) {
$data[$date][$line_num][$instance] = ...;
}
// Date and Line Num are the same, Seq_num is the same
// but the cell_num is higher, we replace
if ($data[$date][$line_num][$instance]['seq_num'] == $seq_num &&
$data[$date][$line_num][$instance]['cell_num'] < $cel_num) {
$data[$date][$line_num][$instance] = ...;
}
// Date and Line Num are the same, and seq_num are same
// go ahead and make a new instance
if ($data[$date][$line_num][$instance]['seq_num'] == $seq_num &&
$data[$date][$line_num][$instance]['cell_num'] == $cel_num) {
$data[$date][$line_num][$instance++] = data...;
}
} // End foreach $instance
} else {
$data[$date][$line_num][0] = data...;
} // End does $date/$line_num exist
You'll need to figure out when its appropriate to stop checking other
instances and when you should drop out...
If you have records such as
11 APM 2007-10-03 1 0 0 1.25
12 APM 2007-10-03 1 0 0 2.25
13 APM 2007-10-03 1 1 0 1.25
14 APM 2007-10-03 1 1 0 1.25
How do 13 and 14 get handled?
Hope that helps,
Alec
On 10/12/07, jsWalter <jsW...@to...> wrote:
>
> I have a very unusual case on my hands.
>
> I'm working with a 3rd party AR/Time mgt system from hell!
>
> The time sheets are being particularly difficult. And no, I can't change
> the schema, change the system, use linux, or anything else in that vane. I
> have to use what I have.
>
> Here is a sample record set...
>
> ID DATE line_num seq_num cell_num time
> 1 APM 2007-10-01 1 0 0 8.00
> 2 APM 2007-10-01 1 1 0 8.00
> 3 APM 2007-10-02 1 0 0 0.50
> 4 APM 2007-10-02 2 0 0 6.50
> 5 APM 2007-10-02 2 0 1 6.00
> 6 APM 2007-10-02 2 0 2 5.50
> 7 APM 2007-10-02 3 0 0 1.00
> 8 APM 2007-10-02 3 0 1 0.50
> 9 APM 2007-10-02 4 0 0 1.00
> 10 APM 2007-10-02 4 0 1 1.25
> 11 APM 2007-10-03 1 0 0 1.25
> 12 APM 2007-10-03 1 0 0 2.25
>
> What I need to do is pull out it the time for each day. This example has 3
> days.
>
> If you look at line 1 and 2, I would use line #2 because it has 2 LINE_NUM
> that are the same, but the second SEQ_NUM is higher. So I keep it.
>
> Lines 3 through 12 are the same date, with 5 sets of LINE_NUMs.
> - #3 we keep, it is unique
> - #6 we keep, because it's CEL_NUM is highest, even though it has the
> same LINE_NUM and SEQ_NUMs
> - #8 & #10 we keep, same reason as #6
> - we keep both 12 & 12 because the have the same numbers all across the 3
> fields.
>
> Confused!?
>
> Steps, as I see them...
> 1 - collect in an array date sets
> 2 - collect into an array of LINE_NUM sub-sets by date
> 3 - collect into an array of LINE_NUM by LINE_NUM by date
> 4 - collect into an array of SEQ_NUM by LINE_NUM by LINE_NUM by date
> 5 - SEQ_NUM value has the time for that record
>
> 2007-10-01 -> 1 -> 0 -> 0 = 8.00
> -> 1 -> 1 -> 0 = 8.00
>
> 2007-10-02 -> 1 -> 0 -> 0 = 0.50
> -> 2 -> 0 -> 0 = 6.50
> -> 2 -> 0 -> 1 = 5.00
> -> 2 -> 0 -> 2 = 5.50
> -> 3 -> 0 -> 0 = 1.00
> -> 3 -> 0 -> 1 = 0.50
> -> 4 -> 0 -> 0 = 1.00
> -> 4 -> 0 -> 1 = 1.25
>
> 2007-10-03 -> 1 -> 0 -> 0 = 1.25
> -> 1 -> 0 -> 0 = 2.25
>
> Using indexed arrays I get...
>
> 2007-10-01 -> 1 -> 1 -> 0 = 8.00
>
> 2007-10-02 -> 1 -> 0 -> 0 = 0.50
> -> 2 -> 0 -> 2 = 5.50
> -> 3 -> 0 -> 1 = 0.50
> -> 4 -> 0 -> 1 = 1.25
>
> 2007-10-03 -> 1 -> 0 -> 0 = 2.25
>
> This is 90% of what I need.
>
> 10-01 and 10-02 give me the result I need form the data they have, but
> 10-03 fails. Because both SEQ_NUM and CEL_NUM are the same, I need to keep
> both of them.
>
> So, any minds greater than mine see anyway to do this? Without convoluted
> conditionals?
>
> Please?
>
> Thx
>
> Walter
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find problems? Stop.
> Now Search log events and configuration files using AJAX and a browser.
> Download your FREE copy of Splunk now >> http://get.splunk.com/
> _______________________________________________
> chiPHPug-discuss mailing list
> chi...@li...
> https://lists.sourceforge.net/lists/listinfo/chiphpug-discuss
>
|
|
From: Peter H. <ph...@ma...> - 2007-10-12 16:11:13
|
On Fri, Oct 12, 2007 at 12:28:45AM -0500, jsWalter wrote: > Here is a sample record set... Would an SQL query to do this be useful, or do you only have this data in an array? > ID DATE line_num seq_num cell_num time > 1 APM 2007-10-01 1 0 0 8.00 > 2 APM 2007-10-01 1 1 0 8.00 > 3 APM 2007-10-02 1 0 0 0.50 > 4 APM 2007-10-02 2 0 0 6.50 > 5 APM 2007-10-02 2 0 1 6.00 > 6 APM 2007-10-02 2 0 2 5.50 > 7 APM 2007-10-02 3 0 0 1.00 > 8 APM 2007-10-02 3 0 1 0.50 > 9 APM 2007-10-02 4 0 0 1.00 > 10 APM 2007-10-02 4 0 1 1.25 > 11 APM 2007-10-03 1 0 0 1.25 > 12 APM 2007-10-03 1 0 0 2.25 > > What I need to do is pull out it the time for each day. This example has 3 > days. > > If you look at line 1 and 2, I would use line #2 because it has 2 LINE_NUM > that are the same, but the second SEQ_NUM is higher. So I keep it. > > Lines 3 through 12 are the same date, with 5 sets of LINE_NUMs. > - #3 we keep, it is unique > - #6 we keep, because it's CEL_NUM is highest, even though it has the > same LINE_NUM and SEQ_NUMs > - #8 & #10 we keep, same reason as #6 > - we keep both 12 & 12 because the have the same numbers all across the 3 > fields. So you're selecting the rows for each date and line_num that have (or tie for) the highest line_num and seq_num. You don't mention whether cel_num or seq_num has higher priority. Which of these two rows would be selected? ID DATE line_num seq_num cell_num time 1 APM 2007-10-02 2 0 1 6.00 2 APM 2007-10-02 2 1 0 5.50 -- Peter Harkins - http://push.cx - http://NearbyGamers.com |
|
From: jsWalter <jsW...@to...> - 2007-10-12 19:04:15
|
> So you're selecting the rows for each date and line_num that have (or > tie for) the highest line_num and seq_num. > > You don't mention whether cel_num or seq_num has higher priority. > Which of these two rows would be selected? > > ID DATE line_num seq_num cell_num time > 1 APM 2007-10-02 2 0 1 6.00 > 2 APM 2007-10-02 2 1 0 5.50 Sorry, #2 has the higher priority. walter |
|
From: Peter H. <ph...@ma...> - 2007-10-12 16:53:19
|
On Fri, Oct 12, 2007 at 11:25:26AM -0500, jsWalter wrote: > > You don't mention whether cel_num or seq_num has higher priority. > > Which of these two rows would be selected? > > > > ID DATE line_num seq_num cell_num time > > 1 APM 2007-10-02 2 0 1 6.00 > > 2 APM 2007-10-02 2 1 0 5.50 > > SEQ has higher priority than CELL > > If multiple LINE, use higher SEQ > If multiple SEQ, use higher CELL > If multiple SEQ and multiple CELL are the same, keep all. So in this example, #1 would be selected? -- Peter Harkins - http://push.cx - http://NearbyGamers.com |
|
From: Richard L. <ce...@l-...> - 2007-10-16 19:19:08
|
On Fri, October 12, 2007 12:28 am, jsWalter wrote: > I have a very unusual case on my hands. > > I'm working with a 3rd party AR/Time mgt system from hell! > > The time sheets are being particularly difficult. And no, I can't > change > the schema, change the system, use linux, or anything else in that > vane. I > have to use what I have. > > Here is a sample record set... > > ID DATE line_num seq_num cell_num time > 1 APM 2007-10-01 1 0 0 8.00 > 2 APM 2007-10-01 1 1 0 8.00 > 3 APM 2007-10-02 1 0 0 0.50 > 4 APM 2007-10-02 2 0 0 6.50 > 5 APM 2007-10-02 2 0 1 6.00 > 6 APM 2007-10-02 2 0 2 5.50 > 7 APM 2007-10-02 3 0 0 1.00 > 8 APM 2007-10-02 3 0 1 0.50 > 9 APM 2007-10-02 4 0 0 1.00 > 10 APM 2007-10-02 4 0 1 1.25 > 11 APM 2007-10-03 1 0 0 1.25 > 12 APM 2007-10-03 1 0 0 2.25 > > What I need to do is pull out it the time for each day. This example > has 3 > days. > > If you look at line 1 and 2, I would use line #2 because it has 2 > LINE_NUM > that are the same, but the second SEQ_NUM is higher. So I keep it. > > Lines 3 through 12 are the same date, with 5 sets of LINE_NUMs. > - #3 we keep, it is unique > - #6 we keep, because it's CEL_NUM is highest, even though it has the > same LINE_NUM and SEQ_NUMs > - #8 & #10 we keep, same reason as #6 > - we keep both 12 & 12 because the have the same numbers all across > the 3 > fields. > > Confused!? > > Steps, as I see them... > 1 - collect in an array date sets > 2 - collect into an array of LINE_NUM sub-sets by date > 3 - collect into an array of LINE_NUM by LINE_NUM by date > 4 - collect into an array of SEQ_NUM by LINE_NUM by LINE_NUM by date > 5 - SEQ_NUM value has the time for that record > > 2007-10-01 -> 1 -> 0 -> 0 = 8.00 > -> 1 -> 1 -> 0 = 8.00 > > 2007-10-02 -> 1 -> 0 -> 0 = 0.50 > -> 2 -> 0 -> 0 = 6.50 > -> 2 -> 0 -> 1 = 5.00 > -> 2 -> 0 -> 2 = 5.50 > -> 3 -> 0 -> 0 = 1.00 > -> 3 -> 0 -> 1 = 0.50 > -> 4 -> 0 -> 0 = 1.00 > -> 4 -> 0 -> 1 = 1.25 > > 2007-10-03 -> 1 -> 0 -> 0 = 1.25 > -> 1 -> 0 -> 0 = 2.25 > > Using indexed arrays I get... > > 2007-10-01 -> 1 -> 1 -> 0 = 8.00 > > 2007-10-02 -> 1 -> 0 -> 0 = 0.50 > -> 2 -> 0 -> 2 = 5.50 > -> 3 -> 0 -> 1 = 0.50 > -> 4 -> 0 -> 1 = 1.25 > > 2007-10-03 -> 1 -> 0 -> 0 = 2.25 > > This is 90% of what I need. > > 10-01 and 10-02 give me the result I need form the data they have, but > 10-03 fails. Because both SEQ_NUM and CEL_NUM are the same, I need to > keep > both of them. > > So, any minds greater than mine see anyway to do this? Without > convoluted > conditionals? If you are correct about the steps, then the conditionals with good variable names should not be that confusing... You could also consider pulling it all into SQL. -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X