HELPPP 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

HELPPP QUERY

Author

Topic

jhon11
Starting Member

42 Posts

Posted - 2007-11-12 : 12:54:48

I have write donw following query.
Now I have two seprate results for prescriberid and idnumber from TWO tables ..ADULT_goodclm AND CHILD_goodclm.

Now I got following results.
ADULT good clm TABLE
id_number-16779
prescriber-id-1923

CHILD good clm TABLE
id_number-9540
prescriber-id-1241

Queries are as follow for both table.

select count(distinct c.id_number),count(distinct c.prescriber_id)
from bpms_ak.dbo.lu_NDC a
inner Join bpms.dbo.luReportDrugClassGroups b
on a.CNS_ClassCode=b.DrugClass
inner join bpms_ak.dbo.Adult_GoodClm c
on c.ndc=a.ndc
where a.Product_Code in (1,6)
and a.cns_classcode in (1,3,4,7,8,9,11,12,13,16,20)
and c.yearmonth between '200607' and '200704'
union all

select count(distinct c.id_number),count(distinct c.prescriber_id)
from bpms_ak.dbo.lu_NDC a
inner Join bpms.dbo.luReportDrugClassGroups b
on a.CNS_ClassCode=b.DrugClass
inner join bpms_ak.dbo.Child_GoodClm c
on c.ndc=a.ndc
where a.Product_Code in (1,6)
and a.cns_classcode in (1,3,4,7,8,9,11,12,13,16,20)
and c.yearmonth between '200607' and '200704'

BUT NOW , My task is to find out UNIQUE ID_NUMBER and UNIQUE PRESCRIBER_ID for both adult and child

Means I want to find out END results as
# No of unique prescriber
3no of unique Id_number
for both child and adult.
BECAUSE ...From my result i get seprate results...

BUT I WANT TO MAKE SURE WHTEVER prescriber and Id number are coming for ADULTs are not coming in child.
So bascily...i want filter results after this

Reply ASAP

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 13:42:24

It isn't clear what you want. The queries you post don't seem to have anything to do with your question at the end.

What are the tables and structures you need your end result FROM, and what are the queries you are using that give you separate results?

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

jhon11
Starting Member

42 Posts

Posted - 2007-11-12 : 13:48:35

i mean to say i have total 4 results out of two table .

2 for adult (prescriberid and idnumber)
2 for child (prescriberid and idnumber)
But my task is to get only one result

combined result for adult and child for both prescriber and id number.

so right now i m getting distinct results for prescriber id and id number for ADULT and CHILD table sepratly. But I want combine results.
AND ALSO it should be distinct prescriber id and id number.

I mean to say... i need to comibine both results and from that final results..I need to find out again distinct presciber id and id number...

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 13:54:18

THis would be one way to combine results using a derived table, using UNION to join the two columns from each table as one, and selecting the DISTINCT combinations from each.

Select DISTINCT t.Prescriber_ID,t.IDNumber
FROM (Select Prescriber_ID, IDNumber FROM [Adult]
UNION
Select Prescriber_ID,IDNumber FROM [Child]) t

Or
Select DISTINCT Prescriber_ID,IDNumber
FROM [Adult]
Where Prescriber_ID not in (Select Prescriber_ID FROM [Child])
UNION
Select DISTINCT Prescriber_ID,IDNumber
FROM [Child]
Where Prescriber_ID not in (Select Prescriber_ID from [Adult])

Something like that what you need ?

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

jhon11
Starting Member

42 Posts

Posted - 2007-11-12 : 14:00:39

I am trying to do like this

Select Distinct(f.id_number) from

(select f.id_number, f.prescriber_id
from bpms_ak.dbo.lu_NDC d
inner Join bpms.dbo.luReportDrugClassGroups e
on d.CNS_ClassCode=e.DrugClass
inner join bpms_ak.dbo.Child_GoodClm f
on f.ndc=d.ndc
where d.Product_Code in (1,6)
and d.cns_classcode in (1,3,4,7,8,9,11,12,13,16,20)
and f.yearmonth between '200607' and '200704')

where f.id_number not in

(select distinct(c.id_number) from

(select c.id_number, c.prescriber_id
from bpms_ak.dbo.lu_NDC a
inner Join bpms.dbo.luReportDrugClassGroups b
on a.CNS_ClassCode=b.DrugClass
inner join bpms_ak.dbo.Adult_GoodClm c
on c.ndc=a.ndc
where a.Product_Code in (1,6)
and a.cns_classcode in (1,3,4,7,8,9,11,12,13,16,20)
and c.yearmonth between '200607' and '200704'))

So that I can find out results which are in child but not in adult and than i can subtract out of total...

but that query is giving me syntax error..

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 14:10:38

Okay..

maybe this rewrite


Select Distinct f.id_number 
FROM	 bpms_ak.dbo.lu_NDC d
			inner Join bpms.dbo.luReportDrugClassGroups e
				on d.CNS_ClassCode=e.DrugClass
			inner join bpms_ak.dbo.Child_GoodClm f
				on f.ndc=d.ndc
		        where d.Product_Code in (1,6)
			and d.cns_classcode in (1,3,4,7,8,9,11,12,13,16,20)
			and f.yearmonth between '200607' and '200704'

where f.id_number not in

		(select c.id_number
		 from bpms_ak.dbo.lu_NDC a
			inner Join bpms.dbo.luReportDrugClassGroups b
				on a.CNS_ClassCode=b.DrugClass
			inner join bpms_ak.dbo.Adult_GoodClm c
				on c.ndc=a.ndc
			where a.Product_Code in (1,6)
			and a.cns_classcode in (1,3,4,7,8,9,11,12,13,16,20)
			and c.yearmonth between '200607' and '200704')

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

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-13 : 04:53:51

quote:
Originally posted by jhon11

I have write donw following query.
Now I have two seprate results for prescriberid and idnumber from TWO tables ..ADULT_goodclm AND CHILD_goodclm.

Now I got following results.
ADULT good clm TABLE
id_number-16779
prescriber-id-1923

CHILD good clm TABLE
id_number-9540
prescriber-id-1241

Queries are as follow for both table.

select count(distinct c.id_number),count(distinct c.prescriber_id)
from bpms_ak.dbo.lu_NDC a
inner Join bpms.dbo.luReportDrugClassGroups b
on a.CNS_ClassCode=b.DrugClass
inner join bpms_ak.dbo.Adult_GoodClm c
on c.ndc=a.ndc
where a.Product_Code in (1,6)
and a.cns_classcode in (1,3,4,7,8,9,11,12,13,16,20)
and c.yearmonth between '200607' and '200704'
~~union all~~
union
select count(distinct c.id_number),count(distinct c.prescriber_id)
from bpms_ak.dbo.lu_NDC a
inner Join bpms.dbo.luReportDrugClassGroups b
on a.CNS_ClassCode=b.DrugClass
inner join bpms_ak.dbo.Child_GoodClm c
on c.ndc=a.ndc
where a.Product_Code in (1,6)
and a.cns_classcode in (1,3,4,7,8,9,11,12,13,16,20)
and c.yearmonth between '200607' and '200704'

BUT NOW , My task is to find out UNIQUE ID_NUMBER and UNIQUE PRESCRIBER_ID for both adult and child

Means I want to find out END results as
# No of unique prescriber
3no of unique Id_number
for both child and adult.
BECAUSE ...From my result i get seprate results...

BUT I WANT TO MAKE SURE WHTEVER prescriber and Id number are coming for ADULTs are not coming in child.
So bascily...i want filter results after this

Reply ASAP

try Union instead of union all

if u want to keep uniqueness of records in whole (i.e. the combination of all fields should be unique)
ike id and prescriber id both in combination should be distinct and should ot be taken twice from two tables

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 05:02:27

Use a derived table

select count(distinct id_number),count(distinct prescriber_id)
from (
select c.id_number, c.prescriber_id
from bpms_ak.dbo.lu_NDC a
inner Join bpms.dbo.luReportDrugClassGroups b
on a.CNS_ClassCode=b.DrugClass
inner join bpms_ak.dbo.Adult_GoodClm c
on c.ndc=a.ndc
where a.Product_Code in (1,6)
and a.cns_classcode in (1,3,4,7,8,9,11,12,13,16,20)
and c.yearmonth between '200607' and '200704'

union all

select c.id_number, c.prescriber_id
from bpms_ak.dbo.lu_NDC a
inner Join bpms.dbo.luReportDrugClassGroups b
on a.CNS_ClassCode=b.DrugClass
inner join bpms_ak.dbo.Child_GoodClm c
on c.ndc=a.ndc
where a.Product_Code in (1,6)
and a.cns_classcode in (1,3,4,7,8,9,11,12,13,16,20)
and c.yearmonth between '200607' and '200704'
) as d

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 05:09:07

Try this rewritten code

select		count(distinct c.id_number),
		count(distinct c.prescriber_id)
FROM		(
			SELECT	ndc,
				id_number,
				prescriber_id
			FROM	bpms_ak.dbo.Adult_GoodClm
			WHERE	yearmonth between '200607' and '200704'

			UNION ALL

			SELECT	ndc,
				id_number,
				prescriber_id
			FROM	bpms_ak.dbo.Child_GoodClm
			WHERE	yearmonth between '200607' and '200704'
		) AS c
INNER JOIN	bpms_ak.dbo.lu_NDC as a ON a.ndc = c.ndc
WHERE		a.Product_Code in (1, 6)
		AND a.cns_classcode in (1, 3, 4, 7, 8, 9, 11, 12, 13, 16, 20)
		AND EXISTS (SELECT * FROM bpms.dbo.luReportDrugClassGroups as b WHERE b.DrugClass = a.CNS_ClassCode)

E 12°55'05.25"
N 56°04'39.16"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-13 : 13:35:09

Thanks Peso, that makes a lot more sense...appreciate the rewrite that improves things.

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