Skip to content
Snippets Groups Projects

get_tariff_result

  • Clone with SSH
  • Clone with HTTPS
  • Embed
  • Share
    The snippet can be accessed without any authentication.
    Authored by Emanuel Mosegue
    Edited
    get_tariff_result.sql 1.37 KiB
    SELECT 
      ss.clientId,
      TIMESTAMP_DIFF(se.service.serviceEndAt, se.service.serviceStartAt, HOUR) AS duration,
      se.service.requirements.assetKindAlias as asset_kind,
      CASE 
        WHEN se.service.requirements.auxiliaries = 0 THEN false
        ELSE true
      END AS auxiliaries,
      ss.hubId,
      ers.municipality as destination,
      p.details.serviceId,
      p.details.driverId,
      CAST(p.details.totalAmount / 100 AS INT64)  as price,
      CAST(c.costTotal / 100 AS INT64) as cost,
      ss.name
    FROM cabify-prodops.cabify_dw.logistics_pricing_ServiceBillableV1 p
    INNER JOIN cabify-prodops.cabify_dw.logistics_pricing_LogisticsCostV1 c
      ON p.details.serviceId = c.serviceId 
      AND p.details.driverId = c.driverId
    INNER JOIN `cabify-prodops.cabify_dw.service_offers_IndexServiceV2` ss 
      ON p.details.serviceId = ss.serviceId
    INNER JOIN `cabify-prodops.cabify_dw.barad_dur_ExternalRouteServiceStartedV1` ers 
      ON ss.serviceId = ers.serviceId 
    INNER JOIN `cabify-prodops.cabify_dw.service_offers_ServiceEventV1` se 
      ON ss.serviceID = se.service.serviceId
    WHERE ss.deleted = false 
      AND ss.serviceType = "SERVICE_TYPE_EXTERNAL_ROUTE"
      GROUP BY 
      p.details.serviceId,
      p.details.driverId,
      ss.name,
      p.details.totalAmount,
      c.costTotal,
      ss.clientId,
      se.service.requirements.assetKindAlias,
      ss.hubId,
      ers.municipality,
      auxiliaries,
      TIMESTAMP_DIFF(se.service.serviceEndAt, se.service.serviceStartAt, HOUR)
    0% Loading or .
    You are about to add 0 people to the discussion. Proceed with caution.
    Please register or to comment