-1

I am trying to figure a work around for this query I am writing in SQL Server.

I need to be able to use the results from the CASE statement in the DateDiff function.

  • If the encounter end date is null then I want to use the encounter begin date for the date diff calculation.

  • If the encounter end date is not null then I want to use the encounter end date for the date diff calculation.

However, I keep getting an "invalid column name" error when I put the dateofservice in the datediff function.

This is my code:

Select 
    e.encounterid,
    Case 
        when e.encounterenddate = null
            then e.encounterbegindate
        else e.encounterenddate
    end as dateofservice,
    rf.submissiondate,
    datediff(day, dateofservice, rf.submissiondate) as lagtime,
    rf.healthplancode,
    rf.transactiontypeid
from 
    udt_encounter as E
join 
    udt_receivedfile as RF on e.receivedfileid = rf.receivedfileid
where 
    rf.healthplancode = '00'
    and rf.transactiontypeid = 1
    and rf.submissiondate between '2020-01-01' and '2020-06-30'
order by 
    encounter.id asc
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
atdower
  • 1
  • Does this answer your question? [Reference alias (calculated in SELECT) in WHERE clause](https://stackoverflow.com/questions/11182339/reference-alias-calculated-in-select-in-where-clause) – Stu May 17 '22 at 22:07
  • 2
    Your use of checking if something is null is incorrect: `when e.encounterenddate=null` would never be satisfied. You should use `when e.encounterenddate IS NULL` . Having said that @KekeSemau's answer is a btter way of doing it. – tinazmu May 17 '22 at 22:28

1 Answers1

2

You do not even need CASE, COALESCE will do it.
Since this is quite short I would stick with simply repeating the expression:

SELECT 
    e.encounterid,
    COALESCE(e.encounterenddate, e.encounterbegindate) AS dateofservice
    ,rf.submissiondate
    ,datediff(day, COALESCE(e.encounterenddate, e.encounterbegindate), rf.submissiondate) AS lagtime
...
KekuSemau
  • 6,830
  • 4
  • 24
  • 34
  • Thanks so much everyone for the input. I've been using SQL Server/SQL for literally 2 weeks. Learning on the fly from YouTube. – atdower May 18 '22 at 13:54
  • @atdower Great, good luck! If this has answered your question, please 'accept' it with the checkmark button. – KekuSemau May 18 '22 at 14:31