How to turn around this sp? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to turn around this sp?

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-12-28 : 21:46:03

this sp will return data that shows the patients in a medical facility who have 'declined', that they cannot suddenly do something, like walk.

Now I am told to have this SP 'do the opposite' and it will show opposite data, i.e. patients who got better, *can* now walk.

Do you see any where in the sp where this can be changed to present the 'opposite' view?

CREATE PROCEDURE [OPTC].[THS_GET_DECLINES_second_value](
@P_FACILITY_KEY CHAR(4),
@P_SDT DATETIME,
@P_EDT DATETIME,
@P_UNIT_CODE CHAR(2),
@P_PAT_NUMBER NUMERIC(9) = NULL,
@P_MENU_GROUP CHAR(38),
@P_INCLUDE_CP NUMERIC(1),
@P_THRESHOLD NUMERIC(9))
AS
BEGIN
/*
--
*/

SET NOCOUNT ON

DECLARE
@L_ARCH NUMERIC(1),
@L_SPLIT_DATE DATETIME,
@L_SHIFT_FACILITY_KEY CHAR(4),
@L_LAST_TIME NUMERIC(12, 6),
@L_LAST_DATE AS DATETIME,
@L_SDT DATETIME,
@L_EDT DATETIME;

SELECT @L_SPLIT_DATE = NULLIF(SPLIT_DATE, CONVERT(DATETIME, '20000101', 112)) + 1
FROM OGEN.GEN_P_ARCHIVE
WHERE FACILITY_KEY = @P_FACILITY_KEY;

IF @@ROWCOUNT = 0
SET @L_SPLIT_DATE = CONVERT(DATETIME, '20000102', 112);

EXEC OGEN.GET_CODE_FACILITY @P_FACILITY_KEY, 'OGEN.GEN_C_SHIFT', 'BASE', @L_SHIFT_FACILITY_KEY OUTPUT;

SELECT @L_SDT = MIN(SH.START_TIME), @L_EDT = MAX(SH.END_TIME)
FROM OGEN.GEN_C_SHIFT SH
WHERE SH.FACILITY_KEY = @L_SHIFT_FACILITY_KEY;

SET @L_EDT = DATEDIFF(dd, 0, DATEADD(dd, 0, @P_EDT)) + (@L_EDT - DATEDIFF(dd, 0, DATEADD(dd, 0, @L_EDT))) + CASE WHEN @L_SDT > @L_EDT THEN 1 ELSE 0 END;
SET @L_SDT = DATEDIFF(dd, 0, DATEADD(dd, 0, @P_SDT)) + (@L_SDT - DATEDIFF(dd, 0, DATEADD(dd, 0, @L_SDT)));

SET @L_ARCH = 0;
IF @L_SPLIT_DATE <= @L_EDT
SET @L_ARCH = @L_ARCH + 1;
IF @L_SPLIT_DATE >= @L_SDT
SET @L_ARCH = @L_ARCH - 1;

SELECT @L_LAST_DATE = MIN(CASE WHEN SH.START_TIME > SH.END_TIME THEN SH.END_TIME ELSE GETDATE() END)
FROM OGEN.GEN_C_SHIFT SH
WHERE SH.FACILITY_KEY = @L_SHIFT_FACILITY_KEY;

SET @L_LAST_TIME = (DATEDIFF(ss, 0, @L_LAST_DATE - DATEDIFF(dd, 0, DATEADD(dd, 0, @L_LAST_DATE))));

WITH TRROWS AS
(SELECT RES.*, CASE WHEN RES.PAT_NUMBER IS NULL THEN 0 ELSE 1 END AS IS_FOUND, ISNULL(CASE WHEN RES.PAT_NUMBER IS NULL THEN 'No Info Found' ELSE 'Didn''t occur' END,'') AS INFO,
ROW_NUMBER() OVER(PARTITION BY RES.PAT_NUMBER, RES.MENU_ID ORDER BY RES.TR_DATETIME) AS RN
FROM (SELECT PM.PAT_NUMBER,
PM.UNIT_CODE,
PM.NURSING_UNIT AS UNIT_NAME,
PM.LAST_NAME,
PM.FIRST_NAME,
PM.PATIENT_ID,
PM.MRN,
PM.ROOM_BED,
TR.SHIFT_CODE,
TR.TR_DATETIME,
DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(ss, -@L_LAST_TIME, TR.TR_DATETIME))) AS TRDATE,
MG.ORDER_NO AS MENU_ID_GROUP_ORDER,
M.ORDER_NO AS MENU_ID_ORDER,
M.LEVEL3_PARENT AS MENU_ID_GROUP,
-- li --------
M.PURPOSE,
M.DESCR_SHORT,
M.SHORT_PATH,
----------
TR.MENU_ID,
TR.MENU_ID_VALUE,
MV.VALUE_CODE,
MV.VALUE AS MENU_ID_VALUE_DESCR,
Isnull(TR.VALUE,'') VALUE,
TR.USER_ID,
CONVERT(VARCHAR,TR.UPDATED_ON,106) UPDATED_ON
FROM OPTC.THS_T_TRANSACTIONS1 TR
JOIN OGEN.GEN_M_PATIENT_MAST PM ON (TR.PAT_NUMBER = PM.PAT_NUMBER)
JOIN OPTC.THS_M_MENU2 M ON (M.MENU_ID = TR.MENU_ID)
JOIN OPTC.THS_M_MENU2 MG ON (MG.MENU_ID = M.LEVEL3_PARENT)
JOIN OPTC.THS_D_MENU_GROUP GR ON (GR.MENU_ID = M.LEVEL3_PARENT)
JOIN OPTC.THS_M_VALUES MV ON (MV.MENU_ID = TR.MENU_ID_VALUE)
WHERE @L_ARCH >= 0
AND TR.FACILITY_KEY = @P_FACILITY_KEY
AND TR.TR_DATETIME BETWEEN @L_SDT AND @L_EDT
AND TR.EDIT_NO < 0
AND (NULLIF(@P_PAT_NUMBER, 0) IS NULL OR TR.PAT_NUMBER = @P_PAT_NUMBER)
AND GR.GROUP_ID = @P_MENU_GROUP
AND (NULLIF(@P_UNIT_CODE, '') IS NULL OR PM.UNIT_CODE = @P_UNIT_CODE)
AND MV.VALUE_TYPE IS NULL
AND MV.VALUE_CODE >= 0
AND M.SUB_SYS_EXT = 'G1'
UNION ALL
SELECT PM.PAT_NUMBER,
PM.UNIT_CODE,
PM.NURSING_UNIT AS UNIT_NAME,
PM.LAST_NAME,
PM.FIRST_NAME,
PM.PATIENT_ID,
PM.MRN,
PM.ROOM_BED,
TR.SHIFT_CODE,
TR.TR_DATETIME,
DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(ss, -@L_LAST_TIME, TR.TR_DATETIME))) AS TRDATE,
MG.ORDER_NO AS MENU_ID_GROUP_ORDER,
M.ORDER_NO AS MENU_ID_ORDER,
M.LEVEL3_PARENT AS MENU_ID_GROUP,
-- --------
M.PURPOSE,
M.DESCR_SHORT,
M.SHORT_PATH,
-----------
TR.MENU_ID,
TR.MENU_ID_VALUE,
MV.VALUE_CODE,
MV.VALUE AS MENU_ID_VALUE_DESCR,
Isnull(TR.VALUE,'') VALUE,
TR.USER_ID,
CONVERT(VARCHAR,TR.UPDATED_ON,106) UPDATED_ON
FROM OPTC.THS_T_TRANSACTIONS_ARCHIVE TR
JOIN OGEN.GEN_M_PATIENT_MAST PM ON (TR.PAT_NUMBER = PM.PAT_NUMBER)
JOIN OPTC.THS_M_MENU2 M ON (M.MENU_ID = TR.MENU_ID)
JOIN OPTC.THS_M_MENU2 MG ON (MG.MENU_ID = M.LEVEL3_PARENT)
JOIN OPTC.THS_D_MENU_GROUP GR ON (GR.MENU_ID = M.LEVEL3_PARENT)
JOIN OPTC.THS_M_VALUES MV ON (MV.MENU_ID = TR.MENU_ID_VALUE)
WHERE @L_ARCH <= 0
AND TR.FACILITY_KEY = @P_FACILITY_KEY
AND TR.TR_DATETIME BETWEEN @L_SDT AND @L_EDT
AND TR.EDIT_NO < 0
AND (NULLIF(@P_PAT_NUMBER, 0) IS NULL OR TR.PAT_NUMBER = @P_PAT_NUMBER)
AND GR.GROUP_ID = @P_MENU_GROUP
AND (NULLIF(@P_UNIT_CODE, '') IS NULL OR PM.UNIT_CODE = @P_UNIT_CODE)
AND MV.VALUE_TYPE IS NULL
AND MV.VALUE_CODE >= 0
AND M.SUB_SYS_EXT = 'G1') RES
-- /*AND M.SUB_SYS_EXT = 'G1'*/) RES
)
SELECT R1.*
FROM TRROWS R1
WHERE EXISTS
(SELECT *
FROM TRROWS R2
WHERE R2.PAT_NUMBER = R1.PAT_NUMBER
AND R2.MENU_ID = R1.MENU_ID
AND R2.RN < R1.RN + @P_THRESHOLD)
AND NOT EXISTS
(SELECT *
FROM TRROWS R2
WHERE R2.PAT_NUMBER = R1.PAT_NUMBER
AND R2.MENU_ID = R1.MENU_ID
AND R2.VALUE_CODE <= R1.VALUE_CODE
AND R2.RN < R1.RN
AND R2.RN > R1.RN + @P_THRESHOLD)
UNION
SELECT R1.*
FROM TRROWS R1
WHERE EXISTS
(SELECT *
FROM TRROWS R2
WHERE R2.PAT_NUMBER = R1.PAT_NUMBER
AND R2.MENU_ID = R1.MENU_ID
AND R2.RN >= R1.RN + @P_THRESHOLD)
AND NOT EXISTS
(SELECT *
FROM TRROWS R2
WHERE R2.PAT_NUMBER = R1.PAT_NUMBER
AND R2.MENU_ID = R1.MENU_ID
AND R2.VALUE_CODE <= R1.VALUE_CODE
AND R2.RN > R1.RN
AND R2.RN <= R1.RN + @P_THRESHOLD);

END;

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-28 : 22:08:00

We don't have enough information to help you as the code doesn't provide any clue as to walking or not. Can't help here without understanding the database design and data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 23:55:33

why dont you post some sample data and explain what you want. read the link below for assistance

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources