|
AdamWest
Constraint Violating Yak Guru
360 Posts |
 Posted - 2012-01-19 : 16:29:38
|
| I would like to know if in this infamous sp, I can not accept records where the datetime is greater than the current time.ALTER PROCEDURE [OGEN].[DBD_GET_MEDICINE_DETAIL] ( @FACILITYKEY varchar(1000), @DATEFROM datetime, @DATETHRU datetime, @UNITSTR VARCHAR(100), @INCLUDEDISCH NUMERIC(1, 0), @PATNUMBER NUMERIC(9, 0), @MEDNAME VARCHAR(100) )ASBEGINSELECT @UNITSTR = isnull(@UNITSTR,'') SELECT AM.FACILITY_KEY FACILITY , M.UNIT_CODE UNIT , RTRIM(M.LAST_NAME) + CASE WHEN RTRIM(M.FIRST_NAME) <> '' THEN ', ' ELSE '' END + RTRIM(M.FIRST_NAME) PATIENT_NAME , CASE WHEN M.NURSING_UNIT is not null THEN M.NURSING_UNIT ELSE '' END NURSING_UNIT , CASE WHEN M.UNIT_CODE is not null THEN M.UNIT_CODE ELSE '' END UNIT_CODE , CASE WHEN M.ROOM_BED is not null THEN SUBSTRING(M.ROOM_BED, 1, CHARINDEX(' ', M.ROOM_BED) - 1) ELSE '' END ROOM , CASE WHEN M.ROOM_BED is not null THEN SUBSTRING(M.ROOM_BED, 1, CHARINDEX(' ', M.ROOM_BED) - 1) ELSE '' END ROOM,CASE WHEN M.ROOM_BED LIKE '% %' THEN SUBSTRING(M.ROOM_BED, 1, CHARINDEX(' ', M.ROOM_BED) - 1) ELSE '' END ROOM ,(COALESCE(CONVERT(VARCHAR(36), DTR.DATE_ORDER, 110) + ' ','') + COALESCE(SUBSTRING(CONVERT(VARCHAR, AM.ADMIN_TIME, 100), 13, 7),'')) ADMIN_TIME , AM.ORDER_NAME , AM.DOSAGE , AM.DOSAGE_FORM , CASE WHEN AL.ADMIN_RESULT = -9 THEN 'Y' ELSE '' END NOTE_ENTERED , AM.ORDER_KEY FROM (SELECT TOP(DATEDIFF(DD, @DATEFROM, @DATETHRU) + 1) OGEN.DATEONLY(@DATEFROM) + ROW_NUMBER() OVER (ORDER BY DX_KEY) - 1 DATE_ORDER, ROW_NUMBER() OVER(ORDER BY DX_KEY) DAY_ORDER FROM OGEN.GEN_C_DX ORDER BY DX_KEY) DTR INNER JOIN OPTC.ORD_M_ADMIN AM ON DTR.DATE_ORDER + ( AM.ADMIN_TIME - OGEN.DATEONLY(AM.ADMIN_TIME) ) BETWEEN AM.START_DATE AND AM.END_DATE AND (AM.START_DATE BETWEEN @DATEFROM AND @DATETHRU OR AM.END_DATE BETWEEN @DATEFROM AND @DATETHRU OR (AM.START_DATE < @DATEFROM AND AM.END_DATE > @DATETHRU)) AND AM.FACILITY_KEY IN (SELECT VALUE FROM [OGEN].[COMMA_TO_TABLE](@FACILITYKEY)) AND ( @PATNUMBER = -1 OR AM.PAT_NUMBER = @PATNUMBER ) AND ( @MEDNAME IS NULL OR @MEDNAME = '' OR AM.ORDER_NAME LIKE '%' + @MEDNAME + '%' ) AND AM.PRN = 0 LEFT OUTER JOIN OPTC.ORD_D_ADMIN_LOG AL ON AM.ORDER_KEY = AL.ORDER_KEY AND OGEN.DATEONLY(AL.ADMIN_TIME) = DTR.DATE_ORDER AND AL.ADMIN_TIME - OGEN.DATEONLY(AL.ADMIN_TIME) = AM.ADMIN_TIME - OGEN.DATEONLY(AM.ADMIN_TIME) LEFT OUTER JOIN OPTC.ORD_C_ADMIN_RESULT AR ON AL.ADMIN_RESULT = AR.RESULT_KEY INNER JOIN OGEN.GEN_M_PATIENT_MAST M ON AM.PAT_NUMBER = M.PAT_NUMBER AND AM.FACILITY_KEY = M.FACILITY_KEY AND M.FACILITY_KEY IN (SELECT VALUE FROM [OGEN].[COMMA_TO_TABLE](@FACILITYKEY)) AND ( @UNITSTR IS NULL OR @UNITSTR = '' OR CHARINDEX(M.UNIT_CODE, @UNITSTR) % 2 = 1 ) LEFT OUTER JOIN OGEN.GEN_M_DOCTOR_MAST DR ON M.PRIMARY_DOCTOR_KEY = DR.DOCTOR_KEY AND M.FACILITY_KEY = DR.FACILITY_KEY WHERE --AM.FACILITY_KEY = @FACILITYKEY ( AM.PAT_NUMBER = @PATNUMBER OR @PATNUMBER = -1 ) AND ( @MEDNAME IS NULL OR @MEDNAME = '' OR AM.ORDER_NAME LIKE ( '%' + @MEDNAME + '%' ) ) and AM.PRN = 0 AND OPTC.ORD_IS_ADMIN(AM.START_DATE, DTR.DATE_ORDER, AM.DAYS_REQ) = 1 AND NOT EXISTS ( SELECT AL1.ORDER_KEY FROM OPTC.ORD_D_ADMIN_LOG AL1 WHERE AL1.ORDER_KEY = AM.ORDER_KEY AND OGEN.DATEONLY(AL1.ADMIN_TIME) = DTR.DATE_ORDER AND AL1.ADMIN_TIME - OGEN.DATEONLY(AL1.ADMIN_TIME) = AM.ADMIN_TIME - OGEN.DATEONLY(AM.ADMIN_TIME) AND CHARINDEX(CAST(ABS(AL1.ADMIN_RESULT) AS VARCHAR(2)), '01234') > 0 ) AND NOT EXISTS ( SELECT OM.ORDER_KEY FROM OPTC.ORD_M_ORDER OM WHERE OM.ORDER_STATUS = 0 AND OM.HOLD_ON_FROM = AM.ORDER_KEY AND DTR.DATE_ORDER BETWEEN OM.START_DATE AND OM.END_DATE ) AND ( @INCLUDEDISCH = 0 OR 1 = CASE WHEN @INCLUDEDISCH = 1 AND M.DISCHARGE_DATE IS NULL THEN 1 WHEN @INCLUDEDISCH = 2 AND M.DISCHARGE_DATE IS NOT NULL THEN 1 ELSE 0 END ) ORDER BY PATIENT_NAME, M.PAT_NUMBER, DATE_ORDER, AM.ADMIN_TIME - OGEN.DATEONLY(AM.ADMIN_TIME)END |
|
Is it possible to limit records by current datetim