Stored proc has "pivot"? what does this do? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Stored proc has "pivot"? what does this do?

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-09-28 : 09:23:29

IN this stored proc, there is statement with 'Pivot' (see PIV)

I am wondering if this is causing the problem I am having which is when I pass FROM and TO dates, starting with the current date, it returns rows with date from one week ago. This SP I was told will return for a medical facility those patients who missed any medications order by date.

ALTER PROCEDURE [dbo].[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)
)

AS
BEGIN
SELECT 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,
SUBSTRING(M.ROOM_BED, 1, CHARINDEX(' ', M.ROOM_BED) - 1) ROOM,
CONVERT(VARCHAR, DTR.DATE_ORDER, 110) + ' '
+ 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 OGEN.DATEONLY(@DATEFROM + TEMP.PIV) DATE_ORDER, DATEPART(dd, @DATEFROM + TEMP.PIV) DAY_ORDER
FROM ( SELECT TOP ( DATEDIFF(dd, @DATEFROM, @DATETHRU) + 1 )
ROW_NUMBER() OVER ( ORDER BY DX_KEY ) PIV
FROM OGEN.GEN_C_DX) TEMP
WHERE @DATEFROM + TEMP.PIV <= @DATETHRU) 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
)

LEFT OUTER JOIN OPTC.ORD_D_ADMIN_LOG AL ON AM.ORDER_KEY = AL.ORDER_KEY
--AG AND OGEN.DATEONLY(AL.ADMIN_DATETIME) = DTR.DATE_ORDER
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 ( @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 IN (SELECT Value FROM dbo.ListToTable(@FACILITYKEY,',')) AND
( 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
--AG AND OGEN.DATEONLY(AL1.ADMIN_DATETIME) = DTR.DATE_ORDER
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

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-28 : 09:31:57

It's not a PIVOT. It's an integer. Look at this line:

ROW_NUMBER() OVER ( ORDER BY DX_KEY ) PIV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 10:43:10

it looks like a dynamic date offset to me based on DX_KEY field

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources