API tools faq
paste
cldscchttn

Performance consulenti

cldscchttn
Nov 11th, 2025
207
0
Never
1
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 22.18 KB | None | 0 0
raw download clone embed print report
  1.  create table risultati_performance_consulenti as(
  2.  with
  3.  dati as (
  4.         select
  5.             a.company,
  6.             a.tenant,
  7.             a.id,
  8.             a.date_start,
  9.             current_timestamp AT TIME ZONE 'Europe/Rome' AS data_ultimo_aggiornamento,
  10.             date(a.date_end) AS giorno,
  11.             min(case
  12.                 when a3.date_start - a.date_start <  '00:25:00'::time then '< 25 minuti'
  13.                 when a3.date_start - a.date_start <  '00:50:00'::time then 'fra 25 e 50 minuti'
  14.                 when a3.date_start - a.date_start >= '00:50:00'::time then '> 50 minuti'                     
  15.             end) as distanza,
  16.             CASE
  17.                 WHEN a.date_end BETWEEN current_date AND current_date + interval '1 days' THEN 'oggi'
  18.                 WHEN a.date_end BETWEEN current_date - interval '1 days' AND current_date + interval '1 days' THEN 'ieri'
  19.                 WHEN a.date_end BETWEEN current_date - interval '2 days' AND current_date + interval '1 days' THEN '2-3'
  20.                 WHEN a.date_end BETWEEN current_date - interval '6 days' AND current_date + interval '1 days' THEN '4-7'
  21.                 WHEN a.date_end BETWEEN current_date - interval '14 days' AND current_date + interval '1 days' THEN '8-15'
  22.                 WHEN a.date_end BETWEEN current_date - interval '29 days' AND current_date + interval '1 days' THEN '16-30'
  23.                 WHEN a.date_end BETWEEN current_date - interval '59 days' AND current_date + interval '1 days' THEN '31-60'
  24.                 WHEN a.date_end BETWEEN current_date - interval '89 days' AND current_date + interval '1 days' THEN '61-90'
  25.                 WHEN a.date_end BETWEEN current_date - interval '119 days' AND current_date + interval '1 days' THEN '91-120'
  26.                 WHEN a.date_end BETWEEN current_date - interval '179 days' AND current_date + interval '1 days' THEN '121-180'
  27.             END AS periodo,
  28.             lower(a.company) AS azienda,
  29.             lower(a.tenant) AS area,
  30.             s.id AS id_store,
  31.             lower(s.alias) AS istituto,
  32.             u2.id AS id_op,
  33.             u2.lastname AS operatore_cognome,
  34.             u2.firstname AS operatore_nome,
  35.             operatore_cognome + ' ' + operatore_nome AS operatrice,
  36.             u3.id AS id_op_comm,
  37.             u3.lastname AS operatore_cognome_comm,
  38.             u3.firstname AS operatore_nome_comm,
  39.             operatore_cognome_comm + ' ' + operatore_nome_comm AS operatrice_comm,
  40.             a.customer_id AS id_customer,
  41.             a.id AS id_app,
  42.             case when a.user_id_comm is not null then 1 else 0 end as commerciale_operativo,
  43.             CASE
  44.                 WHEN ac.visita_commerciale_id_riattr IN ('commerciale_1') THEN 'commerciale 1v'
  45.                 WHEN ac.visita_commerciale_id_riattr IN ('commerciale_0') THEN 'commerciale rientro'
  46.                 WHEN ac.visita_commerciale_id_riattr IN ('cliente_centro') THEN 'operativo'
  47.                 WHEN ac.visita_commerciale_id_riattr IN ('porta_1') THEN 'porta 1v'
  48.                 WHEN ac.visita_commerciale_id_riattr IN ('rientro_porta') THEN 'porta rientro'
  49.                 WHEN ac.visita_commerciale_id_riattr IN ('contactcenter_1') THEN 'contact center 1v'
  50.                 WHEN ac.visita_commerciale_id_riattr IN ('contactcenter_0') THEN 'contact center rientro'
  51.             END AS commerciale,
  52.             max(oa.date) as disp,
  53.             max(case when date(a.date_start) = date(a2.date_start) then a2.user_id end) as id_op_vendita,
  54.             CASE
  55.                 WHEN a.user_times <= 1 THEN 'nuovo cliente'
  56.                 ELSE 'smaltimento'
  57.             END AS visita,
  58.             tc.name AS categoria_trattamento,
  59.             CASE
  60.                 WHEN t.f_contab IS NOT TRUE THEN a.id
  61.             END AS ingressi_singolo,
  62.             CASE
  63.                 WHEN (a.f_ref = 0 OR a.f_ref IS NULL) THEN a.price
  64.                 ELSE 0
  65.             END AS incasso_singolo,
  66.               sum(case
  67.                     when date(a.date_start) = date(a2.date_start)
  68.                     and (a2.f_ref = 0 OR a2.f_ref IS NULL)
  69.                     then a2.price
  70.                   end) AS incasso_primo_giorno,
  71.             count(case
  72.                     when date(a2.date_start) <= (date(a.date_start) + interval '30 days')
  73.                     and date(a2.date_start) > date(a.date_start)
  74.                     then true
  75.                   end) as ingresso_mese,
  76.             count( distinct
  77.                    case
  78.                     when date(a2.date_start) <= (date(a.date_start) + interval '30 days')
  79.                     and date(a2.date_start) > date(a.date_start)
  80.                     then a2.customer_id
  81.                    end) as clienti_mese,
  82.               sum( case
  83.                     when date(a2.date_start) <= (date(a.date_start) + interval '30 days')
  84.                     and date(a2.date_start) > date(a.date_start)
  85.                     AND (a2.f_ref = 0 OR a2.f_ref IS NULL)
  86.                     then a2.customer_id
  87.                  end) as incasso_mese,
  88.               count( distinct
  89.                  case
  90.                     when date(a.date_start) = date(a2.date_start)
  91.                     and (a2.f_ref = 0 OR a2.f_ref IS NULL)
  92.                     and a2.price >= 0
  93.                     then a2.customer_id
  94.                  end) as clienti_primo_giorno_paganti,
  95.               count( distinct
  96.                  case
  97.                     when date(a.date_start) = date(a2.date_start)
  98.                     and (a2.f_ref = 0 OR a2.f_ref IS NULL)
  99.                     and a2.price >= 100
  100.                     then a2.customer_id
  101.                  end) as clienti_primo_giorno_paganti_new,
  102.               sum(
  103.                  case
  104.                     when date(a.date_start) = date(a2.date_start)
  105.                     and (a2.f_ref = 0 OR a2.f_ref IS NULL)
  106.                     -- and a2.price >= 100
  107.                     then a2.price
  108.                  end) as cassa_primo_giorno_paganti_tot_new,
  109.               count( distinct
  110.                  case
  111.                     when date(a.date_start) < date(a2.date_start)
  112.                     and date(a2.date_start) <= (date(a.date_start) + interval '7 days')
  113.                     and (a2.f_ref = 0 OR a2.f_ref IS NULL)
  114.                     and a2.price >= 100
  115.                     then a2.customer_id
  116.                  end) as clienti_sette_giorni_paganti_new,
  117.               sum( distinct
  118.                  case
  119.                     when date(a.date_start) < date(a2.date_start)
  120.                     and date(a2.date_start) <= (date(a.date_start) + interval '7 days')
  121.                     and (a2.f_ref = 0 OR a2.f_ref IS NULL)
  122.                     -- and a2.price >= 100
  123.                     then a2.price
  124.                  end) as cassa_sette_giorni_paganti_new,
  125.               count( distinct
  126.                  case
  127.                     when date(a.date_start) < date(a2.date_start)
  128.                     and date(a2.date_start) <= (date(a.date_start) + interval '30 days')
  129.                     and (a2.f_ref = 0 OR a2.f_ref IS NULL)
  130.                     and a2.price >= 100
  131.                     then a2.customer_id
  132.                  end) as clienti_trenta_giorni_paganti_new,
  133.               sum( distinct
  134.                  case
  135.                     when date(a.date_start) < date(a2.date_start)
  136.                     and date(a2.date_start) <= (date(a.date_start) + interval '30 days')
  137.                     and (a2.f_ref = 0 OR a2.f_ref IS NULL)
  138.                     -- and a2.price >= 100
  139.                     then a2.price
  140.                  end) as cassa_trenta_giorni_paganti_new
  141.         FROM
  142.             appointments a INNER JOIN appointments_commerciale ac using (company, tenant, id)
  143.         INNER JOIN users u
  144.         ON
  145.             u.id = a.by_operator
  146.             AND u.company = a.company
  147.             AND u.tenant = a.tenant
  148.         INNER JOIN users u2
  149.         ON
  150.             u2.id = a.user_id
  151.             AND u2.company = a.company
  152.             AND u2.tenant = a.tenant
  153.         left join users u3
  154.         on
  155.             u3.id = a.user_id_comm
  156.             and u3.company = a.company
  157.             and u3.tenant = a.tenant
  158.         INNER JOIN stores s
  159.         ON
  160.             s.id = a.store_id
  161.             AND s.company = a.company
  162.             AND s.tenant = a.tenant
  163.         INNER JOIN treatments t
  164.         ON
  165.             t.id = a.treatment_id
  166.             AND t.company = a.company
  167.             AND t.tenant = a.tenant
  168.         INNER JOIN treatment_categories tc
  169.         ON
  170.             tc.id = t.treatment_category_id
  171.             AND tc.company = t.company
  172.             AND tc.tenant = t.tenant
  173.         LEFT JOIN operator_availabilities oa
  174.                     ON oa.user_id = u2.id
  175.                     AND oa.company = u2.company
  176.                     AND oa.tenant = u2.tenant
  177.                     AND oa.date BETWEEN date_trunc('month', current_date) AND last_day(current_date)
  178.                     AND oa.deleted_at IS NULL
  179.                     AND oa.reserved = 1
  180.         left outer join appointments AS a2
  181.                     on  a.customer_id = a2.customer_id
  182.                     AND a.company = a2.company
  183.                     AND a.tenant = a2.tenant
  184.                     AND a2.deleted_at IS NULL
  185.                     AND a2.paid IS TRUE
  186.                     AND a2.recalled < 3
  187.                     AND a.id <> a2.id
  188.         left outer join appointments AS a3
  189.                     on  a.user_id = a3.user_id
  190.                     and a.store_id = a3.store_id
  191.                     AND a.company = a3.company
  192.                     AND a.tenant = a3.tenant
  193.                     AND a3.deleted_at IS NULL
  194.                     AND a3.paid IS TRUE
  195.                     AND a3.recalled < 3
  196.                     AND a.id <> a3.id
  197.                     AND a3.date_start between a.date_start and (date(a.date_start) + interval '1 day')
  198.         WHERE
  199.             -- a.date_end >= '2025-10-21' -- (current_date - interval '180 days')
  200.             -- AND a.date_end < '2025-10-22' -- (current_timestamp AT TIME ZONE 'Europe/Rome') and
  201.             a.paid IS TRUE
  202.             AND a.tenant NOT IN ('Morepa', 'Lombardia', 'Bios')
  203.             -- and commerciale not in ('operativo')
  204.             AND a.company IN ('Biolaser')
  205.             -- and (a.tenant IN ('Veneto')) AND (istituto = 'verona')
  206.             AND a.deleted_at IS null
  207.             AND ( (t.f_contab IS TRUE
  208.                 AND (NOT EXISTS (
  209.                 SELECT
  210.                     *
  211.                 FROM
  212.                     appointments a3
  213.                 INNER JOIN treatments t2
  214.                 ON
  215.                     t2.id = a3.treatment_id
  216.                     AND t2.company = a3.company
  217.                     AND t2.tenant = a3.tenant
  218.                 WHERE
  219.                     a.customer_id = a3.customer_id
  220.                     AND a.company = a3.company
  221.                     AND a.tenant = a3.tenant
  222.                     AND t2.f_contab IS NOT TRUE
  223.                     AND ((a3.id <> a.id
  224.                         AND date(a3.date_start) = date(a.date_start)))
  225.                         AND a3.deleted_at IS NULL
  226.                         AND a3.recalled < 3
  227.                         AND a3.paid IS TRUE )))
  228.                 OR ( t.f_contab IS NOT TRUE
  229.                 AND NOT EXISTS (
  230.                 SELECT
  231.                     *
  232.                 FROM
  233.                     appointments a3
  234.                 INNER JOIN treatments t2
  235.                 ON
  236.                     t2.id = a3.treatment_id
  237.                     AND t2.company = a3.company
  238.                     AND t2.tenant = a3.tenant
  239.                 WHERE
  240.                     a.customer_id = a3.customer_id
  241.                     AND a.company = a3.company
  242.                     AND a.tenant = a3.tenant
  243.                     AND t2.f_contab IS NOT TRUE
  244.                     AND (a3.date_start < a.date_start
  245.                         AND date(a3.date_start) = date(a.date_start))
  246.                         AND a3.deleted_at IS NULL
  247.                         AND a3.recalled < 3
  248.                         AND a3.paid IS TRUE )))
  249.                 and not exists(
  250.                 SELECT
  251.                     *
  252.                 FROM
  253.                     operator_availabilities oa_prec
  254.                 WHERE
  255.                     oa_prec.user_id = u2.id
  256.                     AND oa_prec.company = u2.company
  257.                     AND oa_prec.tenant = u2.tenant
  258.                     AND oa_prec.date BETWEEN date_trunc('month', current_date) AND last_day(current_date)
  259.                     AND oa_prec.deleted_at IS NULL
  260.                     AND oa_prec.reserved = 1
  261.                     and oa_prec.date < oa.date
  262.                 )
  263.                 and not exists(
  264.                 select * from appointments as a3_prima
  265.                 where  a.user_id = a3_prima.user_id
  266.                     and a.store_id = a3_prima.store_id
  267.                     AND a.company = a3_prima.company
  268.                     AND a.tenant = a3_prima.tenant
  269.                     AND a3_prima.deleted_at IS NULL
  270.                     AND a3_prima.paid IS TRUE
  271.                     AND a3_prima.recalled < 3
  272.                     AND a.date_start < a3_prima.date_start
  273.                     AND a3_prima.date_start < a3.date_start
  274.                 )
  275.             group by
  276.             a.company,
  277.             a.tenant,
  278.             a.id,
  279.             a.date_start,
  280.             data_ultimo_aggiornamento,
  281.             giorno,
  282.             -- distanza,
  283.             periodo,
  284.             azienda,
  285.             area,
  286.             id_store,
  287.             istituto,
  288.             id_op,
  289.             operatore_cognome,
  290.             operatore_nome,
  291.             operatrice,
  292.             id_op_comm,
  293.             operatore_cognome_comm,
  294.             operatore_nome_comm,
  295.             operatrice_comm,
  296.             id_customer,
  297.             id_app,
  298.             commerciale_operativo,
  299.             commerciale,
  300.             visita,
  301.             categoria_trattamento,
  302.             ingressi_singolo,
  303.             incasso_singolo
  304. ),
  305. periodi as    (   SELECT 'oggi'    AS tempo, '   oggi'     AS periodi
  306.         UNION ALL SELECT 'ieri'    AS tempo, '   ieri'     AS periodi
  307.         UNION ALL SELECT 'oggi'    AS tempo, '  3 giorni'  AS periodi
  308.         UNION ALL SELECT 'ieri'    AS tempo, '  3 giorni'  AS periodi
  309.         UNION ALL SELECT '2-3'     AS tempo, '  3 giorni'  AS periodi
  310.         UNION ALL SELECT 'oggi'    AS tempo, '  7 giorni'  AS periodi
  311.         UNION ALL SELECT 'ieri'    AS tempo, '  7 giorni'  AS periodi
  312.         UNION ALL SELECT '2-3'     AS tempo, '  7 giorni'  AS periodi
  313.         UNION ALL SELECT '4-7'     AS tempo, '  7 giorni'  AS periodi
  314.         UNION ALL SELECT 'oggi'    AS tempo, ' 15 giorni'  AS periodi
  315.         UNION ALL SELECT 'ieri'    AS tempo, ' 15 giorni'  AS periodi
  316.         UNION ALL SELECT '2-3'     AS tempo, ' 15 giorni'  AS periodi
  317.         UNION ALL SELECT '4-7'     AS tempo, ' 15 giorni'  AS periodi
  318.         UNION ALL SELECT '8-15'    AS tempo, ' 15 giorni'  AS periodi
  319.         UNION ALL SELECT 'oggi'    AS tempo, ' 30 giorni'  AS periodi
  320.         UNION ALL SELECT 'ieri'    AS tempo, ' 30 giorni'  AS periodi
  321.         UNION ALL SELECT '2-3'     AS tempo, ' 30 giorni'  AS periodi
  322.         UNION ALL SELECT '4-7'     AS tempo, ' 30 giorni'  AS periodi
  323.         UNION ALL SELECT '8-15'    AS tempo, ' 30 giorni'  AS periodi
  324.         UNION ALL SELECT '16-30'   AS tempo, ' 30 giorni'  AS periodi
  325.         UNION ALL SELECT 'oggi'    AS tempo, ' 60 giorni'  AS periodi
  326.         UNION ALL SELECT 'ieri'    AS tempo, ' 60 giorni'  AS periodi
  327.         UNION ALL SELECT '2-3'     AS tempo, ' 60 giorni'  AS periodi
  328.         UNION ALL SELECT '4-7'     AS tempo, ' 60 giorni'  AS periodi
  329.         UNION ALL SELECT '8-15'    AS tempo, ' 60 giorni'  AS periodi
  330.         UNION ALL SELECT '16-30'   AS tempo, ' 60 giorni'  AS periodi
  331.         UNION ALL SELECT '31-60'   AS tempo, ' 60 giorni'  AS periodi
  332.         UNION ALL SELECT 'oggi'    AS tempo, ' 90 giorni'  AS periodi
  333.         UNION ALL SELECT 'ieri'    AS tempo, ' 90 giorni'  AS periodi
  334.         UNION ALL SELECT '2-3'     AS tempo, ' 90 giorni'  AS periodi
  335.         UNION ALL SELECT '4-7'     AS tempo, ' 90 giorni'  AS periodi
  336.         UNION ALL SELECT '8-15'    AS tempo, ' 90 giorni'  AS periodi
  337.         UNION ALL SELECT '16-30'   AS tempo, ' 90 giorni'  AS periodi
  338.         UNION ALL SELECT '31-60'   AS tempo, ' 90 giorni'  AS periodi
  339.         UNION ALL SELECT '61-90'   AS tempo, ' 90 giorni'  AS periodi
  340.         UNION ALL SELECT 'oggi'    AS tempo, '120 giorni'  AS periodi
  341.         UNION ALL SELECT 'ieri'    AS tempo, '120 giorni'  AS periodi
  342.         UNION ALL SELECT '2-3'     AS tempo, '120 giorni'  AS periodi
  343.         UNION ALL SELECT '4-7'     AS tempo, '120 giorni'  AS periodi
  344.         UNION ALL SELECT '8-15'    AS tempo, '120 giorni'  AS periodi
  345.         UNION ALL SELECT '16-30'   AS tempo, '120 giorni'  AS periodi
  346.         UNION ALL SELECT '31-60'   AS tempo, '120 giorni'  AS periodi
  347.         UNION ALL SELECT '61-90'   AS tempo, '120 giorni'  AS periodi
  348.         UNION ALL SELECT '91-120'  AS tempo, '120 giorni'  AS periodi
  349.         UNION ALL SELECT 'oggi'    AS tempo, '180 giorni'  AS periodi
  350.         UNION ALL SELECT 'ieri'    AS tempo, '180 giorni'  AS periodi
  351.         UNION ALL SELECT '2-3'     AS tempo, '180 giorni'  AS periodi
  352.         UNION ALL SELECT '4-7'     AS tempo, '180 giorni'  AS periodi
  353.         UNION ALL SELECT '8-15'    AS tempo, '180 giorni'  AS periodi
  354.         UNION ALL SELECT '16-30'   AS tempo, '180 giorni'  AS periodi
  355.         UNION ALL SELECT '31-60'   AS tempo, '180 giorni'  AS periodi
  356.         UNION ALL SELECT '61-90'   AS tempo, '180 giorni'  AS periodi
  357.         UNION ALL SELECT '91-120'  AS tempo, '180 giorni'  AS periodi
  358.         UNION ALL SELECT '121-180' AS tempo, '180 giorni'  AS periodi
  359.     ),
  360. commerciali_operativi as (
  361.     SELECT
  362.         data_ultimo_aggiornamento,
  363.         giorno,
  364.         extract('month' FROM giorno) AS periodo,
  365.         azienda,
  366.         periodi.periodi,
  367.         commerciale,
  368.         area,
  369.         id_store,
  370.         istituto,
  371.         id_op_comm as id_op,
  372.         operatore_cognome_comm as operatore_cognome,
  373.         operatore_nome_comm as operatore,
  374.         operatrice_comm as operatrice,
  375.         id_customer,
  376.         dati.visita,
  377.         categoria_trattamento,
  378.         'commerciali_operativi' as tipo,
  379.         distanza,
  380.         sum(commerciale_operativo) as commerciale_operativo,
  381.         count(disp) AS consulente,
  382.         coalesce(count(ingressi_singolo), 0) AS ingresso_singolo,
  383.         coalesce(sum(incasso_singolo), 0) AS incasso_singolo,
  384.         coalesce(sum(incasso_primo_giorno), 0) AS incasso_primo_giorno,
  385.         coalesce(sum(incasso_singolo),0) + coalesce(sum(incasso_primo_giorno),0) AS cassa_primo_giorno,
  386.         -- coalesce(sum(cassa_primo_giorno_individuale_f), 0) AS cassa_primo_giorno_f,
  387.         sum(clienti_primo_giorno_paganti) AS clienti_primo_giorno_paganti,
  388.         sum(clienti_primo_giorno_paganti_new) AS clienti_primo_giorno_paganti_new,
  389.         coalesce(sum(cassa_primo_giorno_paganti_tot_new),0) AS cassa_primo_giorno_paganti_tot_new,
  390.         sum(clienti_sette_giorni_paganti_new) AS clienti_sette_giorni_paganti_new,
  391.         coalesce(sum(cassa_sette_giorni_paganti_new),0) AS cassa_sette_giorni_paganti_new,
  392.         sum(clienti_trenta_giorni_paganti_new) AS clienti_trenta_giorni_paganti_new,
  393.         coalesce(sum(cassa_trenta_giorni_paganti_new),0) AS cassa_trenta_giorni_paganti_new
  394.     from dati
  395.     INNER join periodi
  396.     ON dati.periodo = periodi.tempo
  397.     GROUP BY
  398.         data_ultimo_aggiornamento,
  399.         giorno,
  400.         azienda,
  401.         area,
  402.         id_store,
  403.         istituto,
  404.         commerciale,
  405.         periodi.periodi,
  406.         tipo,
  407.         id_op_comm,
  408.         operatore_cognome_comm,
  409.         operatore_nome_comm,
  410.         operatrice_comm,
  411.         id_customer,
  412.         dati.visita,
  413.         categoria_trattamento,
  414.         dati.distanza
  415. ),
  416. performance_consulenti as (
  417.     SELECT
  418.         data_ultimo_aggiornamento,
  419.         giorno,
  420.         extract('month' FROM giorno) AS periodo,
  421.         azienda,
  422.         periodi.periodi,
  423.         commerciale,
  424.         area,
  425.         id_store,
  426.         istituto,
  427.         id_op,
  428.         operatore_cognome,
  429.         operatore_nome,
  430.         operatrice,
  431.         id_customer,
  432.         dati.visita,
  433.         categoria_trattamento,
  434.         'commerciali' as tipo,
  435.         dati.distanza,
  436.         sum(commerciale_operativo) as commerciale_operativo,
  437.         count(disp) AS consulente,
  438.         count(ingressi_singolo) AS ingresso_singolo,
  439.         coalesce(sum(incasso_singolo),0) AS incasso_singolo,
  440.         coalesce(sum(incasso_primo_giorno),0) AS incasso_primo_giorno,
  441.         coalesce(sum(incasso_singolo),0) + coalesce(sum(incasso_primo_giorno),0) AS cassa_primo_giorno,
  442.         -- coalesce(sum(incasso_singolo_f),0) + coalesce(sum(incasso_primo_giorno_f),0) AS cassa_primo_giorno_f,
  443.         sum(clienti_primo_giorno_paganti) AS clienti_primo_giorno_paganti,
  444.         sum(clienti_primo_giorno_paganti_new) AS clienti_primo_giorno_paganti_new,
  445.         coalesce(sum(cassa_primo_giorno_paganti_tot_new),0) AS cassa_primo_giorno_paganti_tot_new,
  446.         sum(clienti_sette_giorni_paganti_new) AS clienti_sette_giorni_paganti_new,
  447.         coalesce(sum(cassa_sette_giorni_paganti_new),0) AS cassa_sette_giorni_paganti_new,
  448.         sum(clienti_trenta_giorni_paganti_new) AS clienti_trenta_giorni_paganti_new,
  449.         coalesce(sum(cassa_trenta_giorni_paganti_new),0) AS cassa_trenta_giorni_paganti_new
  450.     from dati
  451.     INNER join periodi
  452.     ON dati.periodo = periodi.tempo
  453.     GROUP BY
  454.         data_ultimo_aggiornamento,
  455.         giorno,
  456.         azienda,
  457.         area,
  458.         id_store,
  459.         istituto,
  460.         commerciale,
  461.         periodi.periodi,
  462.         tipo,
  463.         id_op,
  464.         operatore_cognome,
  465.         operatore_nome,
  466.         operatrice,
  467.         id_customer,
  468.         dati.visita,
  469.         categoria_trattamento,
  470.         dati.distanza
  471. )
  472. -- Combining both queries with UNION ALL
  473. SELECT * FROM commerciali_operativi
  474. -- where giorno='2025-10-21' and periodo=10 and azienda='biolaser' and periodi='180 giorni' and commerciale not in ('operativo') and area='veneto' and istituto='verona' -- and id_op=143
  475. where azienda='biolaser' and periodi='180 giorni'
  476. UNION ALL
  477. SELECT * FROM performance_consulenti
  478. -- where giorno='2025-10-21' and periodo=10 and azienda='biolaser' and periodi='180 giorni' and commerciale not in ('operativo') and area='veneto' and istituto='verona' -- and id_op=143
  479. where azienda='biolaser' and periodi='180 giorni'
  480. )
  481. ;
Comments
  • cldscchttn
    cldscchttn
    346 days
    # text 0.13 KB | 0 0
    view report reply
    1. La query di creazione tabella è stata eseguita per salvare in locale i risultati. Per ottenere il risultato c'è voluta tutta la notte.
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!