Posted by dwerden on 10/02/44 11:54

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]