| Author |
 Topic  |
|
|
jhon11
Starting Member
42 Posts |
 Posted - 11/12/2007 : 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
Flowing Fount of Yak Knowledge
USA
1464 Posts |
Posted - 11/12/2007 : 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.
|
Edited by - dataguru1971 on 11/12/2007 12:08:53 |
 |
|
|
anonymous1
Posting Yak Master
185 Posts |
Posted - 11/12/2007 : 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
Flowing Fount of Yak Knowledge
USA
1464 Posts |
Posted - 11/12/2007 : 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 - 11/12/2007 : 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
Flowing Fount of Yak Knowledge
USA
1464 Posts |
Posted - 11/12/2007 : 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.
|
|
|
| |
Topic |
|
|
|
HELP WITH QUERY
