Create A Variable Using a Previously Variable Created

Asked Jan 30 '19 at 20:42

Active Jan 30 '19 at 20:45

Viewed 21 times

I calculated a time span variable Checkin_to_triage using the Datediff function and would like to categorize that values from that variable into new variable, Pre_Arrival. I've tried using the code below but am obviously getting a invalid column name 'checkin_to_triage' error message due to the IIf ([checkin_to_triage]<60 And [checkin_to_triage]>0,'PreArrival','Null') AS Pre_Arrival code, as the Pre_Arrival variable is not a field within my dataset.

use EMTCQIData

DECLARE @StartDate Date
DECLARE @EndDate Date

Set @StartDate = '01/01/2018'
Set @EndDate = '12/31/2018'

SELECT *, 
  Format([CHECKIN_DATE_TIME],'dddd') AS [Checkin_Day], 
  Format([CHECKIN_DATE_TIME],'dddd dd/mm/yyyy') AS checkin_day_date, 
  CONVERT(VARCHAR(20),[CHECKIN_DATE_TIME],108) as Checkin_Time, 
  DateDiff("n",CHECKIN_DATE_TIME,triage_date_time) AS checkin_to_triage,
  IIf ([checkin_to_triage]<60 And [checkin_to_triage]>0,'PreArrival','Null') AS Pre_Arrival
FROM ED_TAT_MASTER
WHERE (CHECKIN_DATE_TIME > @StartDate and CHECKIN_DATE_TIME < @EndDate)

edited Jan 30 '19 at 20:45

Dale K

25,246
15
42
71

asked Jan 30 '19 at 20:42

Raven

3

btw, for future reference, `checkin_to_triage` --as used in your sample query-- is not a variable. It is an alias. There is a big difference. `@StartDate` and `@EndDate` are variables. – Tab Alleman Jan 30 '19 at 20:47
Thanks @TabAlleman, learning new SQL lingo every day! – Raven Jan 30 '19 at 20:49
1

You cannot use alias (`checkin_to_triage`) as a field name here. replace it with the expression. – Alex Kudryashev Jan 30 '19 at 20:52
Thanks @AlexKudryashev, how would that I do that? Sorry I'm still a beginner at SQL – Raven Jan 30 '19 at 21:18
use `datediff(...)` instead of `checkin_to_triage` – Alex Kudryashev Jan 30 '19 at 21:25
@AlexKudryashev not sure if I'm following, do you mean something like this? `IIf (DateDiff("n",CHECKIN_DATE_TIME,triage_date_time)<60 And DateDiff("n",CHECKIN_DATE_TIME,triage_date_time)>0,'PreArrival','Null') AS Pre_Arrival` – Raven Jan 30 '19 at 21:30
1

It looks ugly but should work – Alex Kudryashev Jan 30 '19 at 21:31
It does! Thank you! – Raven Jan 30 '19 at 21:34

Create A Variable Using a Previously Variable Created

0 Answers0