September 15, 2015 at 11:20 am
hi,
the below query is not working?? the fact1 table has 33520 rows,and fact2 has 33410,
however it returns no row when i use not in clause, what have i done wrong, please help?
many thanks
select
ApplicationIdentifier
from [dbo].[fact1] A
inner join [lookup_cap] L
on A.SCode = L.s_code
and A.App = L.c_seqn
where
cast (ExtractionDate as date) = '20150910'
and ApplicationIdentifier not in
(
select applicationIdentifier from [dbo].[fact2] X
where cast (ExtractionDate as date) = '20150910'
)
September 15, 2015 at 11:32 am
nightowl23 (9/15/2015)
hi,the below query is not working?? the fact1 table has 33520 rows,and fact2 has 33410,
however it returns no row when i use not in clause, what have i done wrong, please help?
many thanks
select
ApplicationIdentifier
from [dbo].[fact1] A
inner join [lookup_cap] L
on A.SCode = L.s_code
and A.App = L.c_seqn
where
cast (ExtractionDate as date) = '20150910'
and ApplicationIdentifier not in
(
select applicationIdentifier from [dbo].[fact2] X
where cast (ExtractionDate as date) = '20150910'
)
Give this a try. No promises as you provided nothing with which to test any code (see the first article I reference in my signature block).
declare @ExtractionDate date = '20150910';
select
A.ApplicationIdentifier
from
[dbo].[fact1] A
inner join [lookup_cap] L
on A.SCode = L.s_code
and A.App = L.c_seqn
where
cast(ExtractionDate as date) = @ExtractionDate
and not exists(select
1
from
[dbo].[fact2] X
where
cast(X.ExtractionDate as date) = @ExtractionDate and
A.ApplicationIdentifier = X.ApplicationIdentifier);
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply