I have query like this

SELECT user_ID, tanggal, kode_layanan, nomor_antrian, aksi_ID,jam 
FROM `transaksi` 
WHERE tanggal = '2018-10-11' 
  AND kode_layanan = 'A' 

and the result is like the picture below

enter image description here

what I want to do is do calculation time difference "jam". I know there are similar question, but i think its difference condition. so how to solve query ? thanks

  • Why those two rows? What is their criteria?
    – danblack
    Oct 24, 2018 at 3:46
  • its just for example, not just those two rows but for further rows I want to calculation like that
    – coder
    Oct 24, 2018 at 3:50
  • Not good enough. You can't ask for a difference for two arbitrary rows that have a black box around them when it shows up on stack overflow, there must be some criteria for this. Did your last post get deleted because it wasn't specific enough?
    – danblack
    Oct 24, 2018 at 3:55
  • yes and I make it again to be easier to understand, can you solve this ?
    – coder
    Oct 24, 2018 at 3:59
  • So the relation ajsu_ID 4 and 9 row? Sequential (based on what order)? Is 4 always after 9. Do they have to be the same user_ID, tanggal etc.?
    – danblack
    Oct 24, 2018 at 4:03

1 Answer 1


I think your column definition will be as shown in this query:


    case aksi_ID
        when 4 then
            timediff (
                    select t2.jam
                    from transaki t2
                    where t2.aksi_ID = 9
                    and t2.nomor_antrian = 
                            select max(t3.nomor_antrian)
                            from transaki t3
                            where t3.nomor_antrian < transaki.nomor_antrian
    end as idle_time

from transaki
order by jam;

The logic is: "if the current row has an aski_ID value of '4' then find the time difference between the current row's jam value, and the jam value of the row having an aski_ID value of 9 and a nomor_antrian value that is the maximum nomor_antrian value less than the current row's nomor_antrian value".

You can see this result working on db-fiddle.

  • thanks for your replying, but can you give me a full code because I tried your code into my code and give me some error
    – coder
    Oct 24, 2018 at 6:38
  • I'm sorry - I had one syntax, one typographical and one logic error. I have corrected the SQL in the answer. I have also added a link at the end of the answer where you can see this running on db-fiddle (ie, here: db-fiddle.com/f/5P6k9qMNP8UCoGHuQe1J2G/1 ) Oct 25, 2018 at 0:28

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.