January 30, 2011 at 6:47 am
HI,
I have a query stated below. I am getting syntax errors.
select tot.zipCode, count(*) from
(
( select distinct ltrim(rtrim(lastname)) + ltrim(rtrim(firstname)) as identifier,
zipCode from Person rpt
inner join chgEnc c on rpt.personid = c.patientid
inner join dbo.ZipCodes z on rpt.priZipID = z.zipcodesID
where ceDateofService >= '1/1/2010' AND ceDateofService < '7/3/2010'
and ( firstname = 'KEVIN' and lastname = 'ZECUISTL') ) a1
full outer join
( select distinct replace(pr.[Patient Name],', ','') as identifier,[Zip Code] from dbo.Patient_Records pr
inner join dbo.Patient_Events pe
on pr.[Patient Account No] = pe.[Patient Account No]
where pe.[Date Of Service] >= '7/1/2010'
and pr.[Patient Name] like '%ZECUISTL%KEVIN%' ) a2
on a1.identifier = a2.identifier
) tot
group by tot.zipCode
Any help appreciated,
Thanks
January 30, 2011 at 8:54 am
You don’t need to give name for the join of the derived table. Bellow you can see a version that gets compiled. I think that you should also consider using a different date format. Currently you are using a date format that can be interpreted as MDY and DMY. The way that it will be interpreted is depended on the login’s default language. You should use a date format that will be interpreted in one way regardless of the login’s default language and other session’s\server’s configuration. Such format is the ISO format – YYYYMMDD. For example today’s date (January 30) will be written this way '20110130'.
select zipCode, count(*) from
( select distinct ltrim(rtrim(lastname)) + ltrim(rtrim(firstname)) as identifier,
zipCode from Person rpt
inner join chgEnc c on rpt.personid = c.patientid
inner join dbo.ZipCodes z on rpt.priZipID = z.zipcodesID
where ceDateofService >= '1/1/2010' AND ceDateofService < '7/3/2010'
and ( firstname = 'KEVIN' and lastname = 'ZECUISTL') ) a1
full outer join
( select distinct replace(pr.[Patient Name],', ','') as identifier,[Zip Code] from dbo.Patient_Records pr
inner join dbo.Patient_Events pe
on pr.[Patient Account No] = pe.[Patient Account No]
where pe.[Date Of Service] >= '7/1/2010'
and pr.[Patient Name] like '%ZECUISTL%KEVIN%' ) a2
on a1.identifier = a2.identifier
group by tot.zipCode
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply