---涂聚文 2017-9-28
SELECT VipExamMailProjectId,VipExamMailStaffID FROM VipExamMailRecord
WHERE
VipExamMailProjectId = 1
GROUP BY VipExamMailStaffID,VipExamMailProjectId HAVING COUNT(1)>1
ORDER BY VipExamMailStaffID
GO
--192
SELECT * FROM
(SELECT BranchStaffID,BranchStaffCountryID,BranchStaffIs,BranchStaffArea,BranchStaffCode,BranchStaffNO,BranchStaffName,BranchStaffServerNo FROM VipBranchStaff) a
LEFT JOIN (SELECT VipExamMailProjectId,VipExamMailStaffID FROM VipExamMailRecord WHERE VipExamMailProjectId = 1
GROUP BY VipExamMailStaffID,VipExamMailProjectId HAVING COUNT(1)>1) b
ON a.BranchStaffID = b.VipExamMailStaffID
WHERE a.BranchStaffCountryID=1
GO
--153 香港
SELECT * FROM VipBranchStaff WHERE BranchStaffCountryID=1 AND BranchStaffIs=1
GO
--View_VipExamMailRecordProject
SELECT a.*,b.VipExamMailProjectId AS 'ExamProjectId',[dbo].getVipExamBranchSimpleName(a.BranchStaffCode) as 'BranchAreaName',[dbo].getAreaName(a.BranchStaffArea) as 'AreaName',dbo.getVipExamBrandServiceName(dbo.getVipExamBrandServiceNo(a.BranchStaffCode)) AS 'ServerName',
dbo.getVipExamBrandServiceNo(a.BranchStaffCode) AS 'BrandService',[dbo].getVipExamQuestionsIs (b.VipExamMailProjectId,a.BranchStaffID) as 'IsSucces',dbo.getStaffProjectTimes(b.VipExamMailProjectId,a.BranchStaffID) AS 'ExamTimes',dbo.getVipExamMailDate(b.VipExamMailProjectId,a.BranchStaffID) AS 'VipExamMailDate'
FROM
(SELECT BranchStaffID,BranchStaffCountryID,BranchStaffIs,BranchStaffArea,BranchStaffCode,BranchStaffNO,BranchStaffName,BranchStaffServerNo FROM VipBranchStaff WHERE BranchStaffCountryID=1 AND BranchStaffIs=1) a
LEFT JOIN (SELECT VipExamMailProjectId,VipExamMailStaffID FROM VipExamMailRecord WHERE VipExamMailProjectId = 1
GROUP BY VipExamMailStaffID,VipExamMailProjectId HAVING COUNT(1)>1) b
ON a.BranchStaffID = b.VipExamMailStaffID
--WHERE a.BranchStaffCountryID=1
GO
--国内
--2425
SELECT * FROM VipBranchStaff WHERE BranchStaffCountryID=2 AND BranchStaffIs=1
GO
SELECT a.*,b.VipExamMailProjectId AS 'ExamProjectId',[dbo].getVipExamBranchSimpleName(a.BranchStaffCode) as 'BranchAreaName',[dbo].getAreaName(a.BranchStaffArea) as 'AreaName',dbo.getVipExamBrandServiceName(dbo.getVipExamBrandServiceNo(a.BranchStaffCode)) AS 'ServerName',
dbo.getVipExamBrandServiceNo(a.BranchStaffCode) AS 'BrandService',[dbo].getVipExamQuestionsIs (b.VipExamMailProjectId,a.BranchStaffID) as 'IsSucces',dbo.getStaffProjectTimes(b.VipExamMailProjectId,a.BranchStaffID) AS 'ExamTimes' FROM
(SELECT BranchStaffID,BranchStaffCountryID,BranchStaffIs,BranchStaffArea,BranchStaffCode,BranchStaffNO,BranchStaffName,BranchStaffServerNo FROM VipBranchStaff WHERE BranchStaffCountryID=2 AND BranchStaffIs=1) a
LEFT JOIN (SELECT VipExamMailProjectId,VipExamMailStaffID FROM VipExamMailRecord WHERE VipExamMailProjectId =2
GROUP BY VipExamMailStaffID,VipExamMailProjectId HAVING COUNT(1)>1) b
ON a.BranchStaffID = b.VipExamMailStaffID
GO
SELECT a.*,b.VipExamMailProjectId AS 'ExamProjectId',[dbo].getVipExamBranchSimpleName(a.BranchStaffCode) as 'BranchAreaName',[dbo].getAreaName(a.BranchStaffArea) as 'AreaName',dbo.getVipExamBrandServiceName(dbo.getVipExamBrandServiceNo(a.BranchStaffCode)) AS 'ServerName',
dbo.getVipExamBrandServiceNo(a.BranchStaffCode) AS 'BrandService',[dbo].getVipExamQuestionsIs (b.VipExamMailProjectId,a.BranchStaffID) as 'IsSucces',dbo.getStaffProjectTimes(b.VipExamMailProjectId,a.BranchStaffID) AS 'ExamTimes',dbo.getVipExamMailDate(b.VipExamMailProjectId,a.BranchStaffID) AS 'VipExamMailDate' FROM
(SELECT BranchStaffID,BranchStaffCountryID,BranchStaffIs,BranchStaffArea,BranchStaffCode,BranchStaffNO,BranchStaffName,BranchStaffServerNo FROM VipBranchStaff WHERE BranchStaffCountryID=2 AND BranchStaffIs=1) a
LEFT JOIN (SELECT VipExamMailProjectId,VipExamMailStaffID FROM VipExamMailRecord WHERE VipExamMailProjectId =2
GROUP BY VipExamMailStaffID,VipExamMailProjectId HAVING COUNT(1)>1) b
ON a.BranchStaffID = b.VipExamMailStaffID
WHERE dbo.getVipExamBrandServiceNo(a.BranchStaffCode)='013180'
ORDER BY a.BranchStaffCode
GO
--2017-09-27修改 合格详情列表
IF EXISTS (select * from sysobjects where [name] = '[proc_Select_HKQualifiedNoDetails]')
DROP PROCEDURE proc_Select_HKQualifiedNoDetails
GO
CREATE PROCEDURE [dbo].proc_Select_HKQualifiedNoDetails
(
@ProjectID int,
@CountryID int,
@BrandService VARCHAR(50),
@search nvarchar(100)
)
as
declare @sql nvarchar(4000),@where nvarchar(4000)
set @sql='SELECT a.*,b.VipExamMailProjectId AS ''ExamProjectId'',[dbo].getVipExamBranchSimpleName(a.BranchStaffCode) as ''BranchAreaName'',[dbo].getAreaName(a.BranchStaffArea) as ''AreaName'',dbo.getVipExamBrandServiceName(dbo.getVipExamBrandServiceNo(a.BranchStaffCode)) AS ''ServerName'',
dbo.getVipExamBrandServiceNo(a.BranchStaffCode) AS ''BrandService'',[dbo].getVipExamQuestionsIs (b.VipExamMailProjectId,a.BranchStaffID) as ''IsSucces'',dbo.getStaffProjectTimes(b.VipExamMailProjectId,a.BranchStaffID) AS ''ExamTimes'',dbo.getVipExamMailDate(b.VipExamMailProjectId,a.BranchStaffID) AS ''VipExamMailDate'' FROM
(SELECT BranchStaffID,BranchStaffCountryID,BranchStaffIs,BranchStaffArea,BranchStaffCode,BranchStaffNO,BranchStaffName,BranchStaffServerNo FROM VipBranchStaff WHERE BranchStaffIs=1 and BranchStaffCountryID='+ cast(@CountryID as varchar)+') a'
+' LEFT JOIN (SELECT VipExamMailProjectId,VipExamMailStaffID FROM VipExamMailRecord WHERE VipExamMailProjectId ='+ cast(@ProjectID as varchar)+' GROUP BY VipExamMailStaffID,VipExamMailProjectId HAVING COUNT(1)>1) b ON a.BranchStaffID = b.VipExamMailStaffID'
set @where=''
IF @BrandService<>'' AND @Search=''
SET @where=@where+' where dbo.getVipExamBrandServiceNo(a.BranchStaffCode) like ''%'+@BrandService +'%'''
if @Search<>'' AND @BrandService=''
begin
set @where=@where+' where (a.BranchStaffCode like ''%'+@search +'%'' or [dbo].getVipExamBranchSimpleName(a.BranchStaffCode) like ''%'+@search +'%'' or [dbo].getAreaName(a.BranchStaffArea) like ''%'+@search +'%'' or dbo.getVipExamBrandServiceName(dbo.getVipExamBrandServiceNo(a.BranchStaffCode)) like ''%'+@search +'%'' or [dbo].getVipExamQuestionsIs (b.VipExamMailProjectId,a.BranchStaffID) like ''%'+@search +'%'' or dbo.getVipExamBrandServiceNo(a.BranchStaffCode) like ''%'+@search +'%'')'
END
IF @BrandService<>'' AND @Search<>''
BEGIN
SET @where=@where+' where dbo.getVipExamBrandServiceNo(a.BranchStaffCode) like ''%'+@BrandService +'%'''
set @where=@where+' and (a.BranchStaffCode like ''%'+@search +'%'' or [dbo].getVipExamBranchSimpleName(a.BranchStaffCode) like ''%'+@search +'%'' or [dbo].getAreaName(a.BranchStaffArea) like ''%'+@search +'%'' or dbo.getVipExamBrandServiceName(dbo.getVipExamBrandServiceNo(a.BranchStaffCode)) like ''%'+@search +'%'' or [dbo].getVipExamQuestionsIs (b.VipExamMailProjectId,a.BranchStaffID) like ''%'+@search +'%'' or dbo.getVipExamBrandServiceNo(a.BranchStaffCode) like ''%'+@search +'%'')'
END
set @sql=@sql+@where+' order by ExamTimes'
print @sql
exec(@sql)
GO
EXEC proc_Select_HKQualifiedNoDetails 1,1,'',''
GO