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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy