Using Case in a Where clause with a variable

robert693 · April 18, 2018, 1:34pm

Hi,

I have a query that checks if a field is null. If the field is null then it populates with an 'N', if it is not null, it populates with a 'Y'. The query populates a field called RSQ_SCREENED_Y_N with the following case statement: (,case when rsq.CSN is null then 'N' else 'Y' end [RSQ_SCREENED_Y_N]) I also have a variable that is a varchar: @ShoPat. When @ShoPat is set to "No" I want only records where rsq.CSN is populated, in other words RSQ_SCREENED_Y_N will be Y. When @ShoPat is set to "Yes" then I want all records. How can I ask this in a Where clause? Thank you very much in advance for any help!

yosiasz · April 18, 2018, 2:05pm

please provide sample DDL and DML, for example

create table #data(CardId int , PhysicianName varchar(150), Procedure  varchar(150), Facility varchar(150))
insert into #data
select 2096, 'DOE, JOHN', 'SHOULDER ARTHROSCOPY MINI OPEN ROTATOR CUFF', 'EMH MAIN OR' union
select

provide us your own data set

robert693 · April 18, 2018, 2:16pm

Does this help?

DECLARE
@BeginDate date
,@EndDate date
,@ShoPat varchar

--SET @BeginDate = {?BeginDate};
--SET @EndDate = {?EndDate};
--SET @ShoPat = {?ShoPat};
SET @BeginDate = '03/01/2018';
SET @EndDate = '03/31/2018';
SET @ShoPat = 'N';
/*******************************************************/

--Create table for the RSQ Score
IF OBJECT_ID ('TEMPDB.DBO.#RSQ') IS NOT NULL DROP TABLE #RSQ

SELECT DISTINCT
pat.PAT_ID [PATIENT_ID]
,pat.PAT_NAME [PATIENT_NAME]
,pat.PAT_MRN_ID [MRN]
,loc.LOC_NAME [LOCATION]
,dep.DEPARTMENT_NAME [DEPARTMENT]
,meas.MEAS_VALUE [RSQ_SCORE]
,meas.RECORDED_TIME [RSQ_RECORDED_TIME]
,hsp.PAT_ENC_CSN_ID [CSN]
,CONVERT(date, meas.RECORDED_TIME ) [RSQ_RECORDED_DATE]

into #RSQ

FROM PAT_ENC_HSP hsp
INNER JOIN PATIENT pat on pat.PAT_ID = hsp.PAT_ID
INNER JOIN CLARITY_DEP dep on dep.DEPARTMENT_ID = hsp.DEPARTMENT_ID
INNER JOIN CLARITY_LOC loc on loc.LOC_ID = dep.REV_LOC_ID
LEFT JOIN IP_FLWSHT_REC rec on rec.INPATIENT_DATA_ID = hsp.INPATIENT_DATA_ID
LEFT JOIN IP_FLWSHT_MEAS meas on meas.FSD_ID = rec.FSD_ID

WHERE
meas.FLO_MEAS_ID = '3976'
and cast(meas.RECORDED_TIME as date) >= @BeginDate
and cast(meas.RECORDED_TIME as date) <= @EndDate
and meas.MEAS_VALUE is not null
/***************************************************************************/
--Compile report

SELECT DISTINCT
pat.PAT_ID [PATIENT_ID]
,pat.PAT_NAME [PATIENT_NAME]
,pat.PAT_MRN_ID [MRN]
,loc.LOC_NAME [LOCATION]
,dep.DEPARTMENT_NAME [DEPARTMENT]
,rsq.CSN [RSQ_CSN]
,case when rsq.CSN is null then 'N'
else 'Y' end [RSQ_SCREENED_Y_N]
,rsq.RSQ_SCORE [RSQ_SCORE]
,rsq.RSQ_RECORDED_TIME [RSQ_RECORDED_TIME]
,dateadd(ww, datediff(ww, 0, rsq.RSQ_RECORDED_TIME ), -1) [BEGIN_WEEK]
,dateadd(ww, datediff(ww, 0, rsq.RSQ_RECORDED_TIME ), 5) [END_WEEK]
,hsp.PAT_ENC_CSN_ID [CSN]
,CONVERT(date, rsq.RSQ_RECORDED_TIME ) [RSQ_RECORDED_DATE]

FROM PAT_ENC_HSP hsp
INNER JOIN PATIENT pat on pat.PAT_ID = hsp.PAT_ID
INNER JOIN CLARITY_DEP dep on dep.DEPARTMENT_ID = hsp.DEPARTMENT_ID
INNER JOIN CLARITY_LOC loc on loc.LOC_ID = dep.REV_LOC_ID

  LEFT JOIN #RSQ           rsq  on rsq.CSN                  = hsp.PAT_ENC_CSN_ID

WHERE
loc.LOC_ID in ('110000') --SFMC
and cast(hsp.HOSP_ADMSN_TIME as date) >= @BeginDate
and cast(hsp.HOSP_ADMSN_TIME as date) <= @EndDate
--Do I use a CASE Statement here to check @ShoPat?

yosiasz · April 18, 2018, 2:24pm

not really. what you provided is not sample data but a copy of some code. for example we do not have access to your table named PAT_ENC_HSP . please provide sample for example like this

create table #data(CardId int , PhysicianName varchar(150), Procedure  varchar(150), 
Facility varchar(150))

insert into #data
select 2096, 'DOE, JOHN', 'SHOULDER ARTHROSCOPY MINI OPEN ROTATOR CUFF', 
'EMH MAIN OR' union
--now you yourself fill in the rest of the sample data here below
select

robert693 · April 18, 2018, 3:18pm

I solved the issue.
DECLARE
@BeginDate date
,@EndDate date
,@ShoPat int

--SET @BeginDate = {?BeginDate};
--SET @EndDate = {?EndDate};
--SET @ShoPat = {?ShoPat};
SET @BeginDate = '03/01/2018';
SET @EndDate = '03/31/2018';
SET @ShoPat = 1;
/*******************************************************/

--Create table for the RSQ Score
IF OBJECT_ID ('TEMPDB.DBO.#RSQ') IS NOT NULL DROP TABLE #RSQ

SELECT DISTINCT
pat.PAT_ID [PATIENT_ID]
,pat.PAT_NAME [PATIENT_NAME]
,pat.PAT_MRN_ID [MRN]
,loc.LOC_NAME [LOCATION]
,dep.DEPARTMENT_NAME [DEPARTMENT]
,meas.MEAS_VALUE [RSQ_SCORE]
,meas.RECORDED_TIME [RSQ_RECORDED_TIME]
,hsp.PAT_ENC_CSN_ID [CSN]
,CONVERT(date, meas.RECORDED_TIME ) [RSQ_RECORDED_DATE]

into #RSQ

FROM PAT_ENC_HSP hsp
INNER JOIN PATIENT pat on pat.PAT_ID = hsp.PAT_ID
INNER JOIN CLARITY_DEP dep on dep.DEPARTMENT_ID = hsp.DEPARTMENT_ID
INNER JOIN CLARITY_LOC loc on loc.LOC_ID = dep.REV_LOC_ID
LEFT JOIN IP_FLWSHT_REC rec on rec.INPATIENT_DATA_ID = hsp.INPATIENT_DATA_ID
LEFT JOIN IP_FLWSHT_MEAS meas on meas.FSD_ID = rec.FSD_ID

WHERE
meas.FLO_MEAS_ID = '3976'
and cast(meas.RECORDED_TIME as date) >= @BeginDate
and cast(meas.RECORDED_TIME as date) <= @EndDate
and meas.MEAS_VALUE is not null
/***************************************************************************/

SELECT DISTINCT
pat.PAT_ID [PATIENT_ID]
,pat.PAT_NAME [PATIENT_NAME]
,pat.PAT_MRN_ID [MRN]
,loc.LOC_NAME [LOCATION]
,dep.DEPARTMENT_NAME [DEPARTMENT]
,rsq.CSN [RSQ_CSN]
,case when rsq.CSN is null then 2
when rsq.CSN is not null then 1
else 0 end [RSQ_SCREENED_Y_N]
,rsq.RSQ_SCORE [RSQ_SCORE]
,rsq.RSQ_RECORDED_TIME [RSQ_RECORDED_TIME]
,dateadd(ww, datediff(ww, 0, rsq.RSQ_RECORDED_TIME ), -1) [BEGIN_WEEK]
,dateadd(ww, datediff(ww, 0, rsq.RSQ_RECORDED_TIME ), 5) [END_WEEK]
,hsp.PAT_ENC_CSN_ID [CSN]
,CONVERT(date, rsq.RSQ_RECORDED_TIME ) [RSQ_RECORDED_DATE]
,case when dep.DEPARTMENT_ID in('10101100', '110000034', '110000126', '111000011', '112000008', '113000013', '114000006', '115000022', '116000003', '117000035', '117501001', '119000008', '120000004', '121000004', '9000410010011')
then 'Emergency'
else 'Inpatient'
end [DEPT_TYPE]

FROM PAT_ENC_HSP hsp
INNER JOIN PATIENT pat on pat.PAT_ID = hsp.PAT_ID
INNER JOIN CLARITY_DEP dep on dep.DEPARTMENT_ID = hsp.DEPARTMENT_ID
INNER JOIN CLARITY_LOC loc on loc.LOC_ID = dep.REV_LOC_ID

  LEFT JOIN #RSQ           rsq  on rsq.CSN                  = hsp.PAT_ENC_CSN_ID

WHERE
--loc.LOC_ID in {?Facility}
loc.LOC_ID in ('110000') --SFMC
--and
and cast(hsp.HOSP_ADMSN_TIME as date) >= @BeginDate
and cast(hsp.HOSP_ADMSN_TIME as date) <= @EndDate
and ('0' = @ShoPat or @ShoPat = (case when rsq.CSN is null then 2
when rsq.CSN is not null then 1
else 0 end))

robert693 · April 18, 2018, 3:18pm

Thank you for your help.