0

If a call (calls other) starts before the (calls c _ call_end_operator) then I need the c.call_end_operator to be other.call_start - 2 seconds.

update 
        calls c  
INNER JOIN
        calls other
ON      c.operator_id = other.operator_id

SET     c.call_end_operator= datediff(second,other.call_start,2)

WHERE   
        c.call_id != other.call_id
AND
        other.call_start BETWEEN c.call_start AND c.call_end
AND
        cast(c.Call_start as date)=date '2021-02-02 '

I have tried multiple ways but for the life of me I can't get something really silly (I'm sure) done.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
L_GIB
  • 125
  • 7
  • did you try `MERGE` command? anyway, go to https://dbfiddle.uk/?rdbms=firebird_3.0 and make required tables with sample data to demonstrate what you have and what you need. Also, add one more tag with your Firebird version. – Arioch 'The Mar 28 '21 at 17:23
  • Also, if it is about `then I need the c.call_end_operator to be other.call_start - 2 seconds` - then create the `after insert or update` SQL trigger, would you? But personally i would refrain from doing so. There can well be overlapping sessions for support operators... You seem to fake primary raw data for the sake of some rather abstract "theory" of what "SHOULD" be happening. I do not think that is correct. – Arioch 'The Mar 28 '21 at 17:27
  • `c.call_id != other.call_id` also assuming you have typical generator-based ever-increasing IDs then you only have to check one second from last talk of that operator, not all of talks. `other.call_id` would be by definition equal to `(select first(1) call_id from calls where (operator_id = c.operator_id) and (call_id < c.call_id) order by call_id descending)` - but again, ur approach seems wrong to me, hypothetically it can lead to negative timespan (start_time > end_time), etc. If your business rules prohibit operator having two sessions - then your app should make it impossible proactively – Arioch 'The Mar 28 '21 at 17:34
  • thank you for your comments. We allow the operator to have more than one message box opened at the same time. When a 'box' is opened (call_start) a call is in progress till the caller hangs up (call_end) and the operator keeps working on the call till she completes (call_end_operator).. between call_end and call_end_operator (wrap up time) another call can start.. the operator is meant to close the previous call (meant to be on DND but often are not).. so if other.call_start begins before c.call_end_operator I need to change the c.call_end_operator to be 2 seconds before the other.Call_start. – L_GIB Mar 29 '21 at 04:19
  • `the operator is meant to close the previous call` is contradicting `We allow the operator to have more than one message box opened`. Basically it feels liek you are about faking KPI data against what opperators actually do. If it should be impossible to open new message boxes (accept user's calls) before previous call is finalized. Or it should be allowed to operators and then properly accounted for in the database. You seem to try to force operatores work in several calls in parallel, but only count one of those for salary/bonuses. Technically, you may fake the data using SQL triggers. – Arioch 'The Mar 29 '21 at 19:32
  • The operator 'forgets' the box opened and starts working on another. I'll working something out another way. Thank you – L_GIB Mar 30 '21 at 04:16
  • this "forgets" is tale-telling. It seems operators want to maximize performance due to some idealistic emotions, while management seeks to minimize performance (but mabe maximize reliability or something else) by enforcing strict rules. It would be Sisyphus battle. I believe correct way wouuld be streamlining client application: if opening multiple windows is prohibited my bosses - then it should just be impossible in the app. Then operators would not "forget" closing but would do what they must. – Arioch 'The Mar 30 '21 at 17:20
  • Instead of the unhelpful mess of independently floating windows, prohibited to use, i'd make the app like cinema film - sole lane with clear separators between sessions and clear FSM. Would be easy for operators to use properly and would clear you from requirements of faking actual data. YMMV of course. – Arioch 'The Mar 30 '21 at 17:23

0 Answers0