CODEWITHSUNDEEP
amazon-web-servicesleft-joinamazon-athenaperiod
Wissam96
Wissam96

Reputation: 11

Athena SQL join Query not working as written

I am creating a query from a table that has different time periods. I am trying to connect them based on when the charge was created. The table has multiple IDs since each ID can have multiple periods.

Below you may find the closest join that I was able to do but sadly it isn't working as I did, since there are sometimes it checks the first ID and if the first condition which is cast(ProdPostgres.payment.charge.created_at as date) >= cast(subscription_periods_v2.period_from as date) and cast(ProdPostgres.payment.charge.created_at as date) <= cast(subscription_periods_v2.period_to as date)) is false, it is jumping to another line to check the other conditions, I do not want that, I want it keep checking all IDs for the first condition then jump to the second, then third... may someone help me out? I'm out of luck...

left join measures.subscription_periods_v2 
  on case when ProdPostgres.payment.charge.type = 'RENTAL_FEE'
          then ProdPostgres.payment.charge.id = measures.subscription_periods_v2.charge_id
     else
        ProdPostgres.payment.booking.external_booking_id = measures.subscription_periods_v2.bookingid and ( (cast(ProdPostgres.payment.charge.created_at as date) >= cast(subscription_periods_v2.period_from as date)  and cast(ProdPostgres.payment.charge.created_at as date) <= cast(subscription_periods_v2.period_to as date))
                                                                                                                or    cast(ProdPostgres.payment.charge.created_at as date) >= cast(subscription_periods_v2.period_to as date)

                                                                                                                or cast(ProdPostgres.payment.charge.created_at as date) <= cast(subscription_periods_v2.period_from as date)   )

I tried different conditions but none of them worked, this was the closet to getting the period that i want. and I explained everything in the above.

This image is the result that i get This is the table where i am joining

The result should be from the first picture the first line should have in column AX 2023-01-25, and AY 2023-02-24 and AU the the created_at that i am doing the conditions with. But luckily i am getting the wrong answer :( the subscription_periods_v2 table is ordered by period from desc .

Upvotes: 1

Views: 209

Answers (0)

Related Questions