Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2009
    Posts
    79

    Unanswered: sql server database - calculated field

    Hi All,

    I have two sql database tables one is mydata and another one is fxrates. Now what I want is that I have the below columns in my database.

    Columns in Mydata table :
    1 ) Object Currency (which contains the text like GBP, EUR, USD etc. which are currency types)
    2) Value In Object Currency (Which contains the values in above mentioned currencies)
    3) Period (Which has month numbers like 1, 2, 3, .i.e. Jan, Feb and Mar and so on)
    4) Year (Which contains years .i.e. 2007, 2008, 2009 and 2010)

    Columns in FX Rates table :
    1) Exchange Rate (Which contains the exchange for all the above mentioned currencies)
    2) Object Currency (which contains the text like GBP, EUR, USD etc. which are currency types)
    3) Period (Which has month numbers like 1, 2, 3, .i.e. Jan, Feb and Mar and so on)
    4) Year (Which contains years .i.e. 2007, 2008, 2009 and 2010)

    Now I want a calculated field which will calculate value in Euros .i.e. the exchange rate is for converting value in euros.

    Something like this.

    Code:
    Fxrates.[Exchange Rate]/mydata.[Value In Object Currency]
    But while doing above calculation it should consider the criterias which are period, year and object currency as well.

    I need a help on this I have macro which pull data from mydata database table to excel now is it possible for us to have this calculated column in the excel directly or we need to insert a calculated column in sql database. I am ok with both ways. Please help...

    Thanks a lot for your help in advance.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The most primitive way, - a view, not a calculated field. A better way is either an inline table-value function, or a procedure.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A user-defined function if you are only retrieving a few calculated records at a time.
    A view if you are retrieving large sets of data at a time.
    An indexed view if you are retrieving large sets of relatively static data (fastest option, but indexed views can be finicky).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    SELECT mydata.[Value In Object Currency] *
              Fxrates.[Exchange Rate] as PriceInEuro
    FROM mydata
         INNER JOIN Fxrates ON
              mydata.Period = Fxrates.Period AND
              mydata.Year = Fxrates.Year AND
              mydata.[Object Currency] = Fxrates.[Object Currency]
    I wonder if your calculation can be correct: Fxrates.[Exchange Rate]/mydata.[Value In Object Currency]; the higher the price in USD, the lower the price in EUR?

    It will be mydata.[Value In Object Currency] / Fxrates.[Exchange Rate] or mydata.[Value In Object Currency] * Fxrates.[Exchange Rate], depending on the definition of [Exchange Rate].

    Perhaps you could consider using another naming strategy. One the doesn't use words that MS SQL already uses (Year) or names with spaces([Value In Object Currency]). It will make your life easier on the long run.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Sep 2009
    Posts
    79

    sql server database - calculated field

    Hi Wim,

    Thanks a lot for your reply, you are correct I had erroneously mentioned the below line

    Incorrect Calculation :
    Code:
    Fxrates.[Exchange Rate]/mydata.[Value In Object Currency]
    Correct Calculation :
    Code:
    mydata.[Value In Object Currency]/Fxrates.[Exchange Rate]
    Now actually I am very bad at sql statements so I need the help on the below.
    Following is the sql statement which I have current in my excel macro. I need your help to consolidate the code provided by you into my existing statement which is as below :

    Code:
    cmd1.CommandText = "SELECT mydata.*, CRM.Country, CCM.[Sub Product UBR Code], CEM.FSI_LINE3_code FROM Data_SAP.dbo.mydata mydata INNER JOIN Data_SAP.dbo.[Country_Region Mapping] CRM  ON (mydata.[Company Code] = CRM.[Company Code])INNER JOIN Data_SAP.dbo.[Cost Center mapping] CCM  ON (mydata.[Cost Center] = CCM.[Cost Center])INNER JOIN Data_SAP.dbo.[Cost Element Mapping] CEM  ON (mydata.[Unique Indentifier 1] = CEM.CE_SR_NO)WHERE CRM.Country IN (" & selection1 & ") AND CCM.[Sub Product UBR Code] IN (" & selection & ") AND CEM.FSI_LINE3_code IN (" & selection2 & ")AND mydata.year = '" & ComboBox4.Value & "' AND mydata.period = '" & ComboBox3.Value & "'AND mydata.[Document Type]= '" & Left(ComboBox11.Value, 2) & "'
    And following is the sql statement provided by you :
    Code:
    SELECT mydata.[Value In Object Currency] *
              Fxrates.[Exchange Rate] as PriceInEuro
    FROM mydata
         INNER JOIN Fxrates ON
              mydata.Period = Fxrates.Period AND
              mydata.Year = Fxrates.Year AND
              mydata.[Object Currency] = Fxrates.[Object Currency]

    Thanks a lot for your help in advance.

  6. #6
    Join Date
    Sep 2009
    Posts
    79

    sql server database - calculated field

    Hi All,

    Did anyone get the chance to look into the above post.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, and we answered your question.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Sep 2009
    Posts
    79

    sql server database - calculated field

    Hi All,

    Thanks a lot. I have successfully incorporated the code provided by Wim in my existing sql statement.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •