Posted by Tom Cooper on 10/02/48 11:54

Assuming there is at most one row in Patientmedicalrecords with detail_type
= -2 and at most one with detail_type = -4 for each matching row in
Appointments, then
Change:
CASE p.detail_type WHEN -2 THEN p.code END AS Code,
CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,

To:
Coalesce(p1.Code,'') As Code,
Coalesce(p2.Code,'') As Procedure,

And change:
From Appointments a, Patientmedicalrecords p
Where a.uniquenumber = p.appt_uniquenumber
and a.Division = 3
and a.Inactive = 0
<rest of where conditions>

to:

From Appointments a
Left Outer Join Patientmedicalrecords p1 On a.uniquenumber =
p1.appt_uniquenumber
And p1.detail_type = -2
Left Outer Join Patientmedicalrecords p1 On a.uniquenumber =
p12.appt_uniquenumber
And p1.detail_type = -4
Where a.Division = 3
and a.Inactive = 0
<rest of where conditions>

Tom

"dwerden" <dwerden@purdue.edu> wrote in message
news:1154457844.644843.182100@m73g2000cwd.googlegroups.com...
> Thanks, Erland. Your code separated them like I wanted but I continue
> to get 2 entries for each actual appointment (as shown below).
>
> Provider--Session Date--Session Type--Code--Proceure--Note
> Written--Co-Signed
> Bossick--7/6/2006--1 hr Session--309.28--(empty)--Done--bb
> Bossick--7/6/2006--1 hr Session--(empty)--99212--Done--bb
>
> Is there a way to force these to combine into only one entry like this?
>
> Provider--Session Date--Session Type--Code--Procedure--Note
> Written--Co-Signed
> Bossick--7/6/2006--1 hr Session--309.28--99212--Done--bb
>
>
> Erland Sommarskog wrote:
>> CASE p.detail_type WHEN -2 THEN p.code END AS Code,
>> CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> Select
> /* Individual Client Task List */
>
> a.Provider as "Provider",
> a.Apptdate as "Session Date",
> a.Appttype as "Session Type",
> CASE p.detail_type WHEN -2 THEN p.code END AS Code,
> CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,
> a.Complaint1 as "Note Written",
> a.Signoffinits as "Co-Signed",
> a.Lastname as "Lastname",
> a.Firstname as "Firstname"
>>From Appointments a, Patientmedicalrecords p
> Where a.uniquenumber = p.appt_uniquenumber
> and a.Division = 3
> and a.Inactive = 0
> and a.Personal = 0
> and a.Ingroup = 0
> and a.Appttype not like 'TELE'
> and a.Apptdate between '1-Jul-2006' and sysdate - 1
> and a.Appttype not in ('AEP2','AEP4','AOD','TOCE2','TOCE4','ETOH2
> Class','AEP4 Class','AOD1 Class')
> and (substr(a.Complaint1,1,2) = 'TS'
> or a.Complaint1 like 'Secretary Signed'
> or a.Complaint1 is null
> or a.Signoffinits is null)
> /* Next line excludes Pyle, Kyler, Aunt Bee, Rowdy */
> and a.Patientnumber not in ('57629','82362','125163','139842')
> Order by
> a.Provider,
> a.Apptdate,
> a.Lastname
>

[Back to original message]