1 SELECTRECORD.[record_id]2, RECORD.[workflow_info_id]3, RECORD.[Form_code]4, RECORD.[apply_date]5, RECORD.[apply_emp_id]6, RECORD.[Apply_emp_number]7, RECORD.[apply_emp_name_en]8, RECORD.[APPLY_EMP_NAME_CN]9, RECORD.[Apply_emp_email]Ten, RECORD.[Apply_emp_phone] One, RECORD.[Apply_emp_mobile] A, RECORD.[APPLY_EMP_POSITION_CN] -, RECORD.[apply_emp_position_en] -, RECORD.[Apply_emp_work_office] the, RECORD.[Apply_emp_work_area_code] -, RECORD.[Apply_emp_dept_code] -, RECORD.[Apply_emp_dept_type] -, RECORD.[apply_emp_dept_is_functional] +, RECORD.[APPLY_EMP_DEPT_NAME_CN] -, RECORD.[apply_emp_dept_name_en] +, RECORD.[Apply_emp_dept_func_code] A, RECORD.[APPLY_EMP_DEPT_FUNC_CN] at, RECORD.[apply_emp_dept_func_en] -, RECORD.[filler_emp_id] -, RECORD.[Filler_emp_number] -, RECORD.[filler_emp_name_en] -, RECORD.[FILLER_EMP_NAME_CN] -, RECORD.[Filler_emp_email] in, RECORD.[Filler_emp_phone] -, RECORD.[Filler_emp_mobile] to, RECORD.[FILLER_EMP_POSITION_CN] +, RECORD.[filler_emp_position_en] -, RECORD.[Filler_emp_work_office] the, RECORD.[Filler_emp_work_area_code] *, RECORD.[Filler_emp_dept_code] $, RECORD.[Filler_emp_dept_type]Panax Notoginseng, RECORD.[filler_emp_dept_is_functional] -, RECORD.[FILLER_EMP_DEPT_NAME_CN] the, RECORD.[filler_emp_dept_name_en] +, RECORD.[FILLER_EMP_DEPT_FUNC_CN] A, RECORD.[filler_emp_dept_func_en] the, RECORD.[Filler_emp_dept_func_code] + , EMP. approve_emp_id - , EMP. Approve_type $ , EMP. EMP_NAME_CN $ , EMP. Emp_name_en - , EMP. Emp_number - , EMP. SOURCE_EMP_NAME_CN the , EMP. Source_emp_name_en - , EMP. Source_emp_numberWuyi, Activity.activity_name--Activity name the , Activity.meeting_category_name - , Activity.meeting_category_code Wu , Activity.activity_join_number - , Activity.start_time About , Activity.finish_time $ , Activity.fee -, Activity.[Activity_region_name] -, Case when(EMP. Wf_activity_name is NULL) Then -( Case A when(RECORD. Record_status=0) Then 'Draft' + when(RECORD. Record_status=2) Then 'completed' the when(RECORD. Record_status=4) Then 'Circulation' - when(RECORD. Record_status=5) Then 'Cancelled' $ when(RECORD. Record_status=7) Then 'Suspend' ELSE 'Unknown' END) the ELSEEmp. Wf_activity_nameEND asCurrent_activity_name the from [dbo].[Tstb_com_ms_item_record]RECORD the Left JOIN [dbo]. Fun_ms_getactivityinfo () Activity the onRECORD. record_id=activity.record_id - Left JOIN in(SELECT *, Row_number () Over(PARTITION byrecord_idORDER byCreate_date) asRn from [dbo].[tstb_com_item_record_approve_emp] WHERESTATUS=1 andApprove_type<>2 andWorkflow_prefix='MS') the asEmp onRECORD. record_id=Emp. record_id andEmp. Rn=1 the WHERERECORD. STATUS=1 andRECORD. Record_status<>5
SQL Server grouping with RowNumber