Solved

Select query

Posted on 2007-04-03
11
230 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query for highest sequence 4 60
Syntax issue with my Where Clause SQL 2012 20 38
Freeze portion of datamart 2 21
T-SQL to copy a database 37 66
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

740 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