CODEWITHSUNDEEP
sqlsql-server
PinkChick93
PinkChick93

Reputation: 13

Converting currencies within a table using data from other tables

I am trying to covert the currencies within a table of sales which uses Substitute IDs of all the related tables (and places them within the relevant column, so: SubstituteItemKey within the Item table is equal to the ItemID within mainsales) and specifies the transaction values. The Snippet of code which fails is:

inner join [dbo].[Multiplier] on [dbo].[dimday].[Day] = [dbo].[Multiplier].[Day]

The problem with the results to this code is that it produces duplicate rows and the results are not correct.

Upvotes: 1

Views: 177

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Multiplier probably holds conversion rates for several currencies. But you don't join the appropriate currency to a mainsales record, but all currencies. Thus you get multiple records instead of one per factsale.

Change 

inner join Multiplier 
  on [dbo].[dimDay].[Day] = [dbo].[Multiplier].[Day]

to something like

inner join Multiplier 
    on  [dbo].[dimDay].[Day] = [dbo].[Multiplier].[Day]
    and [dbo].[mainsales].[Currency] = [dbo].[Multiplier].[Currency]

Upvotes: 1

Related Questions