Hey Sunny !
i.e
I had a meter that was broken for a week, then it was fixed and start to send me measure again, so it will populate the DB with measure of today and the last 7 day, but if i load only date=today, i'll lose data of the last 7 days.
Check this out (little big code), i want to load only data that had some modified. (I dont know with QS has some way to check the last modification)
CONSUMO_CONDOMINIOS:
LOAD
ADDRESS_BLOCK_UC,
ADDRESS_COMPLEMENT_UC,
NAME_UC,
PENDENCY_COUNT,
DESCRIPTION_METER,
SERIAL_NUMBER_METER,
OID_METER,
ID_SYSTEM as OID_SYSTEM,
TYPE_METER,
GROUP_TYPE_METER,
TYPE_UNIT,
EXTERNAL_KEY_UC,
DATEINI,
DATEFIM,
[DATEFIM]-[DATEINI] AS PERIODO,
VALUE_READ_INI,
VALUE_READ_FIM,
CORRINI,
CORRFIM,
LEITURAINI,
LEITURAFIM,
LEITCORRINI,
LEITCORRFIM,
CONSUMO;
SQL SELECT
si.address_block_uc address_block_uc
,si.address_complement_uc address_complement_uc
,si.name_uc name_uc
,pendency_count
,si.description_meter description_meter
,si.serial_number_meter serial_number_meter
,si.oid_meter oid_meter
,si.id_system id_system
,si.name_type_meter type_meter
,si.group_type_meter group_type_meter
,si.type_unit type_unit
,si.external_key_uc
,date_read_ini dateini
,date_read_fim datefim
,value_read_ini value_read_ini
,value_read_fim value_read_fim
,corr_factor_ini corrini
,corr_factor_fim corrfim
,leitura_ini leituraini
,leitura_fim leiturafim
,leitura_ini + corr_factor_ini leitcorrini
,leitura_fim + corr_factor_fim leitcorrfim
,(leitura_fim + corr_factor_fim) - (leitura_ini + corr_factor_ini) consumo
from (select u.address_block_uc, u.address_complement_uc, u.name_uc, u.pendency_count
,u.external_key_uc, m.description_meter, m.serial_number_meter
,tm.name_type_meter,tm.group_type_meter,m.type_unit
,m.oid_meter, lst.id_system
,lst.min_date_read date_read_ini
,ini.value_read value_read_ini
,lst.max_date_read date_read_fim
,fim.value_read value_read_fim
,get_correction_factor( lst.id_system,m.oid_meter,m.oid_uc,lst.min_date_read ) + (get_correction_pulse(lst.id_system,cini.concentrator_counters,cini.port_counters,lst.min_date_read)*m.type_constant) corr_factor_ini
,nvl(f.first_measur,0) + ( ini.value_read * m.type_constant ) leitura_ini
,get_correction_factor( lst.id_system,m.oid_meter,m.oid_uc,lst.max_date_read ) + (get_correction_pulse(lst.id_system,cfim.concentrator_counters,cfim.port_counters,lst.max_date_read)*m.type_constant) corr_factor_fim
,nvl(f.first_measur,0) + ( fim.value_read * m.type_constant ) leitura_fim
from (select id_system, m.oid_meter,
min(date_read) min_date_read, max(date_read) max_date_read
from rs_individual_read i, rs_uc u, rs_meter m, rs_counters c
where m.oid_meter = c.oid_meter
and c.concentrator_counters = i.concentrator
and c.port_counters = i.port
and date_read between c.dt_init and c.dt_end
and date_read between m.dt_init and m.dt_end
and u.oid_uc=m.oid_uc
and u.oid_system=i.id_system
// and u.oid_system= 581
and i.date_read between TO_DATE(SYSDATE-3)
and TO_DATE(SYSDATE+1)
-- and m.individual_flag=?
group by id_system, m.oid_meter
) lst
inner join rs_meter m
on (m.oid_meter = lst.oid_meter
and m.oid_meter = lst.oid_meter
and ( lst.min_date_read between m.dt_init and m.dt_end
or lst.max_date_read between m.dt_init and m.dt_end
)
)
inner join rs_counters cini
on (cini.oid_meter=m.oid_meter
and ( lst.min_date_read between cini.dt_init and cini.dt_end )
)
inner join rs_counters cfim
on (cfim.oid_meter=m.oid_meter
and ( lst.max_date_read between cfim.dt_init and cfim.dt_end )
)
inner join rs_individual_read ini
on (ini.id_system = lst.id_system
and ini.date_read = lst.min_date_read
and ini.concentrator = cini.concentrator_counters
and ini.port = cini.port_counters)
inner join rs_individual_read fim
on (fim.id_system = lst.id_system
and fim.date_read = lst.max_date_read
and fim.concentrator = cfim.concentrator_counters
and fim.port = cfim.port_counters)
inner join rs_uc u
on (u.oid_system = lst.id_system
and u.oid_uc = m.oid_uc)
inner join rs_type_meter tm
on (m.oid_type_meter = tm.oid_type_meter)
left outer join rs_first_measurs f
on (f.oid_meter = m.oid_meter)
) si order by address_block_uc,address_complement_uc,name_uc,group_type_meter,type_meter,serial_number_meter;
STORE CONSUMO_CONDOMINIOS INTO [lib://MI_csv/CONSUMO_CONDOMINIOS.qvd];
