You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
111 lines
3.1 KiB
111 lines
3.1 KiB
INSERT INTO Exam_Result(
|
|
[DID]
|
|
, [DeptName]
|
|
, [FeeItemName]
|
|
, [RptItemName]
|
|
, [TextResult]
|
|
, [Unit]
|
|
, [Range]
|
|
, [UnusualFlag]
|
|
, [ItemClass]
|
|
, [RptItemCode]
|
|
, [Checker]
|
|
, [Reporter]
|
|
, [ReportTime])
|
|
select distinct * from (
|
|
-- ÆÕͨÏîÄ¿
|
|
SELECT -- TOP 20
|
|
X.EnrollmentId AS [DID]
|
|
, X.DepartmentName AS [DeptName]
|
|
, X.Name AS [FeeItemName]
|
|
, Y.Name AS [RptItemName]
|
|
, Y.TextValue AS [TextResult]
|
|
, Y.ValueUnit AS [Unit]
|
|
, [Range]=NULL
|
|
, [UnusualFlag]=NULL
|
|
, CASE X.ItemType WHEN 1 THEN 'ÆÕͨ' ELSE convert(varchar, X.ItemType,10) END AS [ItemClass]
|
|
, [RptItemCode]=NULL
|
|
, [Checker]=NULL
|
|
, [Reporter]=NULL
|
|
, [ReportTime]=NULL
|
|
FROM
|
|
PEIS.DBO.EnrollmentFeeItem X
|
|
JOIN PEIS.DBO.EnrollmentExamItem Y ON X.Id = Y.EnrollmentFeeItemId
|
|
WHERE
|
|
X.SendOperDate IS NOT NULL AND X.RemoveOperDate IS NULL AND X.GiveUpOperDate IS NULL
|
|
AND Y.RemoveOperDate IS NULL AND Y.GiveUpOperDate IS NULL
|
|
-- ¼ìÑéÏîÄ¿
|
|
UNION
|
|
SELECT -- TOP 20
|
|
A.EnrollmentId AS [DID]
|
|
,A.DepartmentName AS [DeptName]
|
|
,A.Name AS [FeeItemName]
|
|
,B.Name AS [RptItemName]
|
|
,B.TextValue AS [TextResult]
|
|
,B.ValueUnit AS [Unit]
|
|
,A.ItemType AS [Range]
|
|
,B.LisFlag AS [UnusualFlag]
|
|
|
|
, CASE a.ItemType WHEN 3 THEN '¼ìÑé' ELSE convert(varchar, a.ItemType,10) END AS [ItemClass]
|
|
,B.LisCode AS [RptItemCode]
|
|
, [Checker]=NULL
|
|
,B.LisVerifyOperName AS [Reporter]
|
|
,B.LisVerifyOperDate AS [ReportTime]
|
|
FROM
|
|
PEIS.DBO.EnrollmentApplicationSheetFeeItem A
|
|
JOIN PEIS.DBO.EnrollmentExamItem B ON A.Id = B.EnrollmentApplicationSheetFeeItemId
|
|
WHERE
|
|
A.SendOperDate IS NOT NULL AND A.RemoveOperDate IS NULL AND A.GiveUpOperDate IS NULL
|
|
AND B.RemoveOperDate IS NULL AND B.GiveUpOperDate IS NULL
|
|
-- ¼ì²éÏîÄ¿
|
|
UNION
|
|
SELECT -- TOP 20
|
|
A.EnrollmentId AS [DID]
|
|
,A.DepartmentName AS [DeptName]
|
|
,B.Name AS [FeeItemName]
|
|
,A.NAME AS [RptItemName]
|
|
,A.TextValue AS [TextResult]
|
|
, [Unit]=NULL
|
|
, [Range]=NULL
|
|
, [UnusualFlag]=NULL
|
|
, [ItemClass]='¼ì²é'
|
|
, [RptItemCode]=NULL
|
|
, [Checker]=NULL
|
|
,A.PacsVerifyOperName [Reporter]
|
|
,A.PacsVerifyOperDate AS [ReportTime]
|
|
FROM
|
|
PEIS.DBO.EnrollmentApplicationSheet A
|
|
LEFT JOIN PEIS.DBO.EnrollmentApplicationSheetFeeItem B ON A.ID= B.EnrollmentApplicationSheetId
|
|
WHERE
|
|
A.TextValue IS NOT NULL AND A.RemoveOperDate IS NULL AND B.RemoveOperDate IS NULL
|
|
) T ORDER BY DID,DeptName,FeeItemName
|
|
|
|
|
|
|
|
|
|
|
|
-- ¸üпÆÊÒcode
|
|
UPDATE EXAM_PART SET DEPTCODE=T.HISCODE FROM
|
|
(
|
|
SELECT Name ,HISCode FROM DICT_DEPT) T
|
|
WHERE T.NAME=EXAM_PART.DeptName AND EXAM_PART.DeptCode=0141
|
|
|
|
-- ¸üÐÂfid,rid,sid
|
|
UPDATE Exam_Result SET FID=T.FID FROM
|
|
(SELECT ID AS FID, FeeItemName FROM vi_FeeItem)T WHERE Exam_Result.FeeItemName=T.FeeItemName
|
|
|
|
UPDATE Exam_Result SET rid=T.RID FROM
|
|
(SELECT A.ID AS FID,A.FeeItemName,B.ID AS RID,B.RptItemName FROM vi_FeeItem a
|
|
left join Dict_ReportItem b ON A.ID=B.FID )T WHERE Exam_Result.fid=T.fid AND Exam_Result.RptItemName=T.RptItemName
|
|
|
|
-- PID
|
|
UPDATE Exam_Result
|
|
SET PID = A.ID
|
|
FROM Exam_Part A
|
|
WHERE A.EID = Exam_Result.DID
|
|
AND A.DeptName = Exam_Result.DeptName;
|
|
|
|
|
|
-- ÊÕ·ÑÏîÄ¿
|
|
UPDATE Enrollment_FeeItem SET FID=T.FID FROM
|
|
(SELECT ID AS FID, FeeItemName FROM vi_FeeItem)T WHERE Enrollment_FeeItem.FeeItemName=T.FeeItemName
|
|
|