difference between two resultsets
-
>>not in on 8 M records?????????????????????????
I agree. I didnt look at the number of records being returned.. I was only thinking about getting the difference between the queries... ******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
Hence my suggestion to read the whole post before trying to help... It's happening far too often these days (not just you
). -
well to simplfy your suggestions. I have both results stored in physical tables, so I only have table A and Table B with the same schemas. I need to find the 800 rows not in Table A that exist in Table B. There are no constraints on the table but I ran both queries with distinct on the select so I don't beleive duplicates are a problem. I tried something like this,but it didn't work:
SELECT *
FROM [DW_JCFacts].[dbo].[E3JCFact_Detail_NoCT] nct
Where not exists (select *
from [DW_JCFacts].[dbo].[E3JCFact_Detail] ct
where ct.[DivisionFK] = nct.[DivisionFK] and
ct.[CommunityFK]= nct.[CommunityFK] and
ct.[JobFK]= nct.[JobFK] and
ct.[CostCodeFK] = nct.[CostCodeFK] and
ct.[ProjectManagerFK]= nct.[ProjectManagerFK] and
ct.[PostDateFK]= nct.[PostDateFK] and
ct.[CostDateFK]= nct.[CostDateFK] and
ct.[JobCostPeriodFK]= nct.[JobCostPeriodFK] and
ct.[PeriodBudgetFK]= nct.[PeriodBudgetFK] and
ct.[CostType]= nct.[CostType] and
ct.[ReferenceNumber]= nct.[ReferenceNumber] and
ct.[BatchNumber]= nct.[BatchNumber] and
ct.[TransactionDescription]= nct.[TransactionDescription] and
ct.[Cost]= nct.[Cost] and
ct.[Revenue]= nct.[Revenue] and
ct.[Quantity]= nct.[Quantity] and
ct.[OriginalBudget] = nct.[OriginalBudget]and
ct.[RevisedBudget] = nct.[RevisedBudget]and
ct.[OriginalQuantity]= nct.[OriginalQuantity] and
ct.[RevisedQuantity] = nct.[RevisedQuantity])
-
Seems ok from here... what's the problem??
Did you do a select into of the full statements or only the last join that was causing problems?
-
I actually used a DTS package and copied the normal query to the normal table, then removed the Costcode join condition on the source query and changed the destination table to the noCT table.
As a result I have two tables with 8.9m rows, one having 700 extra rows. The above query returned 123809 rows. So weird.
-
Is it possible that you forgot 1 column in the exists?..
or that the diff ljoin screwed up more data than you thaught?
-
no I did a right click drag drop to make sure I got every column and then got the copy/paste skills going on it.
I don't think it's possible the removal of the ljoin screwed up any records.
Well I've determined that the Period budget table being joined too has to be cleaned up before joining to the staged table for the fact build. I don't know exactly what rows are giving me a headache, but I know if CostType does not distinguish how the budgets join to the actualls, it shouldn't be a memeber of the periodbudget Key.
I'll have to resolve this by rebuilding my Periodbudget dimension so that it does not require CostType to distinguish rows by removing duplicate distinct jobcode,costcode, and jobcostperioddate codes found by the following query:
select jobcode,costcode,jobcostperioddate,count(costtype)
from dbo.periodbudgets
group by jobcode, costcode, jobcostperioddate
having count(costtype) > 1
WHen that query contains no results, everything should be fine.
-
Glad you can work it out...
-
thx for your help remi.
Helps to have someone to bounce ideas off of.
-
And from my belly profile you could see that you'd have plenty of room to bounce things off me
.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply