0

I'm trying to create a view with progressively increment of the datetime field depending on the number of records and job_id.
This is my structure on mysql 5.6

jobs

| job_id | job_group_id | job_exec_data_sched | job_email_timeout |
+--------+--------------+---------------------+-------------------+
|  1     |     1        | 2019-10-21 10:00:00 |       30          |
|  2     |     1        | 2019-10-22 14:00:00 |        0          |
|  3     |     2        | 2019-10-22 15:00:00 |       10          |

contacts

| contact_id | contact_group_id | contact_email |
+------------+------------------+---------------+
|  1399      |       1          |  aa@mail.com  |
|  1401      |       1          |  bb@mail.com  |
|  1402      |       1          |  cc@mail.com  |
|  2800      |       1          |  dd@mail.com  |
|  2801      |       2          |  dd@mail.com  |
|  3000      |       2          |  cc@mail.com  |

My goal would be to get this

| job_id | job_group_id | contact_email | date_schedule       |
+--------+--------------+---------------+---------------------+
|    1   |     1        |  aa@mail.com  | 2019-10-21 10:00:00 |
|    1   |     1        |  bb@mail.com  | 2019-10-21 10:00:30 |
|    1   |     1        |  cc@mail.com  | 2019-10-21 10:01:00 |
|    1   |     1        |  dd@mail.com  | 2019-10-21 10:01:30 |

|    2   |     1        |  aa@mail.com  | 2019-10-22 14:00:00 |
|    2   |     1        |  bb@mail.com  | 2019-10-22 14:00:00 |
|    2   |     1        |  cc@mail.com  | 2019-10-22 14:00:00 |
|    2   |     1        |  dd@mail.com  | 2019-10-22 14:00:00 |

|    3   |     2        |  dd@mail.com  | 2019-10-25 15:00:00 |
|    3   |     2        |  cc@mail.com  | 2019-10-25 15:00:10 |

I tried to do it this way, but without success because I can't differentiate records for job_id

SELECT 
j.job_id,
j.job_group_id, 
c.contact_email, 
j.job_exec_data_sched 
+ interval j.job_email_timeout * (@curRow:=@curRow + 1) second
- interval j.job_email_timeout second
from jobs j
left join contacts c on j.job_group_id = c.contact_group_id 
join (select @curRow := 0) r

Is it possible to do this?
Do you have any suggestions for me to do this? Thanks!

  • Is it possible to do this? Yes. suggestions for me to do this? Enumerate records within contacts.contact_group_id using 2 table copies. – Akina Oct 21 at 16:28
  • Could you give me an example? I'm going crazy with this. Thank you – Paolo Rossi Oct 21 at 16:31
1

How to enumerate records in contacts table within contact_group_id groups (emulate ROW_NUMBER) starting with 0:

SELECT t1.contact_id, 
       t1.contact_group_id, 
       t1.contact_email, 
       COUNT(t2.contact_id) - 1 row_number
FROM contacts t1
JOIN contacts t2 ON t1.contact_group_id = t2.contact_group_id
                AND t1.contact_id >= t2.contact_id
GROUP BY t1.contact_id, t1.contact_group_id, t1.contact_email

Use this enumeration in DATEADD().

  • I thank you, I will try your solution as soon as I can. I hope I can get what I am looking for – Paolo Rossi Oct 21 at 17:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.