Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JeffS23
Posting Yak Master
212 Posts |
 Posted - 2007-12-26 : 10:16:00
|
| I cannot work out what I need to do to fix the query attached. I am getting the error Line 105: Incorrect syntax near ')'.Thank you for your help[CODE]CREATE PROCEDURE spMHCPall ( @type int ) AS Select fname, lname, dtmenroll, ysnenroll, lngtype, lngprimaryprovider , Type, strreason, provider, lngenrollmentid, encounterdate, encounterdate2, countencounter, countlocus, counttplan, countmrpt, counttplanCBT, counttplanCBT16, countgroupattenance, dtmbaseline, dtmenrollment, satsurvey, encountercount, countcontact, contactdate, psymdconsults, countQOLSurvey, lngpatientid, Case When ( Case When ( Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01') then IsNull(Date1,'1900-01-01') Else IsNull(Date2,'1900-01-01') End )>IsNull(Date3,'1900-01-01') Then ( Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01') then IsNull(Date1,'1900-01-01') Else IsNull(Date2,'1900-01-01') End ) Else IsNull(Date3,'1900-01-01') End)>IsNull(Date4,'1900-01-01') Then ( Case When ( Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01') then IsNull(Date1,'1900-01-01') Else IsNull(Date2,'1900-01-01') End )>IsNull(Date3,'1900-01-01') Then ( Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01') then IsNull(Date1,'1900-01-01') Else IsNull(Date2,'1900-01-01') End ) Else IsNull(Date3,'1900-01-01') End) Else IsNull(Date4,'1900-01-01') End MaxDate, Case When COALESCE(Date1,Date2,Date3,Date4) > DATEADD(M,-1,CONVERT(CHAR(8),GETDATE(),112)) then 'false' When COALESCE(Date1,Date2,Date3,Date4) is null then 'false' else 'true' end ysncontact From ( Select A.lngpatientid, ysnenroll,lngtype, lngprimaryprovider, strreason,dtmenroll, lngenrollmentid, 'Date2'=(Select max(dtmcontact) FROM tblMHCPcontact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type ), 'Date4'=(Select max(B.dtmenroll) FROM tblMHCPEnrollment B where A.lngpatientid=B.lngpatientid and B.lngtype = @type), 'Date3'=(Select max(visitdate) FROM tblMHCPEncounter B where A.lngpatientid=B.lngpatientid and A.lngtype = @type ), 'psymdconsults'=(Select count(visitdate) FROM tblMHCPEncounter B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and B.providerid = '1'), 'countlocus'=(Select case when count(dtmdate)= 0 then 'N' else 'Y' end FROM tblMHCPLOCUS B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmdate between dateadd(month, -6, getdate()) and getdate() ), 'counttplan'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPtreatmentplan B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmstart between dateadd(month, -6, getdate()) and getdate()), 'countmrpt'=(Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and dtmdate between dateadd(month, -1, getdate()) and getdate()), 'counttplanCBT'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and dtmintake between dateadd(month, -1, getdate()) and getdate()), 'counttplanCBT16'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and dtmintake between dateadd(month, +3, getdate()) and getdate()), 'countgroupattenance'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPGroupAttenance B where A.lngpatientid=B.lngpatientid and dtmgroup between dateadd(day, +7, getdate()) and getdate()), 'contactdate'=(Select max(dtmcontact) FROM tblMHCPContact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmcontact between dateadd(day, -7, getdate()) and getdate()), 'dtmbaseline'=(Select max(dtmintake) FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and ysnbaseline = 1), 'dtmenrollment'=(Select max(dtmenroll) FROM tblMHCPenrollment B where A.lngpatientid=B.lngpatientid and A.lngtype=b.lngtype and ysndisenroll is null), 'contactcount'=(Select case when count(dtmcontact)= 0 then 0 else 1 end FROM tblMHCPContact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmcontact between dateadd(day, -7, getdate()) and getdate()), 'countcontact'=(Select count(dtmcontact) FROM tblMHCPContact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmcontact between dateadd(day, -7, getdate()) and getdate()), 'satsurvey'=(Select case when count(dtmSurvey)= 0 then 'N' else 'Y' end FROM tblMHCPClientSat B where A.lngpatientid=B.lngpatientid and dtmSurvey between dateadd(day, -70, getdate()) and getdate()), 'encountercount'=(Select case when count(visitdate)= 0 then 0 else 1 end FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()), 'encounterdate'=(Select Max(visitdate) FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()), 'countencounter'=(Select count(visitdate)FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()), 'countSatSurvey'=(Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPSurveyQOL B where A.lngpatientid=B.lngpatientid and dtmdate between dateadd(month, -6, getdate()) and getdate()), 'countQOLSurvey'=(Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPSurveyQOL B where A.lngpatientid=B.lngpatientid and dtmdate between dateadd(month, -6, getdate()) and getdate()), 'Date1'=(Select max(dtmgroup) FROM tblMHCPGroupAttenance B where A.lngpatientid=B.lngpatientid and A.lngtype = @type), 'fname' = (Select strfname FROM tblPatient as P where A.lngpatientid=P.lngpatientid ) , 'lname' = (Select strlname FROM tblPatient as P where A.lngpatientid=P.lngpatientid ) , 'Type' = (Select strtype FROM tbllkpMHCPEnrollment as P where A.lngtype=P.lngtype ), 'Provider' = (Select fname + ' ' + lname as pName FROM tbllkpMHCPprovider as P where A.lngprimaryprovider=P.staffid) FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate())) From tblMHCPEnrollment A where A.lngtype = @type and ysnDisEnroll IS NULL) AS lngpatientidDate[/CODE] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-26 : 11:13:30
|
| You dont require the brackets for defining params:-CREATE PROCEDURE spMHCPall ( @type int ) AS Also,I think this nested case can cause an error (not sure though.I'm checking this from home so i dont have any way to test this out) Case When ( Case When ( Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01') then IsNull(Date1,'1900-01-01') Else IsNull(Date2,'1900-01-01') End )>IsNull(Date3,'1900-01-01') ) I guess the closing bracket has to be at end of condition check (from red position to blue) |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-26 : 12:19:30
|
You will need a name for the derived table as well.And you have to FROM statements in the derived table.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
|
|
Syntax help