Hi there, I need your appreciated help.
myTable_1
ID iTES ES myDate Zn hh Line PP
31576 D50 FI 2011-07-29 D5R 15:41 11005 1
31579 D50 FI 2011-07-29 D5R 16:21 17825 1
31580 D50 FI 2011-07-29 D5R 16:21 25103 1
31581 D50 FI 2011-07-29 D5R 16:21 25102 1
31582 D50 FI 2011-07-29 D5R 16:21 25104 1
31583 D50 FI 2011-07-29 D5R 16:00 11706 1
myTable_2
ID iTES myDate hh Line Zn PP
60770 D50 2011-07-29 08:58:35 13715 D5R 0
60773 D50 2011-07-29 07:40:59 19007 D5R 0
60778 D50 2011-07-29 09:21:44 25104 D5R 0
60795 D50 2011-07-29 17:00:36 11706 D5R 0
And quartile, one of the three points that divide an ordered distribution into four parts, each containing one quarter of hours:
1) 00:00 - 06:00
2) 06:00 - 12:00
3) 12:00 - 18:00
4) 18:00 - 00:00
Considering for each quartile three hours less than at the beginning and three hours later than the end:
1) 00:00 - 06:00 21:00 - 09:00
2) 06:00 - 12:00 03:00 - 15:00
3) 12:00 - 18:00 09:00 - 21:00
4) 18:00 - 00:00 15:00 - 03:00
I need update the fields PP in the myTable_2 for records #ID 60778 and #ID 60795:
ID iTES myDate hh Line Zn PP
60778 D50 2011-07-29 09:21:44 25104 D5R 1
60795 D50 2011-07-29 17:00:36 11706 D5R 1
Because (for priority):
- PP (myTable_1) = 1
- iTES (myTable_1) = iTES (myTable_2)
- myDate (myTable_1) = myDate (myTable_2)
- Zn (myTable_1) = Zn (myTable_2)
- Line (myTable_1) = Line (myTable_2)
And:
4) In myTable_2 #ID 60778 the value of field hh -09:21:44- is part of quartile (-3)12:00 - 18:00(+3) (09:00-21:00);
5) In myTable_2 #ID 60795 the value of field hh -17:00:36- is part of quartile (-3)12:00 - 18:00(+3) (09:00-21:00);
Can you help me?
Thanks in advance-
-09:21:44- is also part of 2) 06:00 - 12:00 03:00 - 15:00
and
-17:00:36- is also part of 4) 18:00 - 00:00 15:00 - 03:00
And the ‘quartile’ you considered is number 3.
But you want to update PP in table 2 with the value ‘1’ ?
How do you decide the value of PP?
Thanks for your reply.
The quartile of starting are:
1) 00:00 - 06:00
2) 06:00 - 12:00
3) 12:00 - 18:00
4) 18:00 - 00:00
For this reason considered in the quartile #2:
And in the quartile #3:
But when in myTable_2 I have events related with events to myTable_1 (including to a quartile #2 and #3 and with field PP = 1), I need also take events in myTable_2 included for each quartile three hours less than at the beginning and three hours later than the end.
1) 21:00 - 09:00
2) 09:00 - 21:00
2) 03:00 - 15:00
4) 15:00 - 03:00
In this situation I want to update PP in myTable_2 with the value 1.
I decide the value of PP when satisfy the above condition.
The value 1 in the field PP of mytable_1 identify critical event… I need search events corresponding in the myTable_2 and update PP in myTable_2 with the value 1… the fields should be discriminating are:
1) `PP` (myTable_1) = 1
2) `myDate` (myTable_1) = `myDate` (myTable_2)
3) `Zn` (myTable_1) = `Zn` (myTable_2)
4) `hh` (myTable_1) and `hh` (myTable_2) considered in the `quartile`
Maybe you can think about this…
I’m sorry if is confused…
In this situation I want to update PP in myTable_2 with the value 1.
I still don’t understand how you decide what value to put in PP in the second table? Do you take it from the first table?
Select for the myTable_1 all events with the value 1 in the PP: critical event.
Search in the myTable_2 all events corresponding to the quartile where to find critical event of the myTable_1 and to update PP in myTable_2 to value 1.
For example, critical event in myTable_1
ID iTES ES myDate Zn hh Line PP
31576 D50 FI 2011-07-29 D5R 15:41 11005 1
31579 D50 FI 2011-07-29 D5R 16:21 17825 1
31580 D50 FI 2011-07-29 D5R 16:21 25103 1
31581 D50 FI 2011-07-29 D5R 16:21 25102 1
31582 D50 FI 2011-07-29 D5R 16:21 25104 1
31583 D50 FI 2011-07-29 D5R 16:00 11706 1
The quartile is:
3) 12:00 - 18:00
All events in myTable_2:
ID iTES myDate hh Line Zn PP
60770 D50 2011-07-29 08:58:35 13715 D5R 0
60773 D50 2011-07-29 07:40:59 19007 D5R 0
60778 D50 2011-07-29 09:21:44 25104 D5R 0
60795 D50 2011-07-29 17:00:36 11706 D5R 0
I need update (PP=1) the event #ID 60795 in the myTable_2 because including in the quartile #3:
ID iTES myDate hh Line Zn PP
60795 D50 2011-07-29 17:00:36 11706 D5R 1
It is the first time because in the second time update too the #ID 60778 with PP=1 (for each quartile three hours less than at the beginning and three hours later than the end).
Thanks for your help.
UPDATE myTable_2
INNER JOIN myTable_1
ON myTable_2.iTES = myTable_1.iTES
AND myTable_2.myDate = myTable_1.myDate
AND myTable_2.Zn = myTable_1.Zn
AND myTable_2.Line = myTable_1.Line
SET myTable_2.PP = myTable_1.PP
WHERE myTable_1.PP = '1'
AND myTable_2.hh BETWEEN ... here you must do something with the time limits of the quarter that myTable_1.hh is in
The query isn’t finished. Do you have a table with the quartiles?
Yes, I have this table for quartile (myTable_Quartiles):
ID FirstTime LastTime
1 00:00 06:00
2 06:00 12:00
3 12:00 18:00
4 18:00 00:00
Thanks for your help.
You’ll have to join that one too (with table1.hh between firsttime and lasttime), and then check that the hh of the second table is between the firstime - 3 hours and lasttime + 3 hours boundaries.
I don’t have time to do more now. Try finishing the query, and if you can’t get it to work ask again. I’m sure someone else will help you.
Thanks for your suggestion, but I try this but not working…:
Affected rows: 4 and not 2 rows…
[SQL] UPDATE ______myTable_2
INNER JOIN ______myTable_1
INNER JOIN ______mytable_quartiles
ON ______myTable_2.iTES = ______myTable_1.iTES
AND ______myTable_2.myDate = ______myTable_1.myDate
AND ______myTable_2.Zn = ______myTable_1.Zn
SET ______myTable_2.PP = ______myTable_1.PP
WHERE 1 AND
______myTable_1.PP = '1'
AND ______mytable_2.hh BETWEEN ______mytable_quartiles.FirstTime AND ______mytable_quartiles.LastTime
Affected rows: 4
Time: 0.047ms
I can’t thank you enough for the help. That worked perfectly.
ID FirstTime LastTime
1 24:00 06:00
2 06:00 12:00
3 12:00 18:00
4 18:00 00:00
UPDATE ______myTable_2 A
INNER JOIN ______myTable_1 CA
ON A.iTES = CA.iTES
AND A.myDate = CA.myDate
AND A.Zn = CA.Zn
INNER JOIN ______mytable_quartiles CB
ON CA.hh BETWEEN CB.FirstTime AND CB.LastTime
SET A.PP = CA.PP
WHERE 1
AND A.hh BETWEEN CB.FirstTime AND CB.LastTime
OR (A.hh BETWEEN ADDTIME(CB.FirstTime, -30000)
AND ADDTIME(CB.LastTime, 30000))
AND CA.PP = '1';
Output:
ID iTES mydate hh Line Zn PP
60778 D50 2011-07-29 09:21:44 25104 D5R 1
60795 D50 2011-07-29 17:00:36 11706 D5R 1
Ooops… 
ID FirstTime LastTime
1 00:00 06:00
2 06:00 12:00
3 12:00 18:00
4 18:00 00:00
UPDATE ______myTable_2 A
INNER JOIN ______myTable_1 CA
ON A.iTES = CA.iTES
AND A.myDate = CA.myDate
AND A.Zn = CA.Zn
INNER JOIN ______mytable_quartiles CB
ON CA.hh BETWEEN CB.FirstTime AND CB.LastTime
SET A.PP = CA.PP
WHERE 1
AND (A.hh BETWEEN CB.FirstTime AND CB.LastTime)
OR (A.hh BETWEEN TIME(CONCAT(CURDATE(),' ',CB.FirstTime) - INTERVAL 180 MINUTE)
AND TIME(CONCAT(CURDATE(),' ',CB.LastTime) + INTERVAL 180 MINUTE))
AND CA.PP = '1';