SQL岗位信息查询-优快云博客

本文链接：https://blog.youkuaiyun.com/grubby2000/article/details/79015356

SET NOCOUNT ON ;
 DECLARE @epcm_id INT = 183;
 ;WITH effect_post_code AS(
    SELECT d.epcd_post_code FROM HRDB.dbo.thr_emp_postclass_m m 
    INNER JOIN HRDB.dbo.thr_emp_postclass_d d ON d.epcd_epcm_id = m.epcm_id
    WHERE m.epcm_id = @epcm_id 
    AND GETDATE() <= d.epcd_post_enddate
 )  
  SELECT d.epcd_id,d.epcd_post_code, d.epcd_post_mlevel, d.epcd_post_level ,d.epcd_post_key
  , d.epcd_post_name, d.epcd_post_duty, d.epcd_post_ability, epcd_post_begdate = convert ( VARCHAR(10), d.epcd_post_begdate,120),   
 epcd_post_enddate = CONVERT(VARCHAR(10), d.epcd_post_enddate,120) 
   ,wsdm_desc=STUFF(CONVERT(VARCHAR(8000),(
    SELECT TOP 1 ','+b.wsdm_name+'/'+LEFT(a.epcw_wptd_code,2)+CASE WHEN sd.wgsd_user_name<>'' THEN sd.wgsd_user_name ELSE i.wgit_name END
    ,+','+CONVERT(VARCHAR(20),CONVERT(DECIMAL(19,2),HWAG.dbo.rf_wg_GetWsdmItem3(
        wsdm_code
        ,ISNULL(d.epcd_post_begdate,GETDATE())
        ,ISNULL(d.epcd_post_enddate,'9999-12-30')
        ,LEFT(a.epcw_wptd_code,2)
        ,RIGHT(a.epcw_wptd_code,LEN(a.epcw_wptd_code)-2)
      )))
        + CASE WHEN (SELECT COUNT(1) FROM HRDB.dbo.thr_emp_postclass_wstand WHERE epcw_epcd_id=d.epcd_id)>1 THEN ' ...' ELSE '' END 
    FROM HRDB.dbo.thr_emp_postclass_wstand a 
    LEFT JOIN (
        SELECT wsdm_code,wsdm_name,wsdm_wgsm_id,rn=ROW_NUMBER()OVER (PARTITION BY wsdm_code ORDER BY wsdm_version DESC,addtime DESC)
        FROM HWAG.dbo.thr_wage_standard_m  
    )b ON b.wsdm_code=a.epcw_wsdm_code AND b.rn=1
    LEFT JOIN HWAG.dbo.thr_wage_item i ON i.wgit_code = RIGHT(a.epcw_wptd_code,LEN(a.epcw_wptd_code)-2) 
    LEFT JOIN HWAG.dbo.thr_wage_scheme_d sd ON sd.wgsd_wgit_id=i.wgit_id AND sd.wgsd_wgsm_id=b.wsdm_wgsm_id 
    WHERE a.epcw_epcd_id=d.epcd_id ORDER BY a.epcw_id FOR XML PATH('')
  )),1,1,'')
  ,newfalg = ISNULL((select top 1 0 from HRDB.dbo.thr_emp_postclass_d d2 where d.epcd_post_code = d2.epcd_post_code and epcd_epcm_id<>@epcm_id),1)  
  ,nfalg = ISNULL(( SELECT 1 from hwag.dbo.thr_wage_standard_m twsm WHERE twsm.wsdm_code= b.wsdm_code AND twsm.wsdm_status=5   
   AND GETDATE() BETWEEN twsm.wsdm_begtime AND ISNULL(twsm.wsdm_endtime,'9999-01-01')  ),0) 
  FROM HRDB.dbo.thr_emp_postclass_d d 
  INNER JOIN effect_post_code pc ON pc.epcd_post_code = d.epcd_post_code
  WHERE epcd_epcm_id = @epcm_id
  ORDER BY epcd_post_code

 DECLARE @epcm_id INT = 440;
 if OBJECT_ID('tempdb..#effect_post_code') IS NOT NULL DROP TABLE #effect_post_code
    SELECT d.epcd_post_code into #effect_post_code FROM HRDB.dbo.thr_emp_postclass_m m 
    INNER JOIN HRDB.dbo.thr_emp_postclass_d d ON d.epcd_epcm_id = m.epcm_id
    WHERE m.epcm_id = @epcm_id 
    AND GETDATE() <= d.epcd_post_enddate

select epcd_id,epcd_post_code,epcd_post_mlevel,epcd_post_level,epcd_post_key,epcd_post_name,epcd_post_duty,epcd_post_ability,epcd_post_begdate,epcd_post_enddate,newfalg,wsdm_desc,
nfalg=min(nfalg)
 from ( 
  SELECT d.epcd_id,d.epcd_post_code, d.epcd_post_mlevel, d.epcd_post_level ,d.epcd_post_key
  , d.epcd_post_name, d.epcd_post_duty, d.epcd_post_ability, epcd_post_begdate = convert ( VARCHAR(10), d.epcd_post_begdate,120),   
 epcd_post_enddate = CONVERT(VARCHAR(10), d.epcd_post_enddate,120) 
   ,wsdm_desc=''
  -- STUFF(CONVERT(VARCHAR(8000)
  -- ,(
  --    SELECT TOP 1 ','+b.wsdm_name+'/'+LEFT(a.epcw_wptd_code,2)+CASE WHEN sd.wgsd_user_name<>'' THEN sd.wgsd_user_name ELSE i.wgit_name END
  --    ,+','+CONVERT(VARCHAR(20),CONVERT(DECIMAL(19,2),HWAG.dbo.rf_wg_GetWsdmItem3(
  --        wsdm_code
  --        ,ISNULL(d.epcd_post_begdate,GETDATE())
  --        ,ISNULL(d.epcd_post_enddate,'9999-12-30')
  --        ,LEFT(a.epcw_wptd_code,2)
  --        ,RIGHT(a.epcw_wptd_code,LEN(a.epcw_wptd_code)-2)
    --  )))
        --+ CASE WHEN (SELECT COUNT(1) FROM HRDB.dbo.thr_emp_postclass_wstand WHERE epcw_epcd_id=d.epcd_id)>1 THEN ' ...' ELSE '' END 
  --    FROM HRDB.dbo.thr_emp_postclass_wstand a 
  --    LEFT JOIN (
  --        SELECT wsdm_code,wsdm_name,wsdm_wgsm_id,rn=ROW_NUMBER()OVER (PARTITION BY wsdm_code ORDER BY wsdm_version DESC,addtime DESC)
  --        FROM HWAG.dbo.thr_wage_standard_m  
    --)b ON b.wsdm_code=a.epcw_wsdm_code AND b.rn=1
    --LEFT JOIN HWAG.dbo.thr_wage_item i ON i.wgit_code = RIGHT(a.epcw_wptd_code,LEN(a.epcw_wptd_code)-2) 
    --LEFT JOIN HWAG.dbo.thr_wage_scheme_d sd ON sd.wgsd_wgit_id=i.wgit_id AND sd.wgsd_wgsm_id=b.wsdm_wgsm_id 
  --    WHERE a.epcw_epcd_id=d.epcd_id ORDER BY a.epcw_id FOR XML PATH('')
  --)),1,1,'')
  ,newfalg = ISNULL((select top 1 0 from HRDB.dbo.thr_emp_postclass_d d2 where d.epcd_post_code = d2.epcd_post_code and epcd_epcm_id<>@epcm_id),1)  
  , nfalg =ISNULL( (select top(1) 1 from hwag.dbo.thr_wage_standard_m tws 
        where tws.wsdm_status = 5 
        and wsdm_code=ep.epcw_wsdm_code
        and GETDATE() between tws.wsdm_begtime and tws.wsdm_endtime
    ),0)
  FROM HRDB.dbo.thr_emp_postclass_d d 
  INNER JOIN #effect_post_code pc ON pc.epcd_post_code = d.epcd_post_code
  LEFT JOIN HRDB.dbo.thr_emp_postclass_wstand ep on ep.epcw_epcd_id=d.epcd_id  
  WHERE epcd_epcm_id = @epcm_id
  )  a 
  group by epcd_id,epcd_post_code,epcd_post_mlevel,epcd_post_level,epcd_post_key,epcd_post_name,epcd_post_duty,epcd_post_ability,epcd_post_begdate,epcd_post_enddate,newfalg,wsdm_desc
  order  by epcd_post_code


select * from hwag.dbo.thr_wage_standard_m where ISNULL(wsdm_desc,'') <> ''

set nocount on;
declare @mid varchar(20);
set @mid='';
SELECT etpc_part=d.epcd_part,
    pt_desc1=pm.pt_desc1,
    SPL_CODE=epcd_spl_code,
    spl_name=d.epcd_spl_code+'-'+ts.SPL_SHORT_NAME,
    etpc_um=pm.pt_um,
    DOMM_NAME=td.DOMM_NAME,
    etpc_price=(select top(1) etpc_price from r6erp.dbo.tpu_etprice te where etpc_spl_code=epcd_spl_code and  etpc_part=d.epcd_part order by etpc_start desc),
    etpc_start=(select top(1) convert(varchar(10),te.etpc_start,120) from r6erp.dbo.tpu_etprice te where etpc_spl_code=epcd_spl_code and  etpc_part=d.epcd_part order by etpc_start desc),
    d.epcd_remark FROM 
tpu_etprice_exp_d AS d
INNER JOIN pt_mstr AS pm ON pm.pt_part=d.epcd_part AND d.acctid=pm.ACCTID
INNER JOIN TPU_SUPPLIER AS ts ON ts.SPL_CODE=d.epcd_spl_code
INNER JOIN hpur.dbo.v_tba_domain AS td ON td.ACCTID=d.acctid
WHERE d.epcd_epcm_id=@mid
set nocount off;

IF OBJECT_ID('tempdb..#effect_post_code') IS NOT NULL DROP TABLE #effect_post_code
    SELECT d.epcd_post_code into #effect_post_code FROM HRDB.dbo.thr_emp_postclass_m m 
    INNER JOIN HRDB.dbo.thr_emp_postclass_d d ON d.epcd_epcm_id = m.epcm_id
    WHERE m.epcm_id = epcd_epcm_id 
    AND GETDATE() <= d.epcd_post_enddate
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
SELECT d.epcd_post_mlevel, d.epcd_post_level, m.epcm_post_code
    ,epcd_post_name = epcd_post_name + CASE WHEN d.epcd_post_enddate < GETDATE() OR epcm_enable=9 THEN '1' WHEN d.epcd_post_begdate > GETDATE() THEN '2' ELSE '0' END
    ,flag =ISNULL((select top(1) 1 from hwag.dbo.thr_wage_standard_m tws 
    where tws.wsdm_status = 5 
    and  wsdm_code=ep.epcw_wsdm_code
    and GETDATE() between tws.wsdm_begtime and tws.wsdm_endtime),0) 
    into #tmp       
  FROM HRDB.dbo.thr_emp_postclass_d d 
  INNER JOIN #effect_post_code pc ON pc.epcd_post_code = d.epcd_post_code
  LEFT JOIN HRDB.dbo.thr_emp_postclass_wstand ep on ep.epcw_epcd_id=d.epcd_id  
  INNER JOIN  HRDB.dbo.thr_emp_postclass_m m ON d.epcd_epcm_id = m.epcm_id
WHERE epcm_post_property = 2
AND epcm_post_code BETWEEN '101' AND 'P'
AND epcd_post_level BETWEEN '01' AND '20'
        AND GETDATE() BETWEEN epcd_post_begdate AND epcd_post_enddate AND epcm_enable=1
SELECT  *
FROM (    
select epcd_post_mlevel,epcd_post_level,epcm_post_code, epcd_post_name=case c.flag when 1 then '1-'+ epcd_post_name else '0-'+epcd_post_name end from(
select epcd_post_mlevel,epcd_post_level,epcm_post_code, epcd_post_name, flag =(select MIN(flag) from #tmp t where t.epcd_post_level=t1.epcd_post_level and 
t.epcd_post_mlevel=t1.epcd_post_mlevel and t.epcd_post_name=t1.epcd_post_name and t.epcm_post_code=t1.epcm_post_code)
from 
#tmp t1
) c
 ) a
 PIVOT
 (
    max(epcd_post_name)
    FOR epcm_post_code IN ([51],[52],[53],[54],[55],[56],[57],[58],[59],[60],[64],[65],[66],[67],[68],[69],[70],[71],[72],[73],[74],[75],[76],[77],[78],[79],[81],[101],[82],[102],[103],[105],[104],[106],[107],[108],[109],[110],[111],[112],[113],[114],[115],[116],[117],[118],[119],[120],[121],[122],[123],[83],[84],[124],[125],[126],[127],[128],[129],[130],[131],[132],[133],[134],[135],[136],[137],[138],[139],[140],[141],[142],[143],[144],[85],[86],[145],[87],[P])
 ) b 
  ORDER BY epcd_post_mlevel,epcd_post_level

sql server