SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 HELP WITH QUERY
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jhon11
Starting Member

42 Posts

Posted - 11/12/2007 :  11:09:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/12/2007 :  11:17:40  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.


Edited by - dataguru1971 on 11/12/2007 12:08:53
Go to Top of Page

anonymous1
Posting Yak Master

185 Posts

Posted - 11/12/2007 :  11:23:56  Show Profile  Reply with Quote
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
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/12/2007 :  11:26:42  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.

Go to Top of Page

jhon11
Starting Member

42 Posts

Posted - 11/12/2007 :  11:55:02  Show Profile  Reply with Quote
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..

Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/12/2007 :  12:08:22  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000