HELP WITH QUERY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

HELP WITH QUERY

Author

Topic

jhon11
Starting Member

42 Posts

Posted - 2007-11-12 : 11:09:44

Hi,

I need the help for following.
I have one adult table and one child table . Now I want to find out distinct prescriber id and patientid from both of that table.
I can find out the unique records individually. But I dont know how could i find out by writing one query only.
the details are as follow.

Adult table has-
patient id-1443 prescriberid-508
child table has -
patient id-441 and presciber id-188

now in both table they have common
patinet id-6 and prescriber-95 ( which are in child but not adult)

so total is
1443+435=1878 patient id
508+93=601 prescriber id

Now I want total result by writing single query ..can anybody help me out...

following query will give me common pateint and prescriber id.
ALSO HELP ME WITH BETWEEN FUNCTION TO AVOID WRITING ALL DATES..

select distinct[Prescriber_ID] from dbo.Child_Patient_Drug_List_History where Prescriber_ID in
(select distinct[prescriber_ID] from dbo.Adult_Patient_Drug_List_History)and prescriber_id !='000000000'
and Report_Period in('07/2006','08/2006','09/2006','10/2006','11/2006','12/2006','01/2007','02/2007','03/2007')

select distinct[patient_id] from dbo.Child_Patient_Drug_List_History where patient_id in
(select distinct[patient_id] from dbo.Adult_Patient_Drug_List_History) and prescriber_id !='000000000'
and Report_Period in('07/2006','08/2006','09/2006','10/2006','11/2006','12/2006','01/2007','02/2007','03/2007')

Reply SOOn

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 11:17:40

quote:
Originally posted by jhon11

Hi,

I need the help for following.

Reply SOOn

Wow. Most of the people don't need their answers right away, but because you need it really soon, I will drop everything!

Not the way to get help around here. But, since your problem was kind of basic:


select distinct child.[Prescriber_ID] 
from dbo.Child_Patient_Drug_List_History  child INNER JOIN  dbo.Adult_Patient_Drug_List_History parent
on child.[Prescriber_ID] = parent.[Prescriber_ID]
where Report_Period in
('07/2006','08/2006','09/2006','10/2006','11/2006','12/2006','01/2007','02/2007','03/2007')
and child.[Prescriber_ID] != '000000000'

select distinct child.[Patient_ID] 
from dbo.Child_Patient_Drug_List_History  child INNER JOIN  dbo.Adult_Patient_Drug_List_History parent
on child.[Prescriber_ID] = parent.[Prescriber_ID]
where Report_Period in
('07/2006','08/2006','09/2006','10/2006','11/2006','12/2006','01/2007','02/2007','03/2007')
and child.[Prescriber_ID] != '000000000'

those "dates" because aren't dates, so not sure if you need between or not.

Poor planning on your part does not constitute an emergency on my part.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-12 : 11:23:56

this isn't very clear, can you please re-post with more data for the two tables and expected results described.

the between could be something like this...

WHERE RIGHT(Report_Period, 4) + LEFT(Report_Period, 2) BETWEEN @start_year + @start_month AND @stop_year + @stop_month

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 11:26:42

Hmmm, after further thought, this would work for a between clause



and ((Right(Report_Period,4) *100) + left(Report_Period,2)) between 200607 and 200703

Poor planning on your part does not constitute an emergency on my part.

jhon11
Starting Member

42 Posts

Posted - 2007-11-12 : 11:55:02

I didnt get the last part about left and right...Do i need to right next to both those queries???...And I am having trouble in running those quries as they told me to SPECIFY TABLE NAME from which you want to take report preoid id..

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 12:08:22

quote:
Originally posted by jhon11

I didnt get the last part about left and right...Do i need to right next to both those queries???...And I am having trouble in running those quries as they told me to SPECIFY TABLE NAME from which you want to take report preoid id..

My last addition was a statement to convert those "dates" into a useable result for a between statement. This is pretty basic stuff, if it is homework, you might want to read up a little.


select distinct child.[Prescriber_ID] 
      from dbo.Child_Patient_Drug_List_History  child INNER JOIN            
           dbo.Adult_Patient_Drug_List_History parent
on child.[Prescriber_ID] = parent.[Prescriber_ID]
WHERE ((Right(child.Report_Period,4) *100) + left(child.Report_Period,2)) between 200607 and 200703
and child.[Prescriber_ID] != '000000000'

select distinct child.[Patient_ID] 
from dbo.Child_Patient_Drug_List_History  child INNER JOIN
     dbo.Adult_Patient_Drug_List_History parent
on child.[Prescriber_ID] = parent.[Prescriber_ID]
WHERE ((Right(child.Report_Period,4) *100) + left(child.Report_Period,2)) between 200607 and 200703
and child.[Prescriber_ID] != '000000000'

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources