[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 47
  • Last Modified:

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?
0
brucegust
Asked:
brucegust
  • 3
1 Solution
 
Shaun KlineLead Software EngineerCommented:
You are missing the end parenthesis to the DATEDIFF function:
(datediff(d, max(txn.created), getDate()) <= 45)
0
 
Shaun KlineLead Software EngineerCommented:
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)) )
0
 
Shaun KlineLead Software EngineerCommented:
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.
0
 
brucegustAuthor Commented:
Thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now