0

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 aksi_ID BETWEEN 4 AND 9

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 '18 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 '18 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 '18 at 3:55
  • yes and I make it again to be easier to understand, can you solve this ? – coder Oct 24 '18 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 '18 at 4:03
0

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

select
    user_ID,
    tanggal,
    kode_layanan,
    nomor_antrian,
    aksi_ID,
    jam,

    case aksi_ID
        when 4 then
            timediff (
                jam,
                (
                    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
                        )
                )
            )
        else
            null
    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.

| improve this answer | |
  • 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 '18 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 ) – youcantryreachingme Oct 25 '18 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.