Link to home
Create AccountLog in
Avatar of Bruce Gust
Bruce GustFlag for United States of America

asked on

What am I doing wrong with this datediff?

Here's the entire SELECT statement:

 select paymentmethod, payerdesc, type, [date] = case when type = 'c' then dos else posted end, pfirst+' '+plast as patientname, LTRIM( case when type = 'a' then payerdesc when type = 'p' then payerdesc when type = 'c' then ISNULL(cpt, '') +' - '+ ISNULL(left(cptdesc,100),'') end ) as description, amount,'Barton County Memorial Hospital' as physicianid, coalesce(cg.encountercode, '') as practclaimid, case when type = 'c' then 0 else 1 end as priority, left(cptdesc,100) as cptdesc, cpt as cpt, (select sum(amount) from txn where t.chargegroupid = txn.chargegroupid and t.accountid = txn.accountid) as balance, (select min(dos) from txn where t.chargegroupid = txn.chargegroupid and t.accountid = txn.accountid and type = 'c') as firstdos from txn t left join chargegroup cg on t.chargegroupid = cg.id where t.accountid = 12159877 and (cg.status1 not in ('rt','hd','rp','fc') or cg.status1 is null) and cg.id in ( select chargegroupid from txn where accountid = 12159877 group by chargegroupid having max(posted) > 
--dbo.ufn_lastpaperstatementbefore(12159877, cast('2050-01-01' as date)) )
(datediff(d, max(txn.created), getDate() <= 45) 
AND type<>'c' and reversedref is null ) u order by firstdos, practclaimid, priority,[date]

Open in new window


Right now I'm getting an error that says "Incorrect syntax near '<'"

It's referring to the part where I have:

(datediff(d, max(txn.created), getDate() <= 45)

Where am I blowing it?
ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
You have also commented out a line that was the right side to the having clause:
having max(posted) > 
--dbo.ufn_lastpaperstatementbefore(12159877, cast('2050-01-01' as date)) )
You also need to drop the u at the end of this line:
        AND type<>'c' and reversedref is null ) u

You do not alias SELECT statements in WHERE or HAVING clauses.
Avatar of Bruce Gust

ASKER

Thanks!