drop table promemo
drop table conanmemo
drop table anoanmemo
CREATE TABLE promemo(cliente int,sumi int,promedio int)
INSERT INTO promemo
select [Link],[Link],round((sum(cnskwcns)/COUNT(*)),0) as promedio from consum
b right outer join sumser a on([Link]=[Link] and [Link]=[Link] and
(cnsano*100+cnsmes) between '201608' and '201702')
where [Link]=1 and cnssuc=1 group by [Link],[Link] order by 1,2
update promemo set promedio=0 where promedio is NULl
CREATE TABLE conanmemo(cliente int,sumi int,consu_ant int)
INSERT INTO conanmemo
select [Link],[Link],cnskwcns from consum b right outer join sumser a
on([Link]=[Link] and [Link]=[Link] and (cnsano*100+cnsmes)='201612') where
[Link]=1 and cnssuc=1 order by 1,2
update conanmemo set consu_ant=0 where consu_ant is NULL
CREATE TABLE anoanmemo(cliente int,sumi int,ano_ant int)
INSERT INTO anoanmemo
select [Link],[Link],cnskwcns from consum b right outer join sumser a
on([Link]=[Link] and [Link]=[Link] and (CnsAno*100+CnsMes)='201612') where
[Link]=1 and cnssuc=1 order by 1,2
update anoanmemo set ano_ant=0 where ano_ant is NULL
select SumRut,SumOrd,clinom,SUMUBI as
domicilio,[Link],[Link],d.sum6mdd,d.Sum6LecU,case when e.consu_ant is NULL THEN
0 ELSE e.consu_ant end as consu_ant,case when [Link] is NULL then 0 else
[Link] end as promedio,case when g.ano_ant is null then case when promedio is
NULL then case when consu_ant is NULL then 0 end else promedio end else ano_ant end
ano_ant,'0.50' as tolerancia,[Link],d.Sum6Mlt,Me1NroInt,1 as servicio, 0 as
LatitudReg, 0 as LongitudReg, 'Remesa' as AdicioanlEnt1, SUMREM as AdcionalEnt2,
'prueba 3' as AdicionalENt3
from socios a,sumser c,SUMMED d, MEDNUM h, MEDIDO i, SUMINI b
left outer join conanmemo e on (clicod=[Link] and sumnro=[Link]) left outer join
promemo f on (CliCod=[Link] and SumNro=[Link])
left outer join anoanmemo g on (CliCod=[Link] and SumNro=[Link])
where [Link]=[Link] and [Link]=[Link] and [Link]=[Link] and
[Link]=[Link] and [Link]=[Link] and [Link]=[Link]
and [Link]=1 and [Link]=[Link] and [Link]=d.Sum6tpomdd AND SUMREM IN(242)
and [Link]=[Link] and c.Sum2Sts in('1','2','3') and d.Sum6FecBaj='01-01-0001'
and h.me1sts=1 and d.sum6mdd=h.Me1Nro and [Link]=1-- and sumrem between '1' and
'4'
--and sumrut in(1,16,24)and
and sumare=1600 and sumrem=242
--and sumrut in(53,....) para seleccionar rutas
order by 1,2
create table MEMOTEMPORAL (ruta int, cliente int,sumionistro int, medidor
varchar(10), lectura int, ruedas int)
insert into memotemporal
select SumRut,[Link],[Link],d.sum6mdd,d.Sum6LecU,[Link]
from socios a,sumser c,SUMMED d, MEDNUM h, MEDIDO i, SUMINI b
left outer join conanmemo e on (clicod=[Link] and sumnro=[Link]) left outer join
promemo f on (CliCod=[Link] and SumNro=[Link])
left outer join anoanmemo g on (CliCod=[Link] and SumNro=[Link])
where [Link]=[Link] and [Link]=[Link] and [Link]=[Link] and
[Link]=[Link] and [Link]=[Link] and [Link]=[Link]
and [Link]=1 and [Link]=[Link] and [Link]=d.Sum6tpomdd AND SUMREM IN(242)
and [Link]=[Link] and c.Sum2Sts in('1','2','3') and d.Sum6FecBaj='01-01-0001'
and h.me1sts=1 and d.sum6mdd=h.Me1Nro and [Link]=1-- and sumrem between '1' and
'4'
--and sumrut in(1,16,24)and
and sumare=1600 and sumrem=242
--and sumrut in(53,....) para seleccionar rutas
order by 1,2
select * from memotemporal
update memotemporal set ruedas=