Solved

What am I doing wrong with this datediff?

Posted on 2016-10-03
4
38 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SELECT INTO from XML 6 48
efficient backup report for SQL Server 13 79
ASP.NET, Globalization, language indexes, translation, conversion. 2 63
When are cursors useful? 8 58
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

739 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