rds_telemonitoring_alarm:
LOAD id as alarm_id, 
	source_flow, 
	source_technology, 
    date(FLOOR(datetime_edp_preprocessing)) AS Date, 
	datetime_edp_preprocessing, 
	datetime_edp_processing, 
	datetime_start, 
	datetime_close,
    Interval(datetime_close - datetime_edp_processing, 'hh:mm:ss') as minutes_start_to_close,
    interval(datetime_close - datetime_edp_preprocessing, 'hh:mm:ss') as minutes_recepcion_to_close,
	account_number, 
	original_message, 
	alarm_text, 
    state as state_id,     
	original_priority, 
	original_store, 
	alarm_type_id , 
	asset, 
	serial_number, 
	original_equipment, 
	system_id, 
	date(datetime_end) AS datetime_end, 
	sf_ticket_number, 
	position, 
	original_technology, 
    1 as Contador,
    alarm_note, //NEW
    process_note, //NEW
    agent_in_charge, //NEW
    agent_closed, //NEW
	suspension_end_datetime, //NEW
	last_modification_date, //NEW
    closure_reason, //NEW2
	last_modification , //NEW2, //NEW2,
    IF(date(FLOOR(datetime_edp_preprocessing)) >= Today() - 30, 'Lower 30 days', 'Greater 30 days') as has_recent_alarm_30_days //NEW2 
    
FROM [lib://QVD.01.Stage/FSM/tlm_historic_alarm.qvd]
(qvd);

rds_telemonitoring_alarm_final:
NOCONCATENATE
LOAD 
	alarm_id,
	source_flow, 
	source_technology,
    Date,
    datetime_edp_preprocessing, 
	datetime_edp_processing, 
	datetime_start, 
	datetime_close,
    minutes_start_to_close,
    minutes_recepcion_to_close,
	account_number,
//     account_number, // as account_number_tlm_info,
//     account_number as account_number_Test_di_vita ,
	original_message, 
	alarm_text,    
    state_id,
	original_priority, 
	original_store, 
	alarm_type_id , 
	asset, 
	serial_number, 
	original_equipment, 
	system_id,     
    datetime_end,
    sf_ticket_number, 
	position, 
	original_technology, 
    Contador,
    alarm_note, //NEW
    process_note, //NEW
    agent_in_charge, //NEW
    agent_closed, //NEW
	suspension_end_datetime, //NEW
	last_modification_date, //NEW
    closure_reason, //NEW2    
    last_modification, //NEW2
    If(len(Status)>0, Status, 'Unknown') as Status,    
// 	description_alarm_type , 	 
// 	language_alarm_type, 
// 	suggestions_alarm_type,
	type_severity,
	account_id,
    account_id as relatedrecordid, //key
    account_id as account_id_cat,  //key
	retailer_description__c,
    service_territory_id,
    shippingcountry,
    banner_description__c,
    billingcity,    
    
	If(account_number = '1000000', 'Germany', 
	    If(Len(billingcountry) > 0, billingcountry, 'Unknown')
	) as billingcountry,

	If(account_number = '1000000', 'Germany', 
	    If(Len(billingcountry) > 0, billingcountry, 'Unknown')
    ) as REDUCTION,    
    
    location_iso,
    store_management__c,   
    If(len(name)>0, name, 'Unknown') as name,      
    billingstreet,
    mycountry__c,
	service_territory_zipcode, 
	service_territory_city, 
	fsl_assigneddepartments__c, 
	service_territory_country, 
	service_territory_countrycode, 
	isactive, 
	service_territory_street, 
	service_territory_name, 
	service_territory_state,
    maintenancecontractactiveflag__c,
    telemonitoring_contract_active_flag__c,
    tls_contract_end_date__c,
    If(Status = 'INACTIVE' and len(datetime_end)>0 , 'true', 'false') as "Is Okay Message",
    IF ( Date < Today() - 6,'False', 'True') AS "Alarms in the last 7 days",
    has_recent_alarm_30_days,
    shift
RESIDENT rds_telemonitoring_alarm;
DROP TABLE rds_telemonitoring_alarm;

accounts_active_with_alarms_last_30_days:
LOAD 
    account_number,                            // Identificador único de la cuenta
//     Count(alarm_id) as num_alarms,
    IF( (Max(Date) >= Today() - 30), 'Lower 30 days', 'Greater 30 days') as has_recent_alarms
RESIDENT rds_telemonitoring_alarm_final             // Fuente de datos: tabla cargada con las alarmas
WHERE 
      // Alarmas ocurridas en los últimos 30 días
    telemonitoring_contract_active_flag__c = 1  // Cuenta con contrato TLS activo
    AND (tls_contract_end_date__c > Today() OR IsNull(tls_contract_end_date__c))  // Contrato sin fecha de fin o aún vigente
GROUP BY 
     account_number;