--查看一周随访的详情70007
PROCEDURE GET_PATIENT_WEEKFU
(
userId in NUMERIC,
time in date,
V_LIST OUT MYCURSOR
)IS
BEGIN
OPEN V_LIST FOR
SELECT ANSWERTEXT,
OPTIONID,
SCORE,
QTYPE,
QID,
CREATETIMES,
SEQUENCE,
QTYPENAME AS QTYPENAME,
NLOWVALUE,
NHIGVALUE,
UNIT
FROM (SELECT row_number() over(partition by to_char(A.CREATE_TIME,'yyyy-mm-dd') order by A.CREATE_TIME desc) RN,
A.ANSWER_TEXT AS ANSWERTEXT,
A.OPTION_ID AS OPTIONID,
A.SCORE,
A.QTYPE_ID AS QTYPE,
A.QUESTION_ID AS QID,
A.CREATE_TIME AS CREATETIMES,
A.SEQUENCE,
B.QTYPE_NAME AS QTYPENAME,
AST.NOR_LOW_VALUE AS NLOWVALUE,
AST.NOR_HIGH_VALUE AS NHIGVALUE,
AST.UNIT AS UNIT
FROM T_ANSWER_RESULT A
LEFT JOIN T_QUE_TYPE B ON A.QTYPE_ID = B.QTYPE_ID
LEFT JOIN T_ANS_STANDARD AST ON AST.QUESTION_ID = A.QUESTION_ID
where A.PATIENT_ID=userId AND A.CREATE_TIME > to_date(to_char(TRUNC(time-7),'yyyy-mm-dd') || '00:00:00','yyyy-mm-dd hh24:mi:ss')
AND A.CREATE_TIME < to_date(to_char(TRUNC(time+1),'yyyy-mm-dd') || '00:00:00','yyyy-mm-dd hh24:mi:ss')
)
order by QID;