CREATE OR REPLACE VIEW v_consumption_energy_delta
AS WITH delta AS (
         SELECT es.time_utc,
            es.addr,
            es.var,
            es.commodity,
            es.unit,
            es.value,
            es.meter,
            lag(es.value) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc) AS last_value,
            lag(es.time_utc) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc) AS last_timeutc,
            es.value - lag(es.value) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc) AS delta_value,
            min.consumption_min AS prumerna_spotreba,
            date_part('epoch'::text, es.time_utc - lag(es.time_utc) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc)) / 60::double precision AS minut_mereni
           FROM energetics.consumption_energy_consumption es
             JOIN energetics.v_consumption_energy_avg_consumption_min min ON es.addr::text = min.addr::text AND es.var::text = min.var::text AND es.meter::text = min.meter::text
          WHERE es.var::text = 'core'::text AND (es.addr::text ~~ '%F%'::text OR es.addr::text = '/2.1/VP2'::text) AND es.value > 0::numeric
        )
 SELECT delta.time_utc,
    delta.addr,
    delta.var,
    delta.commodity,
    delta.unit,
    delta.value,
    delta.meter,
    delta.last_value,
    delta.last_timeutc,
        CASE
            WHEN abs(delta.delta_value::double precision / 60::double precision) > abs(1000::double precision * delta.prumerna_spotreba) THEN 0::numeric
            ELSE delta.delta_value
        END AS delta_value,
    delta.minut_mereni
   FROM delta;

CREATE OR REPLACE VIEW v_consumption_energy_buildings_real
AS SELECT consumption_energy_buildings.id,
    consumption_energy_buildings.building_name,
    consumption_energy_buildings.description,
    consumption_energy_buildings.building_address_code,
    consumption_energy_buildings.building_label,
    consumption_energy_buildings.current_note,
    consumption_energy_buildings.main_use,
    consumption_energy_buildings.secondary_use,
    consumption_energy_buildings.year_of_construction,
    consumption_energy_buildings.method_of_protection,
    consumption_energy_buildings.built_up_area,
    consumption_energy_buildings.heated_bulding_volume,
    consumption_energy_buildings.students_count,
    consumption_energy_buildings.employees_count,
    consumption_energy_buildings.classrooms_count,
    consumption_energy_buildings.beds_count,
    consumption_energy_buildings.eno_id,
    consumption_energy_buildings.csu_code,
    consumption_energy_buildings.ku_code,
    consumption_energy_buildings.allotment_number,
    consumption_energy_buildings.registration_unit,
    consumption_energy_buildings.gas_consumption_normatives,
    consumption_energy_buildings.heat_consumption_normatives,
    consumption_energy_buildings.water_consumption_normatives,
    consumption_energy_buildings.electricity_consumption_normatives_per_person,
    consumption_energy_buildings.electricity_consumption_normatives,
    consumption_energy_buildings.energetic_management,
    consumption_energy_buildings.opening_hours,
    consumption_energy_buildings.weekend_opening_hours,
    consumption_energy_buildings.latitude,
    consumption_energy_buildings.longitude,
    consumption_energy_buildings.address_street,
    consumption_energy_buildings.address_house_number,
    consumption_energy_buildings.address_city,
    consumption_energy_buildings.address_country,
    consumption_energy_buildings.address_mail,
    consumption_energy_buildings.address_phone,
    consumption_energy_buildings.address_web_address,
    consumption_energy_buildings.penb_penbnumber,
    consumption_energy_buildings.penb_issue_date,
    consumption_energy_buildings.penb_total_building_envelope_area,
    consumption_energy_buildings.penb_volume_factor_of_avshape,
    consumption_energy_buildings.penb_total_energy_reference_area,
    consumption_energy_buildings.penb_total_provided_energy,
    consumption_energy_buildings.penb_total_provided_energy_category,
    consumption_energy_buildings.penb_primary_non_renewable_energy,
    consumption_energy_buildings.penb_primary_non_renewable_energy_category,
    consumption_energy_buildings.penb_building_envelope,
    consumption_energy_buildings.penb_building_envelope_category,
    consumption_energy_buildings.penb_heating,
    consumption_energy_buildings.penb_heating_category,
    consumption_energy_buildings.penb_cooling,
    consumption_energy_buildings.penb_cooling_category,
    consumption_energy_buildings.penb_ventilation,
    consumption_energy_buildings.penb_ventilation_category,
    consumption_energy_buildings.penb_humidity_adjustment,
    consumption_energy_buildings.penb_humidity_adjustment_category,
    consumption_energy_buildings.penb_warm_water,
    consumption_energy_buildings.penb_warm_water_category,
    consumption_energy_buildings.penb_lighting,
    consumption_energy_buildings.penb_lighting_category,
    consumption_energy_buildings.energy_audits_energy_audit,
    consumption_energy_buildings.energy_audits_earegistration_number,
    consumption_energy_buildings.energy_audits_created_at,
    consumption_energy_buildings.building_envelope_side_wall_prevailing_construction,
    consumption_energy_buildings.building_envelope_side_wall_area,
    consumption_energy_buildings.building_envelope_side_wall_heat_insulation,
    consumption_energy_buildings.building_envelope_side_wall_year_of_adjustment,
    consumption_energy_buildings.building_envelope_side_wall_technical_condition,
    consumption_energy_buildings.building_envelope_filling_of_hole_construction,
    consumption_energy_buildings.building_envelope_filling_of_hole_area,
    consumption_energy_buildings.building_envelope_filling_of_hole_year_of_adjustment,
    consumption_energy_buildings.building_envelope_filling_of_hole_technical_condition,
    consumption_energy_buildings.building_envelope_roof_construction,
    consumption_energy_buildings.building_envelope_roof_area,
    consumption_energy_buildings.building_envelope_roof_thermal_insulation,
    consumption_energy_buildings.building_envelope_roof_year_of_adjustment,
    consumption_energy_buildings.building_envelope_roof_technical_condition,
    consumption_energy_buildings.building_envelope_floor_of_the_lowest_heated_floor_construction,
    consumption_energy_buildings.building_envelope_floor_of_the_lowest_heated_floor_area,
    consumption_energy_buildings.floor_of_the_lowest_heated_floor_thermal_insulation,
    consumption_energy_buildings.building_envelope_floor_of_the_lowest_heated_floor_year_of_adju,
    consumption_energy_buildings.floor_of_the_lowest_heated_floor_technical_condition,
    consumption_energy_buildings.fuel_and_energy_coal,
    consumption_energy_buildings.fuel_and_energy_gas,
    consumption_energy_buildings.fuel_and_energy_electricity,
    consumption_energy_buildings.fuel_and_energy_czt,
    consumption_energy_buildings.fuel_and_energy_oze,
    consumption_energy_buildings.fuel_and_energy_other,
    consumption_energy_buildings.technical_equipment_heating_main_source_of_heat,
    consumption_energy_buildings.technical_equipment_heating_heat_percentage,
    consumption_energy_buildings.technical_equipment_heating_secondary_source_of_heat,
    consumption_energy_buildings.technical_equipment_heating_heating_system,
    consumption_energy_buildings.technical_equipment_heating_year,
    consumption_energy_buildings.technical_equipment_heating_technical_condition,
    consumption_energy_buildings.technical_equipment_cooling_cooling_system,
    consumption_energy_buildings.technical_equipment_cooling_cooling_area_percentage,
    consumption_energy_buildings.technical_equipment_cooling_year,
    consumption_energy_buildings.technical_equipment_cooling_technical_condition,
    consumption_energy_buildings.technical_equipment_ventilation_ventilation,
    consumption_energy_buildings.technical_equipment_ventilation_year,
    consumption_energy_buildings.technical_equipment_ventilation_technical_condition,
    consumption_energy_buildings.technical_equipment_humidity_adjustment_humidity_adjustment,
    consumption_energy_buildings.technical_equipment_humidity_adjustment_year,
    consumption_energy_buildings.technical_equipment_humidity_adjustment_technical_condition,
    consumption_energy_buildings.technical_equipment_hot_water_predominant_way_of_heating_tv,
    consumption_energy_buildings.technical_equipment_hot_water_hot_water_source,
    consumption_energy_buildings.technical_equipment_hot_water_year,
    consumption_energy_buildings.technical_equipment_hot_water_technical_condition,
    consumption_energy_buildings.technical_equipment_lighting_lighting,
    consumption_energy_buildings.technical_equipment_lighting_year,
    consumption_energy_buildings.technical_equipment_lighting_technical_condition,
    consumption_energy_buildings.technical_equipment_lighting_other_technological_elements,
    consumption_energy_buildings.technical_equipment_lighting_measurement_method,
    consumption_energy_buildings.oze_energy_production_solar_energy_photovoltaic,
    consumption_energy_buildings.oze_energy_production_solar_energy_photothermal,
    consumption_energy_buildings.oze_energy_production_integrated_turbines_wind_energy,
    consumption_energy_buildings.oze_energy_production_heat_pump,
    consumption_energy_buildings.waste_and_emissions_solid_waste_production,
    consumption_energy_buildings.waste_and_emissions_tied_co2_emissions,
    consumption_energy_buildings.waste_and_emissions_sox_emissions,
    consumption_energy_buildings.waste_and_emissions_operating_co_2emissions,
    consumption_energy_buildings.link
   FROM energetics.consumption_energy_buildings
  WHERE consumption_energy_buildings.building_label::text <> ALL (ARRAY['ZKB 1'::character varying::text, 'ZKB 2'::character varying::text, 'TB1'::character varying::text, 'TB2'::character varying::text]);

CREATE OR REPLACE VIEW v_consumption_energy_buildings_w_data
AS SELECT consumption_energy_buildings.id,
    consumption_energy_buildings.building_name,
    consumption_energy_buildings.description,
    consumption_energy_buildings.building_address_code,
    consumption_energy_buildings.building_label,
    consumption_energy_buildings.current_note,
    consumption_energy_buildings.main_use,
    consumption_energy_buildings.secondary_use,
    consumption_energy_buildings.year_of_construction,
    consumption_energy_buildings.method_of_protection,
    consumption_energy_buildings.built_up_area,
    consumption_energy_buildings.heated_bulding_volume,
    consumption_energy_buildings.students_count,
    consumption_energy_buildings.employees_count,
    consumption_energy_buildings.classrooms_count,
    consumption_energy_buildings.beds_count,
    consumption_energy_buildings.eno_id,
    consumption_energy_buildings.csu_code,
    consumption_energy_buildings.ku_code,
    consumption_energy_buildings.allotment_number,
    consumption_energy_buildings.registration_unit,
    consumption_energy_buildings.gas_consumption_normatives,
    consumption_energy_buildings.heat_consumption_normatives,
    consumption_energy_buildings.water_consumption_normatives,
    consumption_energy_buildings.electricity_consumption_normatives_per_person,
    consumption_energy_buildings.electricity_consumption_normatives,
    consumption_energy_buildings.energetic_management,
    consumption_energy_buildings.opening_hours,
    consumption_energy_buildings.weekend_opening_hours,
    consumption_energy_buildings.latitude,
    consumption_energy_buildings.longitude,
    consumption_energy_buildings.address_street,
    consumption_energy_buildings.address_house_number,
    consumption_energy_buildings.address_city,
    consumption_energy_buildings.address_country,
    consumption_energy_buildings.address_mail,
    consumption_energy_buildings.address_phone,
    consumption_energy_buildings.address_web_address,
    consumption_energy_buildings.penb_penbnumber,
    consumption_energy_buildings.penb_issue_date,
    consumption_energy_buildings.penb_total_building_envelope_area,
    consumption_energy_buildings.penb_volume_factor_of_avshape,
    consumption_energy_buildings.penb_total_energy_reference_area,
    consumption_energy_buildings.penb_total_provided_energy,
    consumption_energy_buildings.penb_total_provided_energy_category,
    consumption_energy_buildings.penb_primary_non_renewable_energy,
    consumption_energy_buildings.penb_primary_non_renewable_energy_category,
    consumption_energy_buildings.penb_building_envelope,
    consumption_energy_buildings.penb_building_envelope_category,
    consumption_energy_buildings.penb_heating,
    consumption_energy_buildings.penb_heating_category,
    consumption_energy_buildings.penb_cooling,
    consumption_energy_buildings.penb_cooling_category,
    consumption_energy_buildings.penb_ventilation,
    consumption_energy_buildings.penb_ventilation_category,
    consumption_energy_buildings.penb_humidity_adjustment,
    consumption_energy_buildings.penb_humidity_adjustment_category,
    consumption_energy_buildings.penb_warm_water,
    consumption_energy_buildings.penb_warm_water_category,
    consumption_energy_buildings.penb_lighting,
    consumption_energy_buildings.penb_lighting_category,
    consumption_energy_buildings.energy_audits_energy_audit,
    consumption_energy_buildings.energy_audits_earegistration_number,
    consumption_energy_buildings.energy_audits_created_at,
    consumption_energy_buildings.building_envelope_side_wall_prevailing_construction,
    consumption_energy_buildings.building_envelope_side_wall_area,
    consumption_energy_buildings.building_envelope_side_wall_heat_insulation,
    consumption_energy_buildings.building_envelope_side_wall_year_of_adjustment,
    consumption_energy_buildings.building_envelope_side_wall_technical_condition,
    consumption_energy_buildings.building_envelope_filling_of_hole_construction,
    consumption_energy_buildings.building_envelope_filling_of_hole_area,
    consumption_energy_buildings.building_envelope_filling_of_hole_year_of_adjustment,
    consumption_energy_buildings.building_envelope_filling_of_hole_technical_condition,
    consumption_energy_buildings.building_envelope_roof_construction,
    consumption_energy_buildings.building_envelope_roof_area,
    consumption_energy_buildings.building_envelope_roof_thermal_insulation,
    consumption_energy_buildings.building_envelope_roof_year_of_adjustment,
    consumption_energy_buildings.building_envelope_roof_technical_condition,
    consumption_energy_buildings.building_envelope_floor_of_the_lowest_heated_floor_construction,
    consumption_energy_buildings.building_envelope_floor_of_the_lowest_heated_floor_area,
    consumption_energy_buildings.floor_of_the_lowest_heated_floor_thermal_insulation,
    consumption_energy_buildings.building_envelope_floor_of_the_lowest_heated_floor_year_of_adju,
    consumption_energy_buildings.floor_of_the_lowest_heated_floor_technical_condition,
    consumption_energy_buildings.fuel_and_energy_coal,
    consumption_energy_buildings.fuel_and_energy_gas,
    consumption_energy_buildings.fuel_and_energy_electricity,
    consumption_energy_buildings.fuel_and_energy_czt,
    consumption_energy_buildings.fuel_and_energy_oze,
    consumption_energy_buildings.fuel_and_energy_other,
    consumption_energy_buildings.technical_equipment_heating_main_source_of_heat,
    consumption_energy_buildings.technical_equipment_heating_heat_percentage,
    consumption_energy_buildings.technical_equipment_heating_secondary_source_of_heat,
    consumption_energy_buildings.technical_equipment_heating_heating_system,
    consumption_energy_buildings.technical_equipment_heating_year,
    consumption_energy_buildings.technical_equipment_heating_technical_condition,
    consumption_energy_buildings.technical_equipment_cooling_cooling_system,
    consumption_energy_buildings.technical_equipment_cooling_cooling_area_percentage,
    consumption_energy_buildings.technical_equipment_cooling_year,
    consumption_energy_buildings.technical_equipment_cooling_technical_condition,
    consumption_energy_buildings.technical_equipment_ventilation_ventilation,
    consumption_energy_buildings.technical_equipment_ventilation_year,
    consumption_energy_buildings.technical_equipment_ventilation_technical_condition,
    consumption_energy_buildings.technical_equipment_humidity_adjustment_humidity_adjustment,
    consumption_energy_buildings.technical_equipment_humidity_adjustment_year,
    consumption_energy_buildings.technical_equipment_humidity_adjustment_technical_condition,
    consumption_energy_buildings.technical_equipment_hot_water_predominant_way_of_heating_tv,
    consumption_energy_buildings.technical_equipment_hot_water_hot_water_source,
    consumption_energy_buildings.technical_equipment_hot_water_year,
    consumption_energy_buildings.technical_equipment_hot_water_technical_condition,
    consumption_energy_buildings.technical_equipment_lighting_lighting,
    consumption_energy_buildings.technical_equipment_lighting_year,
    consumption_energy_buildings.technical_equipment_lighting_technical_condition,
    consumption_energy_buildings.technical_equipment_lighting_other_technological_elements,
    consumption_energy_buildings.technical_equipment_lighting_measurement_method,
    consumption_energy_buildings.oze_energy_production_solar_energy_photovoltaic,
    consumption_energy_buildings.oze_energy_production_solar_energy_photothermal,
    consumption_energy_buildings.oze_energy_production_integrated_turbines_wind_energy,
    consumption_energy_buildings.oze_energy_production_heat_pump,
    consumption_energy_buildings.waste_and_emissions_solid_waste_production,
    consumption_energy_buildings.waste_and_emissions_tied_co2_emissions,
    consumption_energy_buildings.waste_and_emissions_sox_emissions,
    consumption_energy_buildings.waste_and_emissions_operating_co_2emissions,
    consumption_energy_buildings.link
   FROM energetics.consumption_energy_buildings
  WHERE (btrim(consumption_energy_buildings.building_address_code::text) IN ( SELECT DISTINCT v_consumption_energy_consumption.building_address_code
           FROM energetics.v_consumption_energy_consumption)) AND consumption_energy_buildings.building_address_code::text !~~ '10.%'::text;

CREATE OR REPLACE VIEW v_consumption_energy_consumption_month_el_f
AS SELECT date_part('year'::text, consumption_energy_consumption.time_utc) AS rok,
    date_part('month'::text, consumption_energy_consumption.time_utc) AS mesic,
    consumption_energy_consumption.addr,
    consumption_energy_consumption.var,
    consumption_energy_consumption.commodity,
    consumption_energy_consumption.unit,
    sum(consumption_energy_consumption.value::numeric(10,2)) / 4::numeric AS value,
    max(consumption_energy_consumption.time_utc::date) AS data_do,
    count(*) AS count
   FROM energetics.consumption_energy_consumption
  WHERE (consumption_energy_consumption.var::text = ANY (ARRAY['EFwActi'::character varying::text, 'EFwActiNT'::character varying::text, 'EFwActi'::character varying::text])) AND consumption_energy_consumption.addr::text ~~ '%/EF%'::text
  GROUP BY (date_part('year'::text, consumption_energy_consumption.time_utc)), (date_part('month'::text, consumption_energy_consumption.time_utc)), consumption_energy_consumption.addr, consumption_energy_consumption.var, consumption_energy_consumption.commodity, consumption_energy_consumption.unit
UNION
 SELECT v_consumption_vrtbovsky_palac.measure_year AS rok,
    v_consumption_vrtbovsky_palac.measure_month AS mesic,
    v_consumption_vrtbovsky_palac.addr::character varying(255) AS addr,
    v_consumption_vrtbovsky_palac.var::character varying(255) AS var,
    v_consumption_vrtbovsky_palac.commodity::character varying(255) AS commodity,
    v_consumption_vrtbovsky_palac.unit::character varying(255) AS unit,
    sum(v_consumption_vrtbovsky_palac.consumption) AS value,
    v_consumption_vrtbovsky_palac.data_do::date AS data_do,
    count(*) AS count
   FROM energetics.v_consumption_vrtbovsky_palac
  WHERE v_consumption_vrtbovsky_palac.commodity = 'electricity'::text
  GROUP BY v_consumption_vrtbovsky_palac.measure_year, v_consumption_vrtbovsky_palac.measure_month, v_consumption_vrtbovsky_palac.data_do, v_consumption_vrtbovsky_palac.addr, v_consumption_vrtbovsky_palac.var, v_consumption_vrtbovsky_palac.commodity, v_consumption_vrtbovsky_palac.unit;

CREATE OR REPLACE VIEW v_consumption_gas_reserve
AS WITH kalendar AS (
         SELECT den.den::date AS den
           FROM generate_series('2019-01-01 00:00:00+01'::timestamp with time zone, now() - '1 day'::interval, '1 day'::interval) den(den)
        ), meraky AS (
         SELECT DISTINCT consumption_energy_devices.addr
           FROM energetics.consumption_energy_devices
          WHERE consumption_energy_devices.meter_type::text = 'Plynoměr'::text
        ), daily_delta_core AS (
         SELECT date_trunc('day'::text, v_consumption_energy_delta.time_utc - '06:00:00'::interval)::date AS measured_from,
            v_consumption_energy_delta.addr,
            v_consumption_energy_delta.var,
            sum(v_consumption_energy_delta.delta_value) AS delta_value
           FROM energetics.v_consumption_energy_delta
          WHERE v_consumption_energy_delta.commodity::text = 'gas'::text AND date_part('year'::text, v_consumption_energy_delta.time_utc - '06:00:00'::interval) >= 2019::double precision
          GROUP BY (date_trunc('day'::text, v_consumption_energy_delta.time_utc - '06:00:00'::interval)::date), v_consumption_energy_delta.addr, v_consumption_energy_delta.var
        ), daily_delta_core2 AS (
         SELECT date_trunc('day'::text, consumption_energy_consumption.time_utc - '06:00:00'::interval)::date AS measured_from,
            consumption_energy_consumption.addr,
            consumption_energy_consumption.var,
            sum(consumption_energy_consumption.value) AS delta_value
           FROM energetics.consumption_energy_consumption
          WHERE consumption_energy_consumption.var::text = 'core2'::text AND consumption_energy_consumption.time_utc::date > '2018-12-31'::date
          GROUP BY (date_trunc('day'::text, consumption_energy_consumption.time_utc - '06:00:00'::interval)::date), consumption_energy_consumption.addr, consumption_energy_consumption.var
        ), delta_dates AS (
         SELECT daily_delta_core.measured_from,
            daily_delta_core.addr,
            daily_delta_core.var,
            daily_delta_core.delta_value,
            lag(daily_delta_core.measured_from, 1) OVER (PARTITION BY daily_delta_core.addr ORDER BY daily_delta_core.measured_from) AS measured_before,
            lead(daily_delta_core.measured_from, 1) OVER (PARTITION BY daily_delta_core.addr ORDER BY daily_delta_core.measured_from) AS measured_after,
            lag(daily_delta_core.delta_value, 1) OVER (PARTITION BY daily_delta_core.addr ORDER BY daily_delta_core.measured_from) AS delta_before,
            lead(daily_delta_core.delta_value, 1) OVER (PARTITION BY daily_delta_core.addr ORDER BY daily_delta_core.measured_from) AS delta_after
           FROM daily_delta_core
        ), delta_date_diff AS (
         SELECT delta_dates.measured_from,
            delta_dates.addr,
            delta_dates.var,
            delta_dates.delta_value,
            delta_dates.measured_before,
            delta_dates.measured_after,
            delta_dates.delta_before,
            delta_dates.delta_after,
                CASE
                    WHEN (delta_dates.measured_after - delta_dates.measured_from) > 1 AND (delta_dates.measured_from - delta_dates.measured_before) > 1 THEN 'mid'::text
                    WHEN (delta_dates.measured_after - delta_dates.measured_from) > 1 THEN 'begin'::text
                    WHEN (delta_dates.measured_from - delta_dates.measured_before) > 1 THEN 'end'::text
                    ELSE 'ok'::text
                END AS value_type,
                CASE
                    WHEN (delta_dates.measured_after - delta_dates.measured_from) > 1 AND (delta_dates.measured_from - delta_dates.measured_before) > 1 THEN (delta_dates.delta_value / 2::numeric + delta_dates.delta_after) / (delta_dates.measured_after - delta_dates.measured_from + 1)::numeric
                    WHEN (delta_dates.measured_after - delta_dates.measured_from) > 1 THEN (delta_dates.delta_value + delta_dates.delta_after) / (delta_dates.measured_after - delta_dates.measured_from + 1)::numeric
                    ELSE NULL::numeric
                END AS fix
           FROM delta_dates
        ), joined_table AS (
         SELECT kalendar.den AS measured_from,
            meraky.addr,
            diff.delta_value,
            diff.value_type,
            diff.fix,
            core2.delta_value AS core2_delta,
                CASE
                    WHEN diff.value_type = 'ok'::text THEN COALESCE(core2.delta_value, diff.delta_value)
                    ELSE diff.fix
                END AS final_fix,
                CASE
                    WHEN diff.value_type = 'ok'::text THEN COALESCE(core2.var, diff.var)
                    ELSE 'core'::character varying
                END AS final_var
           FROM kalendar
             LEFT JOIN meraky ON true
             LEFT JOIN delta_date_diff diff ON diff.measured_from = kalendar.den AND diff.addr::text = meraky.addr::text
             LEFT JOIN daily_delta_core2 core2 ON core2.measured_from = kalendar.den AND core2.addr::text = meraky.addr::text
          ORDER BY meraky.addr, kalendar.den
        ), fixed_table AS (
         SELECT q.measured_from,
            q.addr,
            q.delta_value,
            q.value_type,
            q.fix,
            q.core2_delta,
            q.final_fix,
            q.final_var,
            q.value_partition,
            first_value(q.final_fix) OVER w AS value
           FROM ( SELECT joined_table.measured_from,
                    joined_table.addr,
                    joined_table.delta_value,
                    joined_table.value_type,
                    joined_table.fix,
                    joined_table.core2_delta,
                    joined_table.final_fix,
                    joined_table.final_var,
                    sum(
                        CASE
                            WHEN joined_table.final_fix IS NULL THEN 0
                            ELSE 1
                        END) OVER (PARTITION BY joined_table.addr ORDER BY joined_table.measured_from) AS value_partition
                   FROM joined_table) q
          WINDOW w AS (PARTITION BY q.addr, q.value_partition ORDER BY q.measured_from)
        )
 SELECT fixed_table.measured_from,
    fixed_table.addr,
    fixed_table.value AS delta_value,
    fixed_table.final_var,
    row_number() OVER (PARTITION BY fixed_table.addr, (date_trunc('month'::text, fixed_table.measured_from::timestamp with time zone)) ORDER BY fixed_table.value DESC) AS ran
   FROM fixed_table
  WHERE fixed_table.value IS NOT NULL;

CREATE OR REPLACE VIEW v_consumption_energy_devices_active
AS SELECT consumption_energy_devices.id,
    consumption_energy_devices.addr,
    consumption_energy_devices.description,
    consumption_energy_devices.meter_number,
    consumption_energy_devices.meter_index,
    consumption_energy_devices.location_number,
    consumption_energy_devices.location_description,
    consumption_energy_devices.include_in_evaluation,
    consumption_energy_devices.meter_type,
    consumption_energy_devices.category,
    consumption_energy_devices.unit,
    consumption_energy_devices.replaced_meter_id,
    consumption_energy_devices.deleted,
    consumption_energy_devices.building_id
   FROM energetics.consumption_energy_devices
  WHERE NOT (consumption_energy_devices.id IN ( SELECT consumption_energy_devices_1.replaced_meter_id::integer AS replaced_meter_id
           FROM energetics.consumption_energy_devices consumption_energy_devices_1
          WHERE consumption_energy_devices_1.replaced_meter_id::text <> ''::text)) AND (consumption_energy_devices.addr::text <> ALL (ARRAY['aaa'::character varying::text, 'VF1'::character varying::text, 'TF1'::character varying::text, 'EF1'::character varying::text, 'PF1'::character varying::text, '01/VF1'::character varying::text, 'Návštěvnost'::character varying::text])) AND consumption_energy_devices.deleted::text <> '1'::text
UNION
 SELECT me.me_id AS id,
    me.me_id::character varying(255) AS addr,
    NULL::character varying(255) AS description,
    NULL::character varying(255) AS meter_number,
    NULL::character varying(255) AS meter_index,
    NULL::character varying(255) AS location_number,
    NULL::character varying(255) AS location_description,
    NULL::character varying(255) AS include_in_evaluation,
    NULL::character varying(255) AS meter_type,
    NULL::character varying(255) AS category,
    NULL::character varying(255) AS unit,
    NULL::character varying(255) AS replaced_meter_id,
    NULL::character varying(255) AS deleted,
    111 AS building_id
   FROM energetics.vpalac_measuring_equipment me;
