抚顺电厂更改低保等状态

--抚顺电厂备份的数据库调整现用数据库

update aa
set aa.gratisreason = bb.gratisreason from owereportmx aa, [shengji].dbo.owereportmx bb

where aa.yearnum = bb.yearnum and aa.ownerid = bb.ownerid
and aa.yearnum = '2012-2013'
and aa.gratisreason <> bb.gratisreason

 

--更新

update o
set o.gratisreason =
 case
  when charindex( '正常',p.appenidix,0) > 0 then '0'
  when charindex( '减免',p.appenidix,0) > 0 then '1' 
  when charindex( '低保边缘户',p.appenidix,0) > 0 then '4' 

  when charindex( '低保',p.appenidix,0) > 0 then '2'
  when charindex( '三方面',p.appenidix,0) > 0 then '3' 
  when charindex( '离休',p.appenidix,0) > 0 then '5'
  --when charindex( '供1-3月',p.appenidix,0) > 0 then '6'
 end
from paydetail p,owereportmx o
where o.ownerid = p.ownerid and
 (p.appenidix like '%正常%'
 OR p.appenidix like '%减免%'
 OR p.appenidix like '%低保%'
 OR p.appenidix like '%三方面%'
 OR p.appenidix like '%低保边缘户%'
 OR p.appenidix like '%离休%'
 --OR p.appenidix like '%供1-3月%'
 )
 and p.yearnum='20122013'
 and o.yearnum = '2012-2013'
 and p.exist <>'1'
 and left(billid,2) = 'db'

 

 

--更新业主表和收款明细表

update p
set p.gratisreason = o.gratisreason from owereportmx o, paydetail p
where o.yearnum = '2012-2013'
 and p.yearnum = '20122013'
 and p.ownerid = o.ownerid
 and p.gratisreason <> o.gratisreason
 and p.exist = '0'
 
update r
set r.gratisreason = o.gratisreason from owereportmx o,owner r
where o.yearnum = '2012-2013'
 and r.ownerid = o.ownerid
 and r.gratisreason <> o.gratisreason
 and r.exist = '0'

 

 

--20131102抚顺电厂新建楼房灌入业主信息

INSERT INTO owner
 ([ownerid]
 ,[name] 
 ,[contactmode]
 ,[yjmoney]
 ,[collectorid]
 ,[roomId]
 ,[Heatattribute]
 ,[Chargemodeid]
 ,[comein]
 ,[address]
 ,[appendix]
 ,[area]
 ,[roomtypeid]
 ,[feemodel]
 ,[area1]
 ,[Chargemodeid1]
 ,[orderId]
 ,[exist]
 ,[Gratis]
 ,[gratis1]
 ,[gratisreason])

select mx.ownerid,mx.name,mx.contactmode,mx.yjk,mx.collectorid,mx.roomid,mx.heatattribute,
 c.chargemodeId,mx.comein,mx.roomname,mx.appendix,mx.area,mx.roomtypeid,mx.boiler,mx.area,
 c.chargemodeId,1,'0',mx.gratis,mx.gratis1,'0'
from owereportmx mx left outer join
 (select Unitprice,max(chargemodeid) chargemodeid from chargemode group by Unitprice) c
 on mx.chargestandard=c.Unitprice
where mx.roomid in(
 select roomidtext from room
 where left(roomidtext,6) in   
  ('012202',
  '012203',
  '012204',
  '012205',
  '012206',
  '012207',
  '012209',
  '012211',
  '012213',
  '012214',
  '012216',
  '012231',
  '012232',
  '012233',
  '012235',
  
  '012501',
  '012502',
  '012503',
  '012504',
  
  '012401',
  '012402',
  '012403',
  
  '012011',
  '012013',
  '012015',
  
  '073001',
  
  '073101',
  '073104',
  '073105',
  '073106',
  '073107',
  '073108',
  '073109',
  '073110',
  '073111',
  '073112',
  '073113',
  '073114',
  '073115',
  '073116',
  '073121',
  
  '123403',
  '123404',
  '123405',
  '123406',
  '123407',
  '123408',
  '123410',

  '073301',
  '073302',
  '073303',
  '073304',
  '073306',
  '073307',
  '073308',
  '073309',
  '073310',
  '073311',
  '073312',
  '073313',
  '073314',
  '073315',
  '073318',
  '073319',
  '073320',
  '073321',
  '073322',
  '073323',
  '073324',
  '073325',
  '073326',
  '073327',
  '073328',
  '073329',
  '073330',
  '073331',
  '073332',
  '073333',
  '073334',
  '073335',
  '073336',
  '073351',
  '073352',
  '073353',
  '073354',
  '073355'
  )
  and roomidtext not in (select roomid from owner)
 )