access 和sql server 复杂sql语句范例.

access
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
1CFG_ORGAN

select
  mid(chraddressid,1,6)+'0'+mid(chraddressid,7,2)+'0'+mid(chraddressid,9,2)+mid(chraddressid,11,2) as ORGAN_ID,
  (trim(chraddressname)+
  iif(trim(chrtype)='41' ,'村',
     iif(trim(chrtype)='42','居委会',
     iif(trim(chrtype)='31' ,'乡',
     iif(trim(chrtype)='32' ,'镇',
     iif(trim(chrtype)='33' ,'街')))))
     )
      as ORGAN_NAME,
     iif(trim(chrtype)='41' ,'6',
     iif(trim(chrtype)='42','6',
     iif(trim(chrtype)='31' ,'5',
     iif(trim(chrtype)='32' ,'5',
     iif(trim(chrtype)='33' ,'5','6')))))
      as ORGAN_LEVEL,
     iif(trim(chrtype)='41' ,mid(chraddressid,1,6)+'0'+mid(chraddressid,7,2)+'00000',
     iif(trim(chrtype)='42',mid(chraddressid,1,6)+'0'+mid(chraddressid,7,2)+'00000',
     iif(trim(chrtype)='31',mid(chraddressid,1,6),
     iif(trim(chrtype)='32' ,mid(chraddressid,1,6),
     iif(trim(chrtype)='33' ,mid(chraddressid,1,6), '000000')))))
     as PARENT_ORGAN_ID
from tbladdress where chrtype>'30' order by chrtype asc

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
select
mid(chrmigratein,1,6)+'0'+mid(chrmigratein,7,2)+'0'+mid(chrmigratein,9,2)
+mid(chrFemaleID,1,6) as BM,
    trim(chrname) as XM ,
trim(chridentity) as SFZH ,
mid(chrhousein,1,6)+'0'+mid(chrhousein,7,2)+'0'+mid(chrhousein,9,2)+mid(chrhousein,11,2)
as HJDDM ,
(SELECT chraddressname FROM tbladdress
WHERE trim(chraddressid)=(mid(trim(chrhousein),1,8)+'0000'))+
  iif( (SELECT chrtype FROM tbladdress
WHERE trim(chraddressid)=(mid(trim(chrhousein),1,8)+'0000'))='31','乡',
iif( (SELECT chrtype FROM tbladdress
WHERE trim(chraddressid)=(mid(trim(chrhousein),1,8)+'0000'))='32','镇','街'))+
   (SELECT trim(chraddressname) FROM tbladdress
WHERE mid(trim(chrhousein),1,10)=mid(trim(chraddressid),1,10) )+
iif( (SELECT chrtype FROM tbladdress
WHERE mid(trim(chrhousein),1,10)=mid(trim(chraddressid),1,10))='41','村','居委会')
as HJD,
  iif(chrMarriageState='1' or chrMarriageState='2' or chrMarriageState='5', 
(SELECT chraddressname FROM tbladdress
WHERE trim(chraddressid)=(mid(trim(chrhousein),1,8)+'0000'))+
iif( (SELECT chrtype FROM tbladdress
WHERE trim(chraddressid)=(mid(trim(chrhousein),1,8)+'0000'))='31','乡',
iif( (SELECT chrtype FROM tbladdress
WHERE trim(chraddressid)=(mid(trim(chrhousein),1,8)+'0000'))='32','镇','街'))+
   (SELECT trim(chraddressname) FROM tbladdress
WHERE mid(trim(chrhousein),1,10)=mid(trim(chraddressid),1,10) )+
iif( (SELECT chrtype FROM tbladdress
WHERE mid(trim(chrhousein),1,10)=mid(trim(chraddressid),1,10))='41','村','居委会')
 )
as ZFHJD,
mid(chrmigratein,1,6)+'0'+mid(chrmigratein,7,2)+'0'+mid(chrmigratein,9,2)+mid(chrmigratein,11,2)
as XJDDM,
(SELECT chraddressname FROM tbladdress
WHERE trim(chraddressid)=(mid(trim(chrmigratein),1,8)+'0000'))+
  iif( (SELECT chrtype FROM tbladdress
WHERE trim(chraddressid)=(mid(trim(chrmigratein),1,8)+'0000'))='31','乡',
iif( (SELECT chrtype FROM tbladdress
WHERE trim(chraddressid)=(mid(trim(chrmigratein),1,8)+'0000'))='32','镇','街'))+
   (SELECT trim(chraddressname) FROM tbladdress
WHERE mid(trim(chrmigratein),1,10)=mid(trim(chraddressid),1,10) )+
iif( (SELECT chrtype FROM tbladdress
WHERE mid(trim(chrmigratein),1,10)=mid(trim(chraddressid),1,10))='41','村','居委会')
as XJD,
   iif(chrMarriageState='1' or chrMarriageState='2' or chrMarriageState='5',
(SELECT chraddressname FROM tbladdress
WHERE trim(chraddressid)=(mid(trim(chrmigratein),1,8)+'0000'))+
  iif( (SELECT chrtype FROM tbladdress
WHERE trim(chraddressid)=(mid(trim(chrmigratein),1,8)+'0000'))='31','乡',
iif( (SELECT chrtype FROM tbladdress
WHERE trim(chraddressid)=(mid(trim(chrmigratein),1,8)+'0000'))='32','镇','街'))+
   (SELECT trim(chraddressname) FROM tbladdress
WHERE mid(trim(chrmigratein),1,10)=mid(trim(chraddressid),1,10) )+
iif( (SELECT chrtype FROM tbladdress
WHERE mid(trim(chrmigratein),1,10)=mid(trim(chraddressid),1,10))='41','村','居委会')
 )
as ZFXZZ,
  iif(chrMarriageState='1' or chrMarriageState='2' or chrMarriageState='5',
mid(chrhousein,1,6)+'0'+mid(chrhousein,7,2)+'0'+mid(chrhousein,9,2)+mid(chrhousein,11,2),'')
as ZFHJDDM,
  iif(chrMarriageState='1' or chrMarriageState='2' or chrMarriageState='5',
mid(chrmigratein,1,6)+'0'+mid(chrmigratein,7,2)+'0'+mid(chrmigratein,9,2)+mid(chrmigratein,11,2),'')
as ZFXZZDM,
  (SELECT  chraddressname FROM tbladdress
WHERE trim(chrworkin)=trim(chraddressid))
as FRDW,
  iif(trim(chrHousehold)='0','1',iif(trim(chrHousehold)='1','2',''))
as HKXZ,
  iif(len(trim(chrtelephone))=0,null,trim(chrtelephone)) as LXDH,
  chrdatebirth as CSRQ,
     iif(trim(chreducation)='0','90',
     iif(trim(chreducation)='1','80',
     iif(trim(chreducation)='2','70',
     iif(trim(chreducation)='3','60',
     iif(trim(chreducation)='4','30','')))))
   as WHCD,
     iif(trim(chrMarriageState)='0','10',
     iif(trim(chrMarriageState)='1','21',
     iif(trim(chrMarriageState)='2','22',
     iif(trim(chrMarriageState)='3','40',
     iif(trim(chrMarriageState)='4','30',
     iif(trim(chrMarriageState)='5','21',
     iif(trim(chrMarriageState)='6','90','')))))))
   as HYZK,
  chrdatefirst as CHRQ,
  chrdatechange as HYBDRQ,
  trim(chrMName) as ZFXM,
  trim(chrMIdentity) as ZFSFZH,
     iif(trim(chrMHousehold)='0','1',
     iif(trim(chrMHousehold)='1','2',''))
  as ZFHKXZ,
  chrMDateBirth as ZFCSRQ,
     iif(trim(chrmeducation)='0','90',
     iif(trim(chrmeducation)='1','80',
     iif(trim(chrmeducation)='2','70',
     iif(trim(chrmeducation)='3','60',
     iif(trim(chrmeducation)='4','30','')))))
  as ZFWFCD,
     iif(trim(chrdatecancel)<>''and mid(chrdatecancel,1,1)>'0','19'+mid(chrdatecancel,1,2)+'-'+mid(chrdatecancel,3,2)+'-'+mid(chrdatecancel,5,2),
     iif(trim(chrdatecancel)<>''and mid(chrdatecancel,1,1)='0','20'+mid(chrdatecancel,1,2)+'-'+mid(chrdatecancel,3,2)+'-'+mid(chrdatecancel,5,2), ''))
  as TCXTRQ,
     iif(trim(chrfemalecancel)='0','3',
     iif(trim(chrfemalecancel)='1','4',''))
  as TCXTYY,
     iif(trim(chrdatemake)<>'',mid(chrdatemake,1,4)+'-'+mid(chrdatemake,5,2)+'-01')
  as JRXTRQ,
     iif(trim(chrdatecredential)<>'',mid(chrdatecredential,1,4)+'-'+mid(chrdatecredential,5,2)+'-01')
  as LSDZRQ,
     iif(trim(chrchino)<>'',mid(chrchino,3,1),'')
  as XZCJTZNS,
  '01' as MZ,
  iif(chrMarriageState='1' or chrMarriageState='2' or chrMarriageState='5','01') as ZFMZ,
  '1'  as RYLB,
  '1'  as SX,
  '1' as JBQK_LDZT,
  iif(trim(chrRebear)<>'',trim(chrRebear)) AS BLSYSCYY,
  iif(trim(chrDateRebear)<>'',mid(chrDateRebear,1,4)+'-'+mid(chrDateRebear,5,2)+'-01') AS BLSYSCSJ,
  chrDateReport AS SBRQ
from tblBasic

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
//JKBHRQ 如果出生日期和 变更日期在一个月，变更日期day取出生日期的day，其他情况day取1

3WIS_ZNQK

select
distinct
mid(tblbasic.chrmigratein,1,6)+'0'+mid(tblbasic.chrmigratein,7,2)+'0'+mid(tblbasic.chrmigratein,9,2)
+mid(tblbasic.chrFemaleID,1,6) as BM,
  trim(tblchild.intsequence) as HC,
     iif(trim(tblchild.chrsex)='1','1',
     iif(trim(tblchild.chrsex)='2','2',
     iif(trim(tblchild.chrsex)='3','9','1')))
     as  ZNXB,
     trim(tblchild.chrattribute1)
     as XYGX,
  tblchild.chrdatebirth as CSRQ,
     iif(trim(tblchild.chrhealth)='0','1',
     iif(trim(tblchild.chrhealth)='1','3',
     iif(trim(tblchild.chrhealth)='2','3',
     iif(trim(tblchild.chrhealth)='3','4',
     iif(trim(tblchild.chrhealth)='4','5','5'))))) as JCZK,
      iif(trim(tblchild.chrdatechange)<>'',mid(tblchild.chrdatechange,1,4)+'-'+mid(tblchild.chrdatechange,5,2)+'-'+
       trim(datepart('d',tblchild.chrdatebirth)))
      as JKBHRQ,
    iif(trim(tblchild.chrattribute2)='00','11',
    iif(trim(tblchild.chrattribute2)='15','13',
    iif(val(tblchild.chrattribute2)<=17,'12',
     iif(trim(tblchild.chrattribute2)='18','21',
     iif(trim(tblchild.chrattribute2)='19','22',
     iif(trim(tblchild.chrattribute2)='20','23',
     iif(trim(tblchild.chrattribute2)='99','90','')))))))
     as SYZCSX,
  tblchild.chrdatewrite as CSBGRQ,
  trim(tblchild.intsequence)  as ID
from  tblchild,tblbasic
WHERE (tblchild.chrfemaleid=tblbasic.chrfemaleid)

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

select
   mid(tblbasic.chrmigratein,1,6)+'0'+mid(tblbasic.chrmigratein,7,2)+'0'+mid(tblbasic.chrmigratein,9,2)
+mid(tblbasic.chrFemaleID,1,6) as BM,
      iif(
      ((select count(*) 
from tblpregnant  c
         where (val(c.chrpregnantstate)>400) and (val(c.chrpregnantstate)<=405) and (tblpregnant.chrfemaleid=c.chrfemaleid) and
           (datediff('m',c.chrdatepregnant,tblpregnant.chrdatepregnant)<=12) and (c.chrdatepregnant<=tblpregnant.chrdatepregnant ))>=1),
(select max(c.intsequence) 
from tblpregnant  c
         where (val(c.chrpregnantstate)>400) and (val(c.chrpregnantstate)<=405) and (tblpregnant.chrfemaleid=c.chrfemaleid) and
           (datediff('m',c.chrdatepregnant,tblpregnant.chrdatepregnant)<=12)and (c.chrdatepregnant<=tblpregnant.chrdatepregnant )) ,tblpregnant.intsequence)
as RSQK_ID,
   tblpregnant.chrdatewrite as RSJSSBRQ,
     iif(trim(tblpregnant.chrpregnantstate)>='101' and trim(tblpregnant.chrpregnantstate)<='112','22',
     iif(trim(tblpregnant.chrpregnantstate)>='201' and trim(tblpregnant.chrpregnantstate)<='212','23',
     iif(trim(tblpregnant.chrpregnantstate)>='301' and trim(tblpregnant.chrpregnantstate)<='312','23',
     iif(trim(tblpregnant.chrpregnantstate)='406', '30',
     iif(trim(tblpregnant.chrpregnantstate)='407', '40',
     iif(trim(tblpregnant.chrpregnantstate)='408', '10',
     iif(trim(tblpregnant.chrpregnantstate)='409', '21',
     iif(trim(tblpregnant.chrpregnantstate)='410', '90',''))))))))
     as RSJG,
  tblpregnant.chrdatepregnant as RCZZRQ,
  iif(trim(tblpregnant.chrpregnantstate)>='101' and trim(tblpregnant.chrpregnantstate)<='112',dateadd('m',-2,tblpregnant.chrdatepregnant),
  iif(trim(tblpregnant.chrpregnantstate)>='201' and trim(tblpregnant.chrpregnantstate)<='212',dateadd('m',-4,tblpregnant.chrdatepregnant),
  iif(trim(tblpregnant.chrpregnantstate)>='301' and trim(tblpregnant.chrpregnantstate)<='312',dateadd('m',-8,tblpregnant.chrdatepregnant),
  iif(trim(tblpregnant.chrpregnantstate)='408',dateadd('m',-9,tblpregnant.chrdatepregnant),
  dateadd('m',-2,tblpregnant.chrdatepregnant))))) as MCYJRQ,
  iif(trim(tblpregnant.chrpregnantstate)>='101' and trim(tblpregnant.chrpregnantstate)<='112',dateadd('m',-2,tblpregnant.chrdatepregnant),
  iif(trim(tblpregnant.chrpregnantstate)>='201' and trim(tblpregnant.chrpregnantstate)<='212',dateadd('m',-4,tblpregnant.chrdatepregnant),
  iif(trim(tblpregnant.chrpregnantstate)>='301' and trim(tblpregnant.chrpregnantstate)<='312',dateadd('m',-8,tblpregnant.chrdatepregnant),
  iif(trim(tblpregnant.chrpregnantstate)='408',dateadd('m',-9,tblpregnant.chrdatepregnant),
  dateadd('m',-2,tblpregnant.chrdatepregnant))))) as RSKSSBRQ,
    iif(tblpregnant.chrpregnantstate>'100' and tblpregnant.chrpregnantstate<'400', '21')
    as RSZCSX
from tblpregnant,tblbasic
where (tblpregnant.chrfemaleid=tblbasic.chrfemaleid)and
( val(tblpregnant.chrpregnantstate)>100 and val(tblpregnant.chrpregnantstate)<400 or
val(tblpregnant.chrpregnantstate)>405 and val(tblpregnant.chrpregnantstate)<500 )

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
4WIS_RSQK_SOURCE2

select
   mid(tblbasic.chrmigratein,1,6)+'0'+mid(tblbasic.chrmigratein,7,2)+'0'+mid(tblbasic.chrmigratein,9,2)+mid(tblbasic.chrFemaleID,1,6) as BM,
   tblpregnant.intsequence as RSQK_ID,
   tblpregnant.chrdatewrite as RSKSSBRQ,
     iif(trim(tblpregnant.chrpregnantstate)='401','11',
     iif(trim(tblpregnant.chrpregnantstate)='402','12',
     iif(trim(tblpregnant.chrpregnantstate)='403','21',
     iif(trim(tblpregnant.chrpregnantstate)='404','22',
     iif(trim(tblpregnant.chrpregnantstate)='405','23','')))))as RSZCSX,
   tblpregnant.chrdatepregnant as MCYJRQ
from tblpregnant,tblbasic
where (tblpregnant.chrfemaleid=tblbasic.chrfemaleid)and
      (val(tblpregnant.chrpregnantstate)>400 and val(tblpregnant.chrpregnantstate)<=405)

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

select mid(tblbasic.chrmigratein,1,6)+'0'+mid(tblbasic.chrmigratein,7,2)+'0'+
mid(tblbasic.chrmigratein,9,2)+mid(tblbasic.chrFemaleID,1,6) as BM,
   (tblpregnant.intsequence+ (select count(*) from tblpregnant c where (
   c.chrfemaleid=tblpregnant.chrfemaleid and (c.chrpregnantstate='902' or c.chrpregnantstate='903'))and
   c.intsequence<tblpregnant.intsequence ))as BYQK_ID,
   tblpregnant.chrdatewrite as BYKSSBRQ,
   tblpregnant.chrdatepregnant as BYKSRQ,
     iif(trim(tblpregnant.chrpregnantstate)='8101','820',
     iif(trim(tblpregnant.chrpregnantstate)='8102','810',
     iif(trim(tblpregnant.chrpregnantstate)>='8201' and
         trim(tblpregnant.chrpregnantstate)<='8209','71'+mid(tblpregnant.chrpregnantstate,4,1),
     iif(trim(tblpregnant.chrpregnantstate)='8210','721',
     iif(trim(tblpregnant.chrpregnantstate)='8211','722',
     iif(trim(tblpregnant.chrpregnantstate)='8212','723',
     iif(trim(tblpregnant.chrpregnantstate)='8300','190',
     iif(trim(tblpregnant.chrpregnantstate)>='8311' and
         trim(tblpregnant.chrpregnantstate)<='8317','1'+mid(tblpregnant.chrpregnantstate,3,2),
     iif(trim(tblpregnant.chrpregnantstate)='8321','118',
     iif(trim(tblpregnant.chrpregnantstate)='8322','119',
     iif(trim(tblpregnant.chrpregnantstate)='8323','121',
     iif(trim(tblpregnant.chrpregnantstate)='8324','122', '')))))))))))) AS  BYZK
from tblpregnant,tblbasic
where (tblpregnant.chrfemaleid=tblbasic.chrfemaleid)and
 (val(tblpregnant.chrpregnantstate)>1000 and val(tblpregnant.chrpregnantstate)<=8324)

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
5WIS_BYQK_SOURCE2

select mid(tblbasic.chrmigratein,1,6)+'0'+mid(tblbasic.chrmigratein,7,2)+'0'+mid(tblbasic.chrmigratein,9,2)+mid(tblbasic.chrFemaleID,1,6) as BM,
  (tblpregnant.intsequence+ (select count(*) from tblpregnant c where (
   c.chrfemaleid=tblpregnant.chrfemaleid and (c.chrpregnantstate='902' or c.chrpregnantstate='903'))and
   c.intsequence<tblpregnant.intsequence ))as BYQK_ID,
   tblpregnant.chrdatewrite as BYKSSBRQ,
   tblpregnant.chrdatepregnant as BYKSRQ,
     iif(trim(tblpregnant.chrpregnantstate)='8325','123',
     iif(trim(tblpregnant.chrpregnantstate)='8331','124',
     iif(trim(tblpregnant.chrpregnantstate)>='8401' and
         trim(tblpregnant.chrpregnantstate)<='8404','21'+mid(tblpregnant.chrpregnantstate,4,1),
     iif(trim(tblpregnant.chrpregnantstate)='8500','313',
     iif(trim(tblpregnant.chrpregnantstate)='8501','311',
     iif(trim(tblpregnant.chrpregnantstate)='8502','312',
     iif(trim(tblpregnant.chrpregnantstate)='8600','615',
     iif(trim(tblpregnant.chrpregnantstate)='8601','501',
     iif(trim(tblpregnant.chrpregnantstate)='8602','612',
     iif(trim(tblpregnant.chrpregnantstate)='8603','613',
     iif(trim(tblpregnant.chrpregnantstate)='8604','504',
     iif(trim(tblpregnant.chrpregnantstate)='8701','610','')))))))))))) as BYZK
from tblpregnant,tblbasic
where (tblpregnant.chrfemaleid=tblbasic.chrfemaleid)and (val(tblpregnant.chrpregnantstate)>=8325)

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
5WIS_BYQK_SOURCE3

select
   mid(tblbasic.chrmigratein,1,6)+'0'+mid(tblbasic.chrmigratein,7,2)+'0'+mid(tblbasic.chrmigratein,9,2)
+mid(tblbasic.chrFemaleID,1,6) as BM,
(tblpregnant.intsequence+ (select count(*) from tblpregnant c where (
   c.chrfemaleid=tblpregnant.chrfemaleid and (c.chrpregnantstate='902' or c.chrpregnantstate='903'))and
   c.intsequence<tblpregnant.intsequence ))as BYQK_ID,
   tblpregnant.chrdatewrite as BYKSSBRQ,
   tblpregnant.chrdatepregnant as BYKSRQ,
   iif(trim(tblpregnant.chrpregnantstate)='901','A40',
     iif(trim(tblpregnant.chrpregnantstate)='902','A40',
     iif(trim(tblpregnant.chrpregnantstate)='903','A40',
     iif(trim(tblpregnant.chrpregnantstate)='904','A41',
     iif(trim(tblpregnant.chrpregnantstate)='905','A51',
     iif(trim(tblpregnant.chrpregnantstate)='906','A52',
     iif(trim(tblpregnant.chrpregnantstate)='907','A53',
     iif(trim(tblpregnant.chrpregnantstate)='908','A54',''))))))))
   as BYZK
from tblpregnant,tblbasic
where (tblpregnant.chrfemaleid=tblbasic.chrfemaleid)and
(val(tblpregnant.chrpregnantstate)>900 and
val(tblpregnant.chrpregnantstate)<=908)

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
5WIS_BYQK_SOURCE4

select
   mid(tblbasic.chrmigratein,1,6)+'0'+mid(tblbasic.chrmigratein,7,2)+'0'+mid(tblbasic.chrmigratein,9,2)
+mid(tblbasic.chrFemaleID,1,6) as BM,
 (tblpregnant.intsequence+ (select count(*) from tblpregnant c where (
   c.chrfemaleid=tblpregnant.chrfemaleid and (c.chrpregnantstate='902' or c.chrpregnantstate='903'))and
   c.intsequence<tblpregnant.intsequence ))as BYQK_ID,
   tblpregnant.chrdatewrite as BYKSSBRQ,
   tblpregnant.chrdatepregnant as BYKSRQ,
   iif(trim(tblpregnant.chrpregnantstate)='909','A10',
iif(trim(tblpregnant.chrpregnantstate)='910','A55',
iif(trim(tblpregnant.chrpregnantstate)='911','A56',
iif(trim(tblpregnant.chrpregnantstate)='912','A57',
iif(trim(tblpregnant.chrpregnantstate)='913','A90',
iif(trim(tblpregnant.chrpregnantstate)='914','A30',
iif(trim(tblpregnant.chrpregnantstate)='915','A58',
iif(trim(tblpregnant.chrpregnantstate)='916','A90',''))))))))
as BYZK
from tblpregnant,tblbasic
where (tblpregnant.chrfemaleid=tblbasic.chrfemaleid)and
(val(tblpregnant.chrpregnantstate)>908 and
val(tblpregnant.chrpregnantstate)<1000)

 

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
5WIS_BYQK_SOURCE5

select
   mid(tblbasic.chrmigratein,1,6)+'0'+mid(tblbasic.chrmigratein,7,2)+'0'+mid(tblbasic.chrmigratein,9,2)
+mid(tblbasic.chrFemaleID,1,6) as BM,
(tblpregnant.intsequence+1+ (select count(*) from tblpregnant c where (
   c.chrfemaleid=tblpregnant.chrfemaleid and (c.chrpregnantstate='902' or c.chrpregnantstate='903'))and
   c.intsequence<tblpregnant.intsequence ))as BYQK_ID,
   tblpregnant.chrdatewrite as BYKSSBRQ,
   tblpregnant.chrdatepregnant as BYKSRQ,
     iif(trim(tblpregnant.chrpregnantstate)='902','A30',
     iif(trim(tblpregnant.chrpregnantstate)='903','A10'))    
   as BYZK
from tblpregnant,tblbasic
where (tblpregnant.chrfemaleid=tblbasic.chrfemaleid)and
(val(tblpregnant.chrpregnantstate)=902 or
val(tblpregnant.chrpregnantstate)=903)

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
5WIS_BYQK_SOURCE6

select
   mid(tblbasic.chrmigratein,1,6)+'0'+mid(tblbasic.chrmigratein,7,2)+'0'+mid(tblbasic.chrmigratein,9,2)+mid(tblbasic.chrFemaleID,1,6)
   as BM,
 (tblpregnant.intsequence+(select count(*) from tblpregnant c where (
   c.chrfemaleid=tblpregnant.chrfemaleid and (c.chrpregnantstate='902' or c.chrpregnantstate='903'))and
   c.intsequence<tblpregnant.intsequence ))as BYQK_ID,
   tblpregnant.chrdatewrite as BYKSSBRQ,
   tblpregnant.chrdatepregnant as BYKSRQ,
     iif(trim(tblpregnant.chrpregnantstate)='401','A20',
     iif(trim(tblpregnant.chrpregnantstate)='402','A20',
     iif(trim(tblpregnant.chrpregnantstate)='403','A20',
     iif(trim(tblpregnant.chrpregnantstate)='404','A20',
     iif(trim(tblpregnant.chrpregnantstate)='405','A20','')))))as BYZK
from tblpregnant,tblbasic
where (tblpregnant.chrfemaleid=tblbasic.chrfemaleid)and
      (val(tblpregnant.chrpregnantstate)>400 and val(tblpregnant.chrpregnantstate)<=405)

 sqlserver

1CFG_ORGAN

select
  substring(chraddressid,1,6)+'0'+substring(chraddressid,7,2)+'0'+substring(chraddressid,9,2)+substring(chraddressid,11,2) as ORGAN_ID,
  (ltrim(rtrim(chraddressname)) +
  case
     when (ltrim(rtrim(chrtype))='41') then '村'
     when (ltrim(rtrim(chrtype))='42') then '居委会'
     when (ltrim(rtrim(chrtype))='31') then '乡'
     when (ltrim(rtrim(chrtype))='32') then '镇'
     when (ltrim(rtrim(chrtype))='33') then '街'
     end ) 
  as ORGAN_NAME,
  case
     when (ltrim(rtrim(chrtype))='41') then '6'
     when (ltrim(rtrim(chrtype))='42') then '6'
     when (ltrim(rtrim(chrtype))='31') then '5'
     when (ltrim(rtrim(chrtype))='32') then '5'
     when (ltrim(rtrim(chrtype))='33') then '5'
     else '6'
  end as ORGAN_LEVEL,
  case
     when (ltrim(rtrim(chrtype))='41') then substring(chraddressid,1,6)+'0'+substring(chraddressid,7,2)+'00000'
     when (ltrim(rtrim(chrtype))='42') then substring(chraddressid,1,6)+'0'+substring(chraddressid,7,2)+'00000'
     when (ltrim(rtrim(chrtype))='31') then substring(chraddressid,1,6)
     when (ltrim(rtrim(chrtype))='32') then substring(chraddressid,1,6)
     when (ltrim(rtrim(chrtype))='33') then substring(chraddressid,1,6)
     else '000000'
  end as PARENT_ORGAN_ID

from tbladdress where chrtype>'30' order by chrtype asc

 

2WIS_JBQK_SOURCE

SELECT
substring(chrmigratein, 1, 6) + '0' + substring(chrmigratein, 7, 2)
      + '0' + substring(chrmigratein, 9, 2) + substring(chrFemaleID, 1, 6) AS BM,
      chrname AS XM, ltrim(rtrim(chridentity)) AS SFZH, substring(chrhousein, 1, 6)
      + '0' + substring(chrhousein, 7, 2) + '0' + substring(chrhousein, 9, 2)
      + substring(chrhousein, 11, 2) AS HJDDM,     
     ( (SELECT rtrim(ltrim(chraddressname)) FROM tbladdress
WHERE rtrim(ltrim(chraddressid))=(substring(rtrim(ltrim(chrhousein)),1,8)+'0000') )+
 ( case when (SELECT chrtype FROM tbladdress
WHERE rtrim(ltrim(chraddressid))=(substring(rtrim(ltrim(chrhousein)),1,8)+'0000') )='31' then '乡'
       when (SELECT chrtype FROM tbladdress
WHERE rtrim(ltrim(chraddressid))=(substring(rtrim(ltrim(chrhousein)),1,8)+'0000'))='32' then '镇'
else '街' end)+
   (SELECT rtrim(ltrim(chraddressname)) FROM tbladdress
WHERE substring(rtrim(ltrim(chrhousein)),1,10)=substring(rtrim(ltrim(chraddressid)),1,10) )+
(case when (SELECT chrtype FROM tbladdress
WHERE substring(rtrim(ltrim(chrhousein)),1,10)=substring(rtrim(ltrim(chraddressid)),1,10))='41' then '村'
else '居委会' end) )
as HJD,
          case when (ltrim(rtrim(chrMarriageState))='1' or ltrim(rtrim(chrMarriageState))='2' or
                  ltrim(rtrim(chrMarriageState))='5' ) then
         ( (SELECT rtrim(ltrim(chraddressname)) FROM tbladdress
WHERE rtrim(ltrim(chraddressid))=(substring(rtrim(ltrim(chrhousein)),1,8)+'0000') )+
 ( case when (SELECT chrtype FROM tbladdress
WHERE rtrim(ltrim(chraddressid))=(substring(rtrim(ltrim(chrhousein)),1,8)+'0000') )='31' then '乡'
       when (SELECT chrtype FROM tbladdress
WHERE rtrim(ltrim(chraddressid))=(substring(rtrim(ltrim(chrhousein)),1,8)+'0000'))='32' then '镇'
else '街' end)+
   (SELECT rtrim(ltrim(chraddressname)) FROM tbladdress
WHERE substring(rtrim(ltrim(chrhousein)),1,10)=substring(rtrim(ltrim(chraddressid)),1,10) )+
(case when (SELECT chrtype FROM tbladdress
WHERE substring(rtrim(ltrim(chrhousein)),1,10)=substring(rtrim(ltrim(chraddressid)),1,10))='41' then '村'
else '居委会' end) ) end       
         AS ZFHJD,
      substring(chrmigratein, 1, 6) + '0' + substring(chrmigratein, 7, 2)
      + '0' + substring(chrmigratein, 9, 2) + substring(chrmigratein, 11, 2) AS XJDDM,
         ( (SELECT rtrim(ltrim(chraddressname)) FROM tbladdress
WHERE rtrim(ltrim(chraddressid))=(substring(rtrim(ltrim(chrmigratein)),1,8)+'0000') )+
 ( case when (SELECT chrtype FROM tbladdress
WHERE rtrim(ltrim(chraddressid))=(substring(rtrim(ltrim(chrmigratein)),1,8)+'0000') )='31' then '乡'
       when (SELECT chrtype FROM tbladdress
WHERE rtrim(ltrim(chraddressid))=(substring(rtrim(ltrim(chrmigratein)),1,8)+'0000'))='32' then '镇'
else '街' end)+
   (SELECT rtrim(ltrim(chraddressname)) FROM tbladdress
WHERE substring(rtrim(ltrim(chrmigratein)),1,10)=substring(rtrim(ltrim(chraddressid)),1,10) )+
(case when (SELECT chrtype FROM tbladdress
WHERE substring(rtrim(ltrim(chrhousein)),1,10)=substring(rtrim(ltrim(chraddressid)),1,10))='41' then '村'
else '居委会' end) )
          AS XJD,
       case when (ltrim(rtrim(chrMarriageState))='1' or ltrim(rtrim(chrMarriageState))='2' or
                  ltrim(rtrim(chrMarriageState))='5' ) then
         ( (SELECT rtrim(ltrim(chraddressname)) FROM tbladdress
WHERE rtrim(ltrim(chraddressid))=(substring(rtrim(ltrim(chrmigratein)),1,8)+'0000') )+
 ( case when (SELECT chrtype FROM tbladdress
WHERE rtrim(ltrim(chraddressid))=(substring(rtrim(ltrim(chrmigratein)),1,8)+'0000') )='31' then '乡'
       when (SELECT chrtype FROM tbladdress
WHERE rtrim(ltrim(chraddressid))=(substring(rtrim(ltrim(chrmigratein)),1,8)+'0000'))='32' then '镇'
else '街' end)+
   (SELECT rtrim(ltrim(chraddressname)) FROM tbladdress
WHERE substring(rtrim(ltrim(chrmigratein)),1,10)=substring(rtrim(ltrim(chraddressid)),1,10) )+
(case when (SELECT chrtype FROM tbladdress
WHERE substring(rtrim(ltrim(chrhousein)),1,10)=substring(rtrim(ltrim(chraddressid)),1,10))='41' then '村'
else '居委会' end) ) end
         AS ZFXZZ,
         case when (ltrim(rtrim(chrMarriageState))='1' or ltrim(rtrim(chrMarriageState))='2' or
                  ltrim(rtrim(chrMarriageState))='5') then
      substring(chrhousein, 1, 6) + '0' + substring(chrhousein, 7, 2)
      + '0' + substring(chrhousein, 9, 2) + substring(chrhousein, 11, 2)
         else '' end  AS ZFHJDDM,
       case when (ltrim(rtrim(chrMarriageState))='1' or ltrim(rtrim(chrMarriageState))='2' or
                  ltrim(rtrim(chrMarriageState))='5' ) then
       substring(chrmigratein, 1, 6) + '0' + substring(chrmigratein, 7, 2)
      + '0' + substring(chrmigratein, 9, 2) + substring(chrmigratein, 11, 2)
         else '' end AS ZFXZZDM,
          (SELECT chraddressname
         FROM tbladdress
         WHERE RTRIM(LTRIM(chrworkin)) = RTRIM(LTRIM(chraddressid))) AS FRDW,
      CASE WHEN (ltrim(rtrim(chrHousehold)) = '0')
      THEN '1' WHEN (ltrim(rtrim(chrHousehold)) = '1') THEN '2' ELSE '' END AS HKXZ,
     case when len(ltrim(rtrim(chrtelephone)))=0 then null
          else ltrim(rtrim(chrtelephone)) end AS LXDH,
            chrdatebirth AS CSRQ, CASE WHEN (ltrim(rtrim(chreducation))
      = '0') THEN '90' WHEN (ltrim(rtrim(chreducation)) = '1')
      THEN '80' WHEN (ltrim(rtrim(chreducation)) = '2')
      THEN '70' WHEN (ltrim(rtrim(chreducation)) = '3')
      THEN '60' WHEN (ltrim(rtrim(chreducation)) = '4') THEN '30' ELSE '' END AS WHCD,
      CASE WHEN (ltrim(rtrim(chrMarriageState)) = '0')
      THEN '10' WHEN (ltrim(rtrim(chrMarriageState)) = '1')
      THEN '21' WHEN (ltrim(rtrim(chrMarriageState)) = '2')
      THEN '22' WHEN (ltrim(rtrim(chrMarriageState)) = '3')
      THEN '40' WHEN (ltrim(rtrim(chrMarriageState)) = '4')
      THEN '30' WHEN (ltrim(rtrim(chrMarriageState)) = '5')
      THEN '21' WHEN (ltrim(rtrim(chrMarriageState)) = '6')
      THEN '90' ELSE '' END AS HYZK, chrdatefirst AS CHRQ, chrdatechange AS HYBDRQ,
      chrMName AS ZFXM, ltrim(rtrim(chrMIdentity)) AS ZFSFZH,
      CASE WHEN (ltrim(rtrim(chrMHousehold)) = '0')
      THEN '1' WHEN (ltrim(rtrim(chrMHousehold)) = '1')
      THEN '2' ELSE '' END AS ZFHKXZ, chrMDateBirth AS ZFCSRQ,
      CASE WHEN (ltrim(rtrim(chrmeducation)) = '0')
      THEN '90' WHEN (ltrim(rtrim(chrmeducation)) = '1')
      THEN '80' WHEN (ltrim(rtrim(chrmeducation)) = '2')
      THEN '70' WHEN (ltrim(rtrim(chrmeducation)) = '3')
      THEN '60' WHEN (ltrim(rtrim(chrmeducation)) = '4')
      THEN '30' ELSE '' END AS ZFWFCD, CASE WHEN ltrim(rtrim(chrdatecancel))
      != '' THEN substring(chrdatecancel, 1, 4) + '-' + substring(chrdatecancel, 5, 2)
      + '-01' ELSE NULL END AS TCXTRQ, CASE WHEN (ltrim(rtrim(chrfemalecancel)) = '0')
      THEN '3' WHEN (ltrim(rtrim(chrfemalecancel)) = '1')
      THEN '4' ELSE '' END AS TCXTYY, CASE WHEN ltrim(rtrim(chrdatemake))
      != '' THEN substring(chrdatemake, 1, 4) + '-' + substring(chrdatemake, 5, 2)
      + '-01' ELSE '' END AS JRXTRQ, CASE WHEN ltrim(rtrim(chrdatecredential))
      != '' THEN substring(chrdatecredential, 1, 4) + '-' + substring(chrdatecredential, 5, 2)
      + '-01'  END AS LSDZRQ, CASE WHEN ltrim(rtrim(chrchino))
      != '' THEN substring(chrchino, 3, 1) ELSE '' END AS XZCJTZNS, '01' AS MZ,
      case when (ltrim(rtrim(chrMarriageState))='1' or ltrim(rtrim(chrMarriageState))='2' or
                  ltrim(rtrim(chrMarriageState))='5' ) then '01' end AS ZFMZ, '1' AS RYLB, '1' AS SX, '1' AS JBQK_LDZT,
      CASE WHEN ltrim(rtrim(chrRebear))
      != '' THEN chrRebear  END
       AS BLSYSCYY,CASE WHEN ltrim(rtrim(chrDateRebear))
      != '' THEN substring(chrDateRebear, 1, 4) + '-' + substring(chrDateRebear, 5, 2)
      + '-01' ELSE '' END AS BLSYSCSJ,chrDateReport AS SBRQ

FROM tblBasic
WHERE ((ltrim(rtrim(chrmigratein))<> '') AND (chrmigratein NOT LIKE '000000000000'))

 

3WIS_ZNQK_SOURCE

select
distinct   substring(tblbasic.chrmigratein,1,6)+'0'+substring(tblbasic.chrmigratein,7,2)+'0'+substring(tblbasic.chrmigratein,9,2)
+substring(tblbasic.chrFemaleID,1,6) as BM,
  tblchild.intsequence as HC,
  case
     when (ltrim(rtrim(tblchild.chrsex))='1') then '1'
     when (ltrim(rtrim(tblchild.chrsex))='2') then '2'
     when (ltrim(rtrim(tblchild.chrsex))='3') then '9'
     else '1'
  end as ZNXB,
  ltrim(rtrim(tblchild.chrattribute1))
 as XYGX,
  tblchild.chrdatebirth as CSRQ,
  case
     when (ltrim(rtrim(tblchild.chrhealth))='0') then '1'
     when (ltrim(rtrim(tblchild.chrhealth))='1') then '3'
     when (ltrim(rtrim(tblchild.chrhealth))='2') then '3'
     when (ltrim(rtrim(tblchild.chrhealth))='3') then '4'
     when (ltrim(rtrim(tblchild.chrhealth))='4') then '5'
     else '5'
  end as JCZK,
  case
      when ltrim(rtrim(tblchild.chrdatechange))!='' then substring(tblchild.chrdatechange,1,4)+'-'+substring(tblchild.chrdatechange,5,2)+
      '-'+ltrim(rtrim(datepart(day,tblchild.chrdatebirth)))
        end as JKBHRQ,
  case
     when (ltrim(rtrim(tblchild.chrattribute2))='00') then '11'
     when (ltrim(rtrim(tblchild.chrattribute2))='01') then '12'
     when (ltrim(rtrim(tblchild.chrattribute2))='02') then '12'
     when (ltrim(rtrim(tblchild.chrattribute2))='03') then '12'
     when (ltrim(rtrim(tblchild.chrattribute2))='04') then '12'
     when (ltrim(rtrim(tblchild.chrattribute2))='05') then '12'
     when (ltrim(rtrim(tblchild.chrattribute2))='06') then '12'
     when (ltrim(rtrim(tblchild.chrattribute2))='07') then '12'
     when (ltrim(rtrim(tblchild.chrattribute2))='08') then '12'
     when (ltrim(rtrim(tblchild.chrattribute2))='09') then '12'
     when (ltrim(rtrim(tblchild.chrattribute2))='10') then '12'
     when (ltrim(rtrim(tblchild.chrattribute2))='11') then '12'
     when (ltrim(rtrim(tblchild.chrattribute2))='12') then '12'
     when (ltrim(rtrim(tblchild.chrattribute2))='13') then '12'
     when (ltrim(rtrim(tblchild.chrattribute2))='14') then '12'
     when (ltrim(rtrim(tblchild.chrattribute2))='15') then '13'
     when (ltrim(rtrim(tblchild.chrattribute2))='16') then '12'
     when (ltrim(rtrim(tblchild.chrattribute2))='17') then '12'
     when (ltrim(rtrim(tblchild.chrattribute2))='18') then '21'
     when (ltrim(rtrim(tblchild.chrattribute2))='19') then '22'
     when (ltrim(rtrim(tblchild.chrattribute2))='20') then '23'
     when (ltrim(rtrim(tblchild.chrattribute2))='99') then '90'
     else ''
  end SYZCSX,
  tblchild.chrdatewrite as CSBGRQ,
  tblchild.intsequence as  ID
from tblchild,tblbasic
where  (tblchild.chrfemaleid=tblbasic.chrfemaleid) AND
((ltrim(rtrim(tblbasic.chrmigratein))<> '') AND (tblbasic.chrmigratein NOT LIKE '000000000000'))

4WIS_RSQK_SOURCE1

select
substring(tblbasic.chrmigratein,1,6)+'0'+substring(tblbasic.chrmigratein,7,2)+'0'+substring(tblbasic.chrmigratein,9,2)
+substring(tblbasic.chrFemaleID,1,6) as BM,
  case
   when
  (select count(c.intsequence)
 from tblpregnant as c
         where (tblpregnant.chrfemaleid=c.chrfemaleid) and (convert(int,c.chrpregnantstate)>400) and (convert(int,c.chrpregnantstate)<=405) and
           (datediff(m,c.chrdatepregnant,tblpregnant.chrdatepregnant)<=12)and (c.chrdatepregnant<=tblpregnant.chrdatepregnant)  )=0
           then tblpregnant.intsequence
   else
      (select max(c.intsequence)
 from tblpregnant as c
         where (tblpregnant.chrfemaleid=c.chrfemaleid) and (convert(int,c.chrpregnantstate)>400) and (convert(int,c.chrpregnantstate)<=405) and
           (datediff(m,c.chrdatepregnant,tblpregnant.chrdatepregnant)<=12)and (c.chrdatepregnant<=tblpregnant.chrdatepregnant))
           end as RSQK_ID,
           case
           when convert(int,tblpregnant.chrpregnantstate)>=101 and convert(int,tblpregnant.chrpregnantstate)<=112 then dateadd(m,-2,tblpregnant.chrdatepregnant)
           when convert(int,tblpregnant.chrpregnantstate)>=201 and convert(int,tblpregnant.chrpregnantstate)<=212 then dateadd(m,-4,tblpregnant.chrdatepregnant)
      when convert(int,tblpregnant.chrpregnantstate)>=301 and convert(int,tblpregnant.chrpregnantstate)<=312 then dateadd(m,-8,tblpregnant.chrdatepregnant)
           when convert(int,tblpregnant.chrpregnantstate)=408 then dateadd(m,-9,tblpregnant.chrdatepregnant)
           else dateadd(m,-2,tblpregnant.chrdatepregnant)
           end  as MCYJRQ,
           case
           when convert(int,tblpregnant.chrpregnantstate)>=101 and convert(int,tblpregnant.chrpregnantstate)<=112 then dateadd(m,-2,tblpregnant.chrdatepregnant)
           when convert(int,tblpregnant.chrpregnantstate)>=201 and convert(int,tblpregnant.chrpregnantstate)<=212 then dateadd(m,-4,tblpregnant.chrdatepregnant)
      when convert(int,tblpregnant.chrpregnantstate)>=301 and convert(int,tblpregnant.chrpregnantstate)<=312 then dateadd(m,-8,tblpregnant.chrdatepregnant)
           when convert(int,tblpregnant.chrpregnantstate)=408 then dateadd(m,-9,tblpregnant.chrdatepregnant)
           else dateadd(m,-2,tblpregnant.chrdatepregnant)
           end  as RSKSSBRQ,
   tblpregnant.chrdatewrite as RSJSSBRQ,
   case
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='101') then '22'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='102') then '22'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='103') then '22'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='104') then '22'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='105') then '22'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='106') then '22'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='107') then '22'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='108') then '22'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='109') then '22'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='110') then '22'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='111') then '22'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='112') then '22'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='201') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='202') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='203') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='204') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='205') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='206') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='207') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='208') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='209') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='210') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='211') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='212') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='301') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='302') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='303') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='304') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='305') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='306') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='307') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='308') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='309') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='310') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='311') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='312') then '23'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='406') then '30'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='407') then '40'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='408') then '10'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='409') then '21'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='410') then '90'
     else ''
  end as RSJG,
tblpregnant.chrdatepregnant as RCZZRQ,
   case
    when (ltrim(rtrim(tblpregnant.chrpregnantstate))>'100' and ltrim(rtrim(tblpregnant.chrpregnantstate))<'400') then '21'
   end as RSZCSX
from tblpregnant,tblbasic
where ((tblpregnant.chrfemaleid=tblbasic.chrfemaleid ) AND ((ltrim(rtrim(tblbasic.chrmigratein))<> '')
AND (tblbasic.chrmigratein NOT LIKE '000000000000')))and((convert(int,tblpregnant.chrpregnantstate)>100 and convert(int,tblpregnant.chrpregnantstate)<400) or
(convert(int,tblpregnant.chrpregnantstate)>405 and convert(int,tblpregnant.chrpregnantstate)<500))

4WIS_RSQK_SOURCE2

select
   substring(tblbasic.chrmigratein,1,6)+'0'+substring(tblbasic.chrmigratein,7,2)+'0'+substring(tblbasic.chrmigratein,9,2)
+substring(tblbasic.chrFemaleID,1,6) as BM,
   tblpregnant.intsequence as RSQK_ID,
   tblpregnant.chrdatewrite as RSKSSBRQ,
   case
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='401') then '11'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='402') then '12'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='403') then '21'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='404') then '22'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='405') then '23'
   end   as RSZCSX,
   tblpregnant.chrdatepregnant as MCYJRQ
from tblpregnant,tblbasic
where (tblpregnant.chrfemaleid=tblbasic.chrfemaleid)and
(convert(int,tblpregnant.chrpregnantstate)>400 and convert(int,tblpregnant.chrpregnantstate)<=405)
AND ((ltrim(rtrim(tblbasic.chrmigratein))<> '') AND (tblbasic.chrmigratein NOT LIKE '000000000000'))

5WIS_BYQK_SOURCE1

select substring(tblbasic.chrmigratein,1,6)+'0'+substring(tblbasic.chrmigratein,7,2)+'0'+substring(tblbasic.chrmigratein,9,2)
+substring(tblbasic.chrFemaleID,1,6) as BM,
  tblpregnant.intsequence as BYQK_ID,
   tblpregnant.chrdatewrite as BYKSSBRQ,
   tblpregnant.chrdatepregnant as BYKSRQ,
  case
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8101') then '820'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8102') then '810'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8201') then '711'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8202') then '712'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8203') then '713'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8204') then '714'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8205') then '715'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8206') then '716'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8207') then '717'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8208') then '718'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8209') then '719'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8210') then '721'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8211') then '722'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8212') then '723'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8300') then '190'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8311') then '111'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8312') then '112'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8313') then '113'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8314') then '114'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8315') then '115'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8316') then '115'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8317') then '117'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8321') then '118'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8322') then '119'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8323') then '121'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8324') then '122'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8325') then '123'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8331') then '124'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8401') then '211'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8402') then '212'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8403') then '213'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8404') then '214'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8500') then '313'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8501') then '311'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8502') then '312'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8600') then '615'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8601') then '501'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8602') then '612'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8603') then '613'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8604') then '504'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='8701') then '610'
     else ''
  end as BYZK
from tblpregnant,tblbasic
where (tblpregnant.chrfemaleid=tblbasic.chrfemaleid)and (convert(int,tblpregnant.chrpregnantstate)>1000)
AND ((ltrim(rtrim(tblbasic.chrmigratein)) <> '') AND (tblbasic.chrmigratein NOT LIKE '000000000000'))

5WIS_BYQK_SOURCE2

select
   substring(tblbasic.chrmigratein,1,6)+'0'+substring(tblbasic.chrmigratein,7,2)+'0'+substring(tblbasic.chrmigratein,9,2)
+substring(tblbasic.chrFemaleID,1,6) as BM,
 tblpregnant.intsequence as BYQK_ID,
   tblpregnant.chrdatewrite as BYKSSBRQ,
   tblpregnant.chrdatepregnant as BYKSRQ,
   case
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='901') then 'A40'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='902') then 'A30'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='903') then 'A10'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='904') then 'A41'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='905') then 'A51'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='906') then 'A52'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='907') then 'A53'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='908') then 'A54'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='909') then 'A10'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='910') then 'A55'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='911') then 'A56'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='912') then 'A57'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='913') then 'A90'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='914') then 'A30'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='915') then 'A58'
     when (ltrim(rtrim(tblpregnant.chrpregnantstate))='916') then 'A90'
          else ''
  end as BYZK
from tblpregnant,tblbasic
where (tblpregnant.chrfemaleid=tblbasic.chrfemaleid)
AND ((ltrim(rtrim(tblbasic.chrmigratein))<> '') AND (tblbasic.chrmigratein NOT LIKE '000000000000'))
and(convert(int,tblpregnant.chrpregnantstate)>900 and convert(int,tblpregnant.chrpregnantstate)<1000)