Solved

Select query

Posted on 2007-04-03
11
225 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
  • 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
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.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

840 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