Skip to content

Instantly share code, notes, and snippets.

@cesarockstar1985

cesarockstar1985/gist:5ee1b6e0bb16a964cddf855ab9dce7cb

Created December 18, 2024 18:17
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save cesarockstar1985/5ee1b6e0bb16a964cddf855ab9dce7cb to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save cesarockstar1985/5ee1b6e0bb16a964cddf855ab9dce7cb to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
WITH RECURSIVE split(testing_no_performed_according_protocol_reason, rest) AS
(SELECT '',
testing_no_performed_according_protocol_reason||';'
FROM impl_recurrent_visit WHERE route_detail_id = 'monsanto|1734177726753|1251712363|172' AND cast_draft_status = 10
UNION ALL SELECT substr(rest, 0, instr(rest, ';')),
substr(rest, instr(rest, ';')+1)
FROM SPLIT
WHERE rest!='' )
SELECT
a.description,
jira_select_id
FROM
(
SELECT
rv.no_good_deal_with_the_inspector_reason description
FROM impl_recurrent_visit rv
WHERE rv.route_detail_id = 'monsanto|1734177726753|1251712363|172' AND rv.cast_draft_status = 10
UNION ALL
SELECT
no_access_to_pod_reason description
FROM impl_recurrent_visit rv
WHERE rv.route_detail_id = 'monsanto|1734177726753|1251712363|172' AND rv.cast_draft_status = 10
UNION ALL
SELECT
case when rv.access_to_truck_draft_point = 'NO' then 'Sin acceso al punto de calado' end description
FROM impl_recurrent_visit rv
WHERE rv.route_detail_id = 'monsanto|1734177726753|1251712363|172' AND rv.cast_draft_status = 10
UNION ALL
SELECT
case when rv.correct_draft = 'NO' then 'Toma de muestra en calado no representativa' end description
FROM impl_recurrent_visit rv
WHERE rv.route_detail_id = 'monsanto|1734177726753|1251712363|172' AND rv.cast_draft_status = 10
UNION ALL
SELECT
case when rv.have_access_to_the_analysis_room = 'NO' then 'Sin acceso al laboratorio' end description
FROM impl_recurrent_visit rv
WHERE rv.route_detail_id = 'monsanto|1734177726753|1251712363|172' AND rv.cast_draft_status = 10
UNION ALL
SELECT
differences_in_inventory_of_test_strips_reason description
FROM impl_recurrent_visit rv
WHERE rv.route_detail_id = 'monsanto|1734177726753|1251712363|172' AND rv.cast_draft_status = 10
UNION ALL
SELECT
testing_no_performed_according_protocol_reason
FROM SPLIT
WHERE testing_no_performed_according_protocol_reason!=''
UNION ALL
SELECT
case when rv.samples_of_first_disposition_are_protected_for72hours = 'NO' then 'No resguardan muestras por 72 hs' end description
FROM impl_recurrent_visit rv
WHERE rv.route_detail_id = 'monsanto|1734177726753|1251712363|172' AND rv.cast_draft_status = 10
UNION ALL
SELECT
CASE WHEN rv.random_tests_on_non_inspected_samples is not 'Permite realizar retesteo, sin cambios de resultados'
AND rv.random_tests_on_non_inspected_samples is not 'No hay cargas con resultado negativo'
THEN rv.random_tests_on_non_inspected_samples end description
FROM impl_recurrent_visit rv
WHERE rv.route_detail_id = 'monsanto|1734177726753|1251712363|172' AND rv.cast_draft_status = 10
UNION ALL
SELECT
case when rv.retesting_for_producer_orders_only_with_presence_of_inspector = 'SI' then 'Retesteo en ausencia del fiscalizador.' end description
FROM impl_recurrent_visit rv
WHERE rv.route_detail_id = 'monsanto|1734177726753|1251712363|172' AND rv.cast_draft_status = 10
UNION ALL
SELECT
case when rv.access_to_romaneos_of_previous_days = 'NO' then 'Sin acceso a romaneos del día / Días anteriores' end description
FROM impl_recurrent_visit rv
WHERE rv.route_detail_id = 'monsanto|1734177726753|1251712363|172' AND rv.cast_draft_status = 10
UNION ALL
SELECT
case when rv.use_intact_seal = 'NO' then 'No se utiliza sello INTACTA POSITIVO' end description
FROM impl_recurrent_visit rv
WHERE rv.route_detail_id = 'monsanto|1734177726753|1251712363|172' AND rv.cast_draft_status = 10
UNION ALL
SELECT
case when rv.attach_strips_of_romaneo_or_are_clearly_identifiable = 'NO' then 'No adjuntan tiras legibles al ROMANEO' end description
FROM impl_recurrent_visit rv
WHERE rv.route_detail_id = 'monsanto|1734177726753|1251712363|172' AND rv.cast_draft_status = 10
UNION ALL
SELECT
case when rv.correct_result_in_romaneo = 'NO' then 'Resultado impreso en romaneo INCORRECTO' end description
FROM impl_recurrent_visit rv
WHERE rv.route_detail_id = 'monsanto|1734177726753|1251712363|172' AND rv.cast_draft_status = 10
UNION ALL
SELECT
case when rv.received_soybeans_at_night_yesterday = 'SI' AND reported_receiving_soybeans = 'NO'
then 'Carga nocturna sin aviso' end description
FROM impl_recurrent_visit rv
WHERE rv.route_detail_id = 'monsanto|1734177726753|1251712363|172' AND rv.cast_draft_status = 10
UNION ALL
SELECT
case when rv.retesting_for_producer_orders_only_with_absence_of_inspector_re = 'Retesteo con cambio de resultado'
then 'Retesteo con cambio de resultado' end description
FROM impl_recurrent_visit rv
WHERE rv.route_detail_id = 'monsanto|1734177726753|1251712363|172' AND rv.cast_draft_status = 10
) a
left join impl_jira_incidence_type b on a.description = b.description
left join impl_exceptions c on b.jira_select_id = c.jira_issue_type
left join impl_point_of_delivery_exception d on d.exception_id = c.portal_id and d.subject_id = 'monsanto|1732912325720|1215666425|1'
where a.description <> '' and coalesce(d.is_exception, 'false') = 'false' and coalesce(b.deleted, 'false') = 'false'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment