July 8, 2010 at 10:28 am
I think I understand your requirements, but some sample data and expected output would have helped. Either way, I took a stab at it.
create table #Lookupproducts (NDC int, Groupid int)
create table #BRCFORMITEM (BRCFORMID int, productid int)
insert into #Lookupproducts
select 11, 1 union all
select 12, 3 union all
select 13, 5 union all
select 14, 6
insert into #BRCFORMITEM
select 1234, 11 union all
select 1234, 14 union all
select 2345, 12 union all
select 3456, 13 union all
select 4567, 14 union all
select 5678, 12 union all
select 5678, 14
;with Groups as
(
select
BRCFORMID,
max(case when Groupid = 1 then 1 else null end) as 'Group1',
max(case when Groupid = 3 then 1 else null end) as 'Group3',
max(case when Groupid = 6 then 1 else null end) as 'Group6'
from #BRCFORMITEM BI
inner join #Lookupproducts L
on BI.productid = L.NDC
and L.Groupid in (1,3,6)
group by
BI.BRCFORMID
)
select BRCFORMID
from Groups
where (Group1 = 1 OR Group3 = 1) AND Group6 = 1
drop table #Lookupproducts
drop table #BRCFORMITEM
- Jeff
July 8, 2010 at 10:40 am
Cool It worked.
Thanks Jeff.
Thanks.
Gunjan.
July 8, 2010 at 2:29 pm
Gunjan (7/7/2010)
Hi,SELECT BI.BRCFORMID FROM BRCFORMITEM BI
inner join Lookupproducts L
on BI.productid = L.NDC
and L.Groupid in (1,3,6)
I think you want to change this to...
SELECT BI.BRCFORMID FROM BRCFORMITEM BI
inner join Lookupproducts L
on BI.productid = L.NDC
WHERE L.Groupid in (1,3,6) --<< changed this
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 8, 2010 at 2:34 pm
Hey Greg,
Thanks for the reply.
I had already tried that query but it didn't worked.
Jeff's solution worked for me.
I appreciate your help in this.
Thanks.
Gunjan.
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply