On Wed, Jun 9, 2021 at 5:36 AM abhijit <[email protected]> wrote:
> Hi Jeremy,
>
> I have a orders table, which has order_id and status_id and date
>
> orders
> ======
> id order_id status_id date
> 1 123 22 2021-03-22
> 33 123 55 2021-03-29
>
> How do I write a query to fetch the difference between the dates of two
> records with the same order_id.
>
> The output required is -
>
> order_id date_diff
> 123 7
>
> Database can be either Posgres or SQLite.
>
Assuming you only have two rows per order_id, or if you have more than two
rows, you are OK with the difference between the latest and earliest date
for each order:
ds = DB[:orders].select_group(:order_id)
ds = ds.select_append{max(:date).as(:max)}.
from_self.
join(ds.select_append{min(:date).as(:min)}, [:order_id])
# PostgreSQL
ds.select{[:order_id, (max - min).as(:date_diff)]}.all
# SQLite
ds.select{[:order_id, (julianday(:max) -
julianday(:min)).cast(Integer).as(:date_diff)]}.all
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CADGZSSeF2ZXMOXADTsX5GqSfd%2BGVwr0D-q7ygfhXEa7EMeiOxA%40mail.gmail.com.
