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]
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)) )
Shaun Kline
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.
having max(posted) >
--dbo.ufn_lastpaperstateme