Solved

What am I doing wrong with this datediff?

Posted on 2016-10-03
4
36 Views
Last Modified: 2016-10-03
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
Comment
Question by:brucegust
  • 3
4 Comments
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 41826908
You are missing the end parenthesis to the DATEDIFF function:
(datediff(d, max(txn.created), getDate()) <= 45)
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41826914
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
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41826919
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
 

Author Closing Comment

by:brucegust
ID: 41826934
Thanks!
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question