How to not pass rows - 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 not pass rows

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-11-16 : 17:15:54

I have the following Query, which I would like to pass records on the condition of 'NOT FOUND' but I don't want any other to pass.
I was not sure how to code this. Where I have 'ELSE 'NOT STARTED - LATE' if I put '' it just blanks out that column but keeps the row, I would rather don't pass anything at all in that case. the rest of the sp is not of interest at that point.

GO
/****** Object: StoredProcedure [OEN].[DBD_GET_SCHEDULE_NOT_STARTED] Script Date: 11/16/2011 17:06:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
case "NOT STARTED":
storeName = "OEN.DBD_GET_SCHEDULE_NOT_STARTED";
*/

ALTER PROCEDURE [OEN].[DBD_GET_SCHEDULE_NOT_STARTED]
@FACILITYKEY VARCHAR(1000),
@UNITSTR VARCHAR(100),
@FromDate DATETIME,
@ToDate DATETIME
AS
BEGIN
( SELECT S.FACILITY_KEY,
P.PATIENT_ID,
S.A3A_DATE_USER,
RTRIM(P.LAST_NAME)
+ CASE WHEN RTRIM(P.FIRST_NAME) <> '' THEN ', '
ELSE ''
END + RTRIM(P.FIRST_NAME) PATIENT_NAME,
CASE WHEN P.NURSING_UNIT IS NOT NULL THEN P.NURSING_UNIT
ELSE ''
END NURSING_UNIT,
CASE WHEN P.UNIT_CODE IS NOT NULL THEN P.UNIT_CODE
ELSE ''
END UNIT_CODE,
CASE WHEN S.A3A_DATE_USER > GETDATE()
THEN 'NOT STARTED'
ELSE
'NOT STARTED - LATE'

-- CASE WHEN S.A3A_DATE_USER < GETDATE()
-- THEN 'NOT STARTED - LATE'
-- ELSE 'NOT STARTED'

END ASSESSMENTS,
OGEN.TRIM(( CASE WHEN S.MDS_REASON <> '99'
THEN S.OBRA_REASON_DESC + ' '
ELSE ' '
END )
+ ( CASE WHEN S.SPECIAL_REASON <> '99'
AND S.SPECIAL_REASON <> '07'
THEN S.PPS_REASON_DESC + ' '
ELSE ' '
END )
+ ( CASE WHEN S.PPS_OMRA_REASON <> '0'
THEN S.PPS_OMRA_REASON_DESC
ELSE ' '
END )) AS BASE_REASON,
CASE WHEN S.ED_REASON <> '99' THEN S.ED_REASON_DESC
ELSE ' '
END AS TRACK_DESC
FROM [OPTC].MDS_M_SCHEDULE S
LEFT JOIN OPTC.MD3_M_MAST M ON S.PAT_NUMBER = M.PAT_NUMBER
AND S.A3A_DATE_USER = M.REFERENCE_DATE
AND S.MDS_REASON = M.OBRA_REASON
AND S.SPECIAL_REASON = M.PPS_REASON
AND S.PPS_OMRA_REASON = M.PPS_OMRA_REASON
AND S.ED_REASON = M.ED_REASON
LEFT JOIN OGEN.GEN_M_PATIENT_MAST P ON S.PAT_NUMBER = P.PAT_NUMBER
WHERE --S.PAT_NUMBER = M.PAT_NUMBER AND
S.A3A_DATE_USER BETWEEN @FromDate AND @ToDate
AND S.FACILITY_KEY IN (
SELECT Value
FROM dbo.ListToTable(@FACILITYKEY, ',') )
AND ( @UNITSTR IS NULL
OR @UNITSTR = ''
OR CHARINDEX(P.UNIT_CODE, @UNITSTR) % 2 = 1
)
)
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 00:15:09

sounds like what you need is a filter like


S.A3A_DATE_USER <= GETDATE()

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources