if (not exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[old_outid]') and objectproperty(id, n'isusertable') = 1))
begin
 select customerid,outid into old_outid from t_customers
 --update  t_customers set outid='請輸入新的學工號'
/* --把新工號恢復成老工號值
update t_customers
set t_customers.outid = old_outid.outid
from old_outid
where old_outid.customerid = t_customers.customerid
*/
end

select *  into #dpt from (select  dpcode1+dpcode2+dpcode3 as 部門代碼,dpname1+'/'+dpname2+'/'+dpname3 as 部門名稱  from t_department where dpname1 is not null and dpname2 is not null and dpname3 is not null union all select dpcode1+dpcode2+dpcode3 as 部門代碼,dpname1+'/'+dpname2 as 部門名稱 from t_department where dpname1 is not null and dpname2 is not null and dpname3 is  null union all select  dpcode1+dpcode2+dpcode3 as 部門代碼,dpname1 as 部門名稱 from t_department where dpname1 is not null and dpname2 is  null and dpname3 is  null) l

select customerid, max(opdt) as 最近一次補卡時間 into #renewcard_maxdt
from t_renewcard
group by customerid

select 部門名稱,name as 姓名, alias as 別名, cardtype as 卡類, c.outid as 新工號,o.outid as 舊工號,最近一次補卡時間
from t_customers c inner join #dpt d on d.部門代碼=c.account inner join #renewcard_maxdt r on
r.customerid=c.customerid inner join old_outid o on c.customerid=o.customerid
where (c.cardtype in (1, 2))

drop table #dpt
drop table #renewcard_maxdt

/* --作視圖代碼，功能同上，不便閱讀理解與修改更新,但適用廣。
select d.部門名稱, c.name as 姓名, c.alias as 別名, c.cardtype as 卡類,
      c.outid as 新工號, o.outid as 舊工號, r.最近一次補卡時間
from t_customers c inner join
          (select dpcode1 + dpcode2 + dpcode3 as 部門代碼,
               dpname1 + '/' + dpname2 + '/' + dpname3 as 部門名稱
         from t_department
         where dpname1 is not null and dpname2 is not null and
               dpname3 is not null
         union all
         select dpcode1 + dpcode2 + dpcode3 as 部門代碼,
               dpname1 + '/' + dpname2 as 部門名稱
         from t_department
         where dpname1 is not null and dpname2 is not null and
               dpname3 is null
         union all
         select dpcode1 + dpcode2 + dpcode3 as 部門代碼, dpname1 as 部門名稱
         from t_department
         where dpname1 is not null and dpname2 is null and dpname3 is null)
      d on d.部門代碼 = c.account inner join
          (select customerid, max(opdt) as 最近一次補卡時間
         from t_renewcard
         group by customerid) r on r.customerid = c.customerid inner join
      old_outid o on c.customerid = o.customerid
where (c.cardtype in (1, 2))
*/