1 2 3 4 5 6 7 8 9 10 11 12 13 14
| Query_Red.Sql.Clear;
Query_Red.Sql.Add('select vps.meter.msno,vps.meter.meter_status_id status,inst_date date_installation,substr(vps.location.location_ref,1,20) numero_abonne');
Query_Red.Sql.Add(',substr(vps.area.area,1,3) section,vps.mrl.maxlimit profil_tarifaire, count(distinct tkn) nb_achat, sum(nvl(UNITS,0)) kWH,max(ts) dernier_achat');
Query_Red.Sql.Add(',floor(months_between(to_date(sysdate,''dd/mm/yyyy''),to_date(max(ts),''dd/mm/yyyy''))) nb_redevance');
Query_Red.Sql.Add('from vps.meter,vps.location,vps.trn,vps.mrl,vps.area');
Query_Red.Sql.Add('where vps.meter.location_id=vps.location.location_id');
Query_Red.Sql.Add('and vps.meter.msno=vps.trn.msno');
Query_Red.Sql.Add('and vps.meter.mrl_id=vps.mrl.mrl_id');
Query_Red.Sql.Add('and vps.area.area_id=vps.location.area_id');
Query_Red.Sql.Add('and upper(tkn) != ' + QuotedStr('REVERSED'));
Query_Red.Sql.Add('and floor(months_between(to_date(sysdate,''dd/mm/yyyy''),to_date(max(ts),''dd/mm/yyyy''))) = ' + QuotedStr(Edit1.text));
Query_Red.Sql.Add('group by vps.meter.msno,vps.meter.meter_status_id ,inst_date ,substr(vps.location.location_ref,1,20) ,substr(vps.area.area,1,3) ,vps.mrl.maxlimit');
Query_Red.Sql.Add('order by round(months_between(sysdate,max(ts))) , substr(vps.area.area,1,3),substr(vps.location.location_ref,1,20)');
Query_Red.Open; |
Partager