Help with case when

[dwerden]

I need help.
This is the code I currently have and need to change:
p.code as "Code",
p.detail_type as "Detail Type",

p.code shows diagnostic code numbers and billing procedure numbers
while p.detail_type indicate which is which. Diagnostic code numbers
are designated as '-2' (minus 2) in the 'detail_type' and procedure
codes are designated as '-4' (minus 4) in the 'detail_type.' The query
I am using now (see below) gives me duplicate appt dates to show both
the diagnostic code and procedure code. I need to clean this up a bit.


What I want this to do is find a statement that will separate the codes
into diagnosis numbers and procedure numbers and place these numbers in
different columns in the ad hoc report that is generated.

I would like something to the effect of:
If p.detail_type = -2 then place the code number in a column known as
"Code"
If p.detail_type - -4 then place the code number in a column known as
"Procedure"

Any ideas on how to write this?




Select
/* Individual Client Task List */

a.Provider as "Provider",
a.Apptdate as "Session Date",
a.Appttype as "Session Type",
p.code as "Code",
p.detail_type as "Detail Type",
a.Complaint1 as "Note Written",
a.Signoffinits as "Co-Signed",
a.Lastname as "Lastname",
a.Firstname as "Firstname"

>From Appointments a, Patientmedicalr ecords p

Where a.uniquenumber = p.appt_uniquenu mber
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.Compl aint1,1,2) = 'TS'
or a.Complaint1 like 'Secretary Signed'
or a.Complaint1 is null
or a.Signoffinits is null)
and a.Patientnumber not in ('57629','82362 ','125163','139 842')
Order by
a.Provider,
a.Apptdate

Thanks for your help. dwerden.

[Erland Sommarskog]

Re: Help with case when

dwerden (dwerden@purdue .edu) writes:

I need help.
This is the code I currently have and need to change:
p.code as "Code",
p.detail_type as "Detail Type",
>
p.code shows diagnostic code numbers and billing procedure numbers
while p.detail_type indicate which is which. Diagnostic code numbers
are designated as '-2' (minus 2) in the 'detail_type' and procedure
codes are designated as '-4' (minus 4) in the 'detail_type.' The query
I am using now (see below) gives me duplicate appt dates to show both
the diagnostic code and procedure code. I need to clean this up a bit.
>
>
What I want this to do is find a statement that will separate the codes
into diagnosis numbers and procedure numbers and place these numbers in
different columns in the ad hoc report that is generated.
>
I would like something to the effect of:
If p.detail_type = -2 then place the code number in a column known as
"Code"
If p.detail_type - -4 then place the code number in a column known as
"Procedure"
>
Any ideas on how to write this?

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@sommarsk og.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

[dwerden]

Re: Help with case when

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@sommarsk og.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/prodinf...ons/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, Patientmedicalr ecords p

Where a.uniquenumber = p.appt_uniquenu mber
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.Compl aint1,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','139 842')
Order by
a.Provider,
a.Apptdate,
a.Lastname

[Tom Cooper]

Re: Help with case when

Assuming there is at most one row in Patientmedicalr ecords 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.Cod e,'') As Code,
Coalesce(p2.Cod e,'') As Procedure,

And change:
From Appointments a, Patientmedicalr ecords p
Where a.uniquenumber = p.appt_uniquenu mber
and a.Division = 3
and a.Inactive = 0
<rest of where conditions>

to:

From Appointments a
Left Outer Join Patientmedicalr ecords p1 On a.uniquenumber =
p1.appt_uniquen umber
And p1.detail_type = -2
Left Outer Join Patientmedicalr ecords p1 On a.uniquenumber =
p12.appt_unique number
And p1.detail_type = -4
Where a.Division = 3
and a.Inactive = 0
<rest of where conditions>

Tom

"dwerden" <dwerden@purdue .eduwrote in message
news:1154457844 .644843.182100@ m73g2000cwd.goo glegroups.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@sommarsk og.se
>>
>Books Online for SQL Server 2005 at
>http://www.microsoft.com/technet/pro...ads/books.mspx
>Books Online for SQL Server 2000 at
>http://www.microsoft.com/sql/prodinf...ons/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, Patientmedicalr ecords p

Where a.uniquenumber = p.appt_uniquenu mber
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.Compl aint1,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','139 842')
Order by
a.Provider,
a.Apptdate,
a.Lastname
>

[--CELKO--]

Re: Help with case when

>p.code shows diagnostic code numbers and billing procedure numbers while p.detail_type indicate which is which. <<

Stop writing code like this. You never cram two or more attributes
into one column. Hey, why not have a column for "squids and
automobiles", too?

What you have done is re-invent the variant record from COBOL, FORTRAN,
Pascal, etc. and other procedural languages.

[Arnie Rowland]

Re: Help with case when

That's a great idea!

I think I remember seeing a squid looking automobile thingy in some animated
film recently.

Joe, Did you create the database used by the film company?

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


"--CELKO--" <jcelko212@eart hlink.netwrote in message
news:1154462822 .641976.57700@m 73g2000cwd.goog legroups.com...

>>p.code shows diagnostic code numbers and billing procedure numbers while
>>p.detail_ty pe indicate which is which. <<

>
Stop writing code like this. You never cram two or more attributes
into one column. Hey, why not have a column for "squids and
automobiles", too?
>
What you have done is re-invent the variant record from COBOL, FORTRAN,
Pascal, etc. and other procedural languages.
>