Solved

Select query

Posted on 2007-04-03
11
234 Views
Last Modified: 2010-03-20
Can you please tell me how I can run this query so that if there is not a record in tblPatient I do not get 3 records for one person for spanish/English/Null.  thanks

SELECT Distinct lastname, firstname,case when bytlanguage = 2 then 'English'  when bytlanguage = 3 THEN 'Spanish' when bytlanguage = 1 THEN 'English'              
when bytlanguage = 4 THEN 'English'  end as 'Language', appt.phone, CONVERT(VARCHAR,APPTDATE, 101) as apptdate, CONVERT(VARCHAR(10), time_hour, 120) + ':' + CONVERT(VARCHAR(10), time_minute, 120) as time,apptreason,  name_last, name_first  FROM DDB_APPT_BASE as appt inner join ddb_pat_base as pat on appt.patid = pat.patid inner join ddb_rsc_base as rsc on appt.prprovid = rsc.urscid inner join tblpatient as p on appt.chart = p.strpatientid
WHERE CONVERT(VARCHAR,APPTDATE, 101) = CONVERT(VARCHAR,DateAdd(dd,
   CASE WHEN DATEPART(weekday, getdate()) IN ( 5,6) THEN  4 ELSE 2 END
  , GetDate()), 101) and rsc.defaultclinic = 30
ORDER BY APPTDATE DESC
0
Comment
Question by:running32
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 7

Expert Comment

by:JuanCarniglia
ID: 18843561
Instead of

"    inner join tblpatient as p on appt.chart = p.strpatientid     "

try

"    inner join tblpatient as p on p.strpatientid = appt.chart     "

Check this article, it explains why I say this:

(http://database.ittoolbox.com/documents/popular-q-and-a/inner-and-outer-join-sql-statements-2108)

Greetings
0
 

Author Comment

by:running32
ID: 18843615
They are still showing up

Ash      K      NULL      970123455      04/05/2007      14:0      CompEx, 2BWX, ProphyCh, FluoridCh      Eckhardt      Bradley
Ash      K      English      970123455      04/05/2007      14:0      CompEx, 2BWX, ProphyCh, FluoridCh      Eckhardt      Bradley
Ash      K      Spanish      970123455      04/05/2007      14:0      CompEx, 2BWX, ProphyCh, FluoridCh      Eckhardt      Bradley
Bea      T      NULL      720123455      04/05/2007      10:0      FullDebrd      Sleight      Bev
Bea      T      English      720123455      04/05/2007      10:0      FullDebrd      Sleight      Bev
0
 
LVL 7

Assisted Solution

by:JuanCarniglia
JuanCarniglia earned 150 total points
ID: 18843671
Ok, try:

"   
    FROM DDB_APPT_BASE as appt
    inner join ddb_pat_base as pat on pat.patid = appt.patid
    inner join ddb_rsc_base as rsc on rsc.urscid  = appt.prprovid
    inner join tblpatient as p on p.strpatientid = appt.chart
"

Instead of

"  FROM DDB_APPT_BASE as appt inner join ddb_pat_base as pat on appt.patid = pat.patid inner join ddb_rsc_base as rsc on appt.prprovid = rsc.urscid inner join tblpatient as p on appt.chart = p.strpatientid   "

Greetings
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 42

Expert Comment

by:dqmq
ID: 18843754
What table contains the byLanguage column?
0
 

Author Comment

by:running32
ID: 18843884
tblpatient
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18844059
for each language you are getting there must be a row in tblpatient...

please explain in business english what you are attempting to achieve..,.

indicate the proper joins between your tables...

indicate what multiple rows mean for each table...



0
 

Author Comment

by:running32
ID: 18844345
the field bytlanguage is stored as 1, 2, 3, 4 so I need to write out the actual language so I need to put the case statement in.

tblPatient contains the bytlanguage and is joined to DDB_APPT_BASE by chart # and strpatientid
DDB_APPT_BASE stores all the appointment and is joined by patid
ddb_rsc_base stored the provider name and is joined to DDB_APPT_BASE  by patid

If there is no client that matches chart # =strpatientid the case statment writes the records out 3 rows.  Null, Spanish and English.   I need to identify if the client does not have a record in tblPatient and write out English instead.
Thanks
0
 
LVL 42

Expert Comment

by:dqmq
ID: 18844699
>tblPatient contains the bytlanguage and is joined to DDB_APPT_BASE by chart # and strpatientid

I think that is your problem.  Chart # contains something besides patientid's otherwise, you would not return any rows when strpatientid is missing.   Basically, you appear to be matching chart # (whatever that is) to some unrelated patient.

Note, the case statement is NOT responsible for writing out the 3 unexpected rows. That's a problem in your WHERE clause.  However, to get the case statement to force "English" for a missing row, you need to do two things (after you get the join working properly)
1. Outer join to the patient table  --  otherwise you will get no rows for the client without a patient record
2. case bylanguage  when 3 THEN 'Spanish' ELSE 'English'  end as 'Language' -- otherwise you will get no language for the client without a patient record



0
 
LVL 42

Expert Comment

by:dqmq
ID: 18845247
I meant, that's a problem in your FROM Clause, i.e. the join criteria
0
 

Author Comment

by:running32
ID: 18845718
In the query I am using the chart and strpatientid are both blank for the records that have been written out twice.   Once for Spanish and once for English.  How can I do a join when there is the potential for both values to be null?

thanks
0
 
LVL 42

Accepted Solution

by:
dqmq earned 350 total points
ID: 18846202
They are not both NULL. In fact, if either is NULL you will not produce a row in the result set. It looks to me like appt.chart contains something, perhaps an empty string and it is matching with exactly that same value in 3 rows of tblPatient.

First, you must find out exactly what is in chart # when there isn't a patient record. Do this:
Select  Chart, ISNULL(Chart,'NULL'), LEN(chart), ASCII(chart)
Then locate one of the "problem" rows and see what it shows.
Then delete the 'Spanish' and 'NULL' rows from tblPatient having that patientID.  I think if you look at those two rows you will see they are not real patients.  

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question