IF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs J JOIN msdb.dbo.sysjobactivity A ON A.job_id=J.job_id WHERE J.name=N'delete resume_x_job_position' AND A.run_requested_date IS NOT NULL AND A.stop_execution_date IS NULL)
    select 'The job is running!'
ELSE
    select 'The job is not running.'

USE [swap_zhaopin]
GO
/****** Object:  StoredProcedure [zhaopin].[PKG_Get_Position_ExportInterfaceBySougouSearch]    Script Date: 11/26/2015 6:51:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  
    
    
-- *************************************************************    
--                     CHANGE LOG                       
-- *************************************************************    
-- Author   : jackson.zhang    
-- Auditor  :     
-- Modified : 2013-07-10     
-- Task No  :   
-- Requirement  :    
-- Purpose  :     
-- Scope :     
-- Risk: [无风险]    
-- Approved by: [审批者]    
-- *************************************************************    
    e
create PROC [zhaopin].[PKG_Get_Position_ExportInterfaceBySougouSearch]    --create为新建，alter为修改
    @ErrorCode BIGINT OUTPUT --错误Flag    
AS     
SET NOCOUNT ON  
SET @ErrorCode = 0    
DECLARE @today DATETIME    
DECLARE @StartDate DATETIME    
SET @today = GETDATE()    
SET @StartDate = DATEADD(d, -1, @today)    
SELECT  
c1.id AS RootCompanyID ,--总公司编号    
c.id AS CompanyID ,--分公司编号    
c1.company_name AS RootCompanyName ,--总公司名称    
c.company_name AS CompanyName ,--分公司名称    
c.intro_url, --公司url  
c1.company_size ,   
c1.company_type ,   
c.industry_id ,    
j.job_title ,    
j.city_id ,    
j.url ,    
j.job_type , --职位大类    
jpst.subjobtype,--职位小类  
j.min_education_level , --学历    
CASE WHEN j.min_years <> -1    
THEN RIGHT(( '000' + CAST(ISNULL(j.min_years, 0) AS VARCHAR) ),    4)    
ELSE CAST(j.min_years AS VARCHAR)    
END AS min_years , --工作经历    
j.date_refresh AS date_start ,    
j.date_end ,    
j.date_refresh ,
CASE WHEN ( ISNULL(j.min_salary, 0) + ISNULL(j.max_salary, 0) ) <= 0    
THEN '0000000000'    
ELSE RIGHT(( '00000' + CAST(ISNULL(j.min_salary, 0) AS VARCHAR) ), 5)   + RIGHT(( '00000'    + CAST(ISNULL(j.max_salary, 0) AS VARCHAR) ),    5)    
END AS salary ,    
j.job_position_number ,    
j.version_number ,    
j.status ,    
j.employment_type,    
j.management_experience_required AS IsShowSalary,
c.c_name,
j.quantity,
ld.cn_name,
c2.company_name AS DepartName --部门名称    
FROM    zhaopin.job_position_partab j WITH ( NOLOCK )    
INNER JOIN zhaopin.company c WITH ( NOLOCK ) ON j.company_id = c.id    
INNER JOIN zhaopin.company c1 WITH ( NOLOCK ) ON c.root_company_id = c1.id  
INNER JOIN zhaopin.company c2 WITH ( NOLOCK ) ON j.department_id = c2.id
LEFT JOIN [zhaopin].[Location_District] ld  with(nolock) ON j.cq_id=ld.cq_id  
INNER JOIN zhaopin.job_position_x_subtype jpst with(nolock)   
on jpst.job_position_number = j.job_position_number   
WHERE j.is_active = 'y'    
AND j.[status] =3  
AND j.is_editable = 'y'    
AND j.date_start BETWEEN @StartDate AND @today 
AND j.date_end> DATEADD(d, 1, @today)