Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Dec 2010
    Posts
    36

    Question Unanswered: Calculations on fields containing non numerical data?

    I am developing a database of soccer transfers. The transfer fee field contains numerical values but also 'u/d' to signify a transfer in which the fee has not been disclosed and 'loan' to signify a loan move. Is it possible to perform calculations on fields containing non numerical data? e.g. could I run a query that replaced u/d and loan with 0 for the purposes of performing calculations or should I remove all non numerical values from the fee field?

    I am preparing the data prior to importing it into SQL but you can view the data structure here :

    Premier League Transfers Winter 2012

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That column is really 2 columns...one column should be anount decimal (15,2) and the Other should be type varchar(??)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2010
    Posts
    36
    I thought about doing that but if a user wants to search for a players history i.e. a list of all the clubs he has played for and the transfer fees paid this scenario is common :

    smith joins arsenal in 2006 for €5.00
    smith joins bolton in 2007 on loan
    smith joins bolton in 2008 for €2.50
    smith joins PSG in 2009 for u/d

    How would I deal with that?

    There's an example here but in their case they have a separate column for loan moves and denoate the fee with - but still have a running total.

    Marco Borriello - Player changes, player transfers - transfermarkt.co.uk

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm with Brett. I'd have two columns, one numeric and one a foreign key to another table describing the type of transfer. The FK data type could be a CHAR to make it simpler, using something like T for transfer, L for loan, and U for undisclosed. You would then have much more flexibility to handle reporting as a side benefit!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2010
    Posts
    36
    Ok. So if I have two columns, one named fee which lists the value of any fee paid and one name type which lists the transfer (disclosed, undisclosed, loan, free). I'm not sure how to construct the query to output the transfer history of a player and give a total of the fees paid. What would be the correct syntax?

    Select* from table where player Like 'playername' And type like 'disclosed' And sum fee?!??!

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE TABLE #Palermo (
       player       NVARCHAR(25)    NOT NULL
    ,  team         NVARCHAR(25)    NOT NULL
    ,  y            DATETIME        NOT NULL
    ,  transType    CHAR(1)         NOT NULL
    ,  amount       MONEY           NOT NULL
    )
    
    INSERT INTO #Palermo
       SELECT 'smith', 'arsenal', '2006-01-01', 'T', 5.00 UNION ALL
       SELECT 'smith', 'bolton',  '2007-01-01', 'L', 0.00 UNION ALL
       SELECT 'smith', 'bolton',  '2008-01-01', 'T', 2.50 UNION ALL
       SELECT 'smith', 'PSG',     '2009-01-01', 'U', 0.00
    
    SELECT player + ' joins ' + team + ' in ' + STR(Year(y), 4)
    +  CASE transType
          WHEN 'T' THEN ' for €' + LTrim(Str(amount, 15, 2))
          WHEN 'L' THEN ' on loan'
          WHEN 'U' THEN ' for u/d'
          ELSE '????'
       END
       FROM #Palermo
    
    DROP TABLE #Palermo
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Dec 2010
    Posts
    36
    Thanks Pat. Now I know it's possible. I'm collating the data at the moment prior to importing into SQL - 22 excel spreadsheets with 242 worksheets in total. I'll make one table from that data as it makes it easier to perform queries. Once that's done, hopefully sometime over the next week, I'll show you the structure so you'll have a better idea. For this particular query it's not necessary to specify the clubs a player transfers to or the year as that data is already recorded in separate fields.

  8. #8
    Join Date
    Dec 2010
    Posts
    36
    Quote Originally Posted by Pat Phelan View Post
    Code:
    CREATE TABLE #Palermo (
       player       NVARCHAR(25)    NOT NULL
    ,  team         NVARCHAR(25)    NOT NULL
    ,  y            DATETIME        NOT NULL
    ,  transType    CHAR(1)         NOT NULL
    ,  amount       MONEY           NOT NULL
    )
    
    INSERT INTO #Palermo
       SELECT 'smith', 'arsenal', '2006-01-01', 'T', 5.00 UNION ALL
       SELECT 'smith', 'bolton',  '2007-01-01', 'L', 0.00 UNION ALL
       SELECT 'smith', 'bolton',  '2008-01-01', 'T', 2.50 UNION ALL
       SELECT 'smith', 'PSG',     '2009-01-01', 'U', 0.00
    
    SELECT player + ' joins ' + team + ' in ' + STR(Year(y), 4)
    +  CASE transType
          WHEN 'T' THEN ' for €' + LTrim(Str(amount, 15, 2))
          WHEN 'L' THEN ' on loan'
          WHEN 'U' THEN ' for u/d'
          ELSE '????'
       END
       FROM #Palermo
    
    DROP TABLE #Palermo
    -PatP
    I've just executed the query and it doesn't give a total of all fees paid. I need to generate a list of the transfers AND give a running total. Below is a screenshot of the structure of a table :

    http://soccer-europe.com/Transfers/Table_Structure.jpg

    So in mind of the data from the above worksheet if I want to generate a report showing a list of transfers to a club and give a running total of the transfers the first part is :

    SELECT*
    FROM TableName
    WHERE NewClub LIKE 'Athletico Madrid%'

    and the second?

    Basically replicating the sum function in Excel which will give a total regardless of non numerical data.
    Last edited by Palermo; 03-01-12 at 14:56.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You'll have to adjust the code based on what kind of extract you are running. For players, the code would be:
    Code:
    CREATE TABLE #Palermo (
       player       NVARCHAR(25)    NOT NULL
    ,  team         NVARCHAR(25)    NOT NULL
    ,  y            DATETIME        NOT NULL
    ,  transType    CHAR(1)         NOT NULL
    ,  amount       MONEY           NOT NULL
    )
    
    INSERT INTO #Palermo
       SELECT 'smith', 'arsenal', '2006-01-01', 'T', 5.00 UNION ALL
       SELECT 'smith', 'bolton',  '2007-01-01', 'L', 0.00 UNION ALL
       SELECT 'smith', 'bolton',  '2008-01-01', 'T', 2.50 UNION ALL
       SELECT 'smith', 'PSG',     '2009-01-01', 'U', 0.00
    
    SELECT player + ' joins ' + team + ' in ' + STR(Year(y), 4)
    +  CASE transType
          WHEN 'T' THEN ' for €' + LTrim(Str(amount, 15, 2))
          WHEN 'L' THEN ' on loan'
          WHEN 'U' THEN ' for u/d'
          ELSE '????'
       END
    ,  (SELECT SUM(amount)
          FROM #Palermo AS z1
          WHERE  z1.player = #Palermo.player
             AND z1.y <= #Palermo.y) AS PmtToDate
       FROM #Palermo
    
    DROP TABLE #Palermo
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Dec 2010
    Posts
    36
    Thanks. Would it be possible to show a total instead of a running total? e.g.

    smith joins arsenal in 2006 for €5.00
    smith joins bolton in 2007 on loan
    smith joins bolton in 2008 for €2.50
    smith joins PSG in 2009 for u/d
    Total €7.50.

    If you don't mind could you explain what the AS statement does?

    Is the purpose of trimming the amount to show the values correct to 2 decimal places?
    Last edited by Palermo; 03-01-12 at 18:20.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on the last change you've proposed I'm pretty sure that what you are trying to do is a presentation issue, not really a data management issue. This means that we're trying to solve the problem in the wrong place.

    For what you are trying to do (sophisticated formatting with the concept of rows, headers, footers, and dynamic formatting) is really an issue that needs to be handled by the presentation tool. This is easier to do within such a tool, and it scales better when you need to support many languges, formats, etc.

    A data management tool like SQL Server is intended to store and protect your data. It handles getting data to the database, from the database, ensuring that the data is "correct" in the sense that it is complete and meets the criteria that define "good" versus "bad" data.

    A data presentaiton tool like Reporting Services allows you to present reports like what you're suggesting. It allows them to be tailored to the users choice of languge, graphics, etc This tool is where you ought to make the kind of changes that you're addressing now, such as running totals versus group totals.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Dec 2010
    Posts
    36
    Ok thanks I'll take a look.

  13. #13
    Join Date
    Dec 2010
    Posts
    36
    Quote Originally Posted by Pat Phelan View Post
    You'll have to adjust the code based on what kind of extract you are running. For players, the code would be:
    Code:
    CREATE TABLE #Palermo (
       player       NVARCHAR(25)    NOT NULL
    ,  team         NVARCHAR(25)    NOT NULL
    ,  y            DATETIME        NOT NULL
    ,  transType    CHAR(1)         NOT NULL
    ,  amount       MONEY           NOT NULL
    )
    
    INSERT INTO #Palermo
       SELECT 'smith', 'arsenal', '2006-01-01', 'T', 5.00 UNION ALL
       SELECT 'smith', 'bolton',  '2007-01-01', 'L', 0.00 UNION ALL
       SELECT 'smith', 'bolton',  '2008-01-01', 'T', 2.50 UNION ALL
       SELECT 'smith', 'PSG',     '2009-01-01', 'U', 0.00
    
    SELECT player + ' joins ' + team + ' in ' + STR(Year(y), 4)
    +  CASE transType
          WHEN 'T' THEN ' for €' + LTrim(Str(amount, 15, 2))
          WHEN 'L' THEN ' on loan'
          WHEN 'U' THEN ' for u/d'
          ELSE '????'
       END
    ,  (SELECT SUM(amount)
          FROM #Palermo AS z1
          WHERE  z1.player = #Palermo.player
             AND z1.y <= #Palermo.y) AS PmtToDate
       FROM #Palermo
    
    DROP TABLE #Palermo
    -PatP
    I've tried to adapt this code for the table structure but cannot get it to work. I have the following fields :

    Player
    Position
    Previous Club
    New Club
    Transfer Fee
    Type
    League
    Window

    Values for Type can be :
    Disclosed
    Undisclosed
    Loan
    Free

    I changed the above code to :

    Code:
    SELECT player + ' joins ' + [New Club] + ' in ' + STR(Year(Window), 4)
    +  CASE Type
    ELSE '????'
    WHEN 'Disclosed' THEN ' for €' + LTrim(Str(Transfer Fee, 15, 2))
    WHEN 'Loan' THEN ' on loan'
    WHEN 'Undisclosed' THEN ' for u/d'
    END
    ,(SELECT SUM(Transfer Fee)
    FROM #Summer_2001_2011 AS z1
    WHERE  z1.player = # Summer_2001_2011.Player
    AND z1.y <= #Summer_2001_2011.y) AS PmtToDate
    FROM #Summer_2001_2011
    The Transfer Fee field is money datatype. NULL values are listed when there is no fee e.g. loan or undisclosed transfer.

    What am I doing wrong?
    Last edited by Palermo; 04-09-12 at 20:04.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Show the complete CREATE TABLE statement for the #Summer_2001_2011 table, and some sample rows of data (like I gave in the previous example) so that I can see if I can help you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh yeah, your error messages would be really nice too!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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