p_posent_person.sql

一个地方税务征收管理系统

declare @acount_id int
select @acount_id=10

SELECT * FROM 

(
SELECT dbo.rw.person_id, dbo.person.pno, dbo.person.name, dbo.rw.s1 AS r_s1, 
     case WHEN v_rc_pno.s1 IS NULL THEN 0 else v_rc_pno.s1 end as s1, 
dbo.rw.s1-(case WHEN v_rc_pno.s1 IS NULL THEN 0 else v_rc_pno.s1 end) as x_s1,         

 dbo.rw.s2 AS r_s2, 
     case WHEN v_rc_pno.s2 IS NULL THEN 0 else v_rc_pno.s2 end as s2, 
dbo.rw.s2-(case WHEN v_rc_pno.s2 IS NULL THEN 0 else v_rc_pno.s2 end) as x_s2,         

 dbo.rw.s1 + dbo.rw.s2 AS r_count,
(case WHEN v_rc_pno.s1 IS NULL THEN 0 else v_rc_pno.s1 end)+
   dbo.rw.s2-(case WHEN v_rc_pno.s2 IS NULL THEN 0 else v_rc_pno.s2 end) as y_count,
dbo.rw.s1 + dbo.rw.s2-(case WHEN v_rc_pno.s1 IS NULL THEN 0 else v_rc_pno.s1 end)+
   dbo.rw.s2-(case WHEN v_rc_pno.s2 IS NULL THEN 0 else v_rc_pno.s2 end) as x_count
   
FROM dbo.rw LEFT OUTER JOIN
      dbo.v_rc_pno ON dbo.v_rc_pno.person_id = dbo.rw.person_id LEFT OUTER JOIN
      dbo.person ON dbo.rw.person_id = dbo.person.person_ID
where rw.person_id in (select distinct person_id from rw where acount_id=@acount_id)
) MA WHERE ma.x_s1>800