Convert Varchar to Int and SUM

  • ibrahimovich87

    SSC Enthusiast

    Points: 130

    Hi,

    need a request...it's possible to convert dbo.RE80_MOVSCHPUNTI.RE80_VALORE this table from nvarchar to INT and make a SUM of it??'

    Thank's in advance

  • John Mitchell-245523

    SSC Guru

    Points: 148667

    Yes, provided all the values in the column can be converted to int.  If you have values such as 'Cuttlefish' and 'Madonna' then it's not going to work.  The best thing, of course, would be to change the table definition so that the column is int.  That's not always an option, but if it is for you, that's what I advise.

    John

  • ibrahimovich87

    SSC Enthusiast

    Points: 130

    Hi jhon can you explain me how do it?? In thi table have all values that it's "numeric" like this (i0m sorry but i'm really newbie with sql)

    0,38

    12,90

    13,05

    15,00

  • John Mitchell-245523

    SSC Guru

    Points: 148667

    OK, so you've got numbers with decimal components, but you want to convert them to int?  What is the rule - disregard everything after the decimal point, round to the nearest whole number, round to the next lowest whole number, round to the next highest whole number, or something else?  Perhaps you could show us what you've already tried?

    John

     

  • ibrahimovich87

    SSC Enthusiast

    Points: 130

    Hi john haven't only tried to see wich values have in this table, need only to check and SUM all Values until first number after  decimal point

  • ScottPletcher

    SSC Guru

    Points: 98398

    SELECT SUM(TRY_CAST(LEFT(RE80_VALORE, CHARINDEX('.', RE80_VALORE + '.')) AS int))

    FROM dbo.RE80_MOVSCHPUNTI

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ibrahimovich87

    SSC Enthusiast

    Points: 130

    ok it' correct, i want to add other column to select,

    it's RE80_DATAMOV i need it for select period to check this value

  • ScottPletcher

    SSC Guru

    Points: 98398

    I am not sure exactly what you mean.

    What specifically do you/we need to do with column RE80_DATAMOV?

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ibrahimovich87

    SSC Enthusiast

    Points: 130

    Have to sum value in RE80_VALORE selecting determinate date range using RE80_DATAMOV

  • anthony.green

    SSC Guru

    Points: 112401

    Can you explain in detail what the full requirements are?

    Can you please post as per this link (http://www.sqlservercentral.com/articles/Best+Practices/61537/) this will help us to get to the point your trying to get answered.

    Please remember we cannot see your environment, so we cannot see what your trying to do.  Please detail as best as you can and provide examples.

    I have Table A with columns BC I want to get X Y etc.

     

     

    SELECT SUM(TRY_CAST(LEFT(RE80_VALORE, CHARINDEX('.', RE80_VALORE + '.')) AS int))
    FROM dbo.RE80_MOVSCHPUNTI
    WHERE RE80_DATAMOV >= 'SOME START DATE' AND RE80_DATAMOV < 'SOME END DATE'
  • ibrahimovich87

    SSC Enthusiast

    Points: 130

    Thanks Antony i'm trying to explaine

    i Have table RE80_MOVSCHPUNTI

    in table A (RE80_VALORE) have value in NVARCHAR

    in Table B (RE80_DATAMOV) have date for extract the value of Table A

    and Table C (RE80_INDTIMOVSCH) that it's the type of Value to filter for extract in Table a (have to filter 1)

    so i need to sum all Value from Table A (converting it from NVARCHAR to INT) in a Date Range of Table B (FILTEREB BY TABLE C=1)

    i hope this can help you

     

  • anthony.green

    SSC Guru

    Points: 112401

    OK, then please read that link I provided and provide the sample schema, tables, data and expected outcome.

    It is unclear from the above how everything is tied together

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • ibrahimovich87

    SSC Enthusiast

    Points: 130

    SELECT dbo.RE80_MOVSCHPUNTI.RE80_DATAMOV, dbo.RE80_MOVSCHPUNTI.RE80_VALORE, dbo.RE80_MOVSCHPUNTI.RE80_INDTIPOMOVSCH

    +dbo.RE80_MOVSCHPUNTI.RE80_DATAMOV(DateValue),

    +dbo.RE80_MOVSCHPUNTI.RE80_VALORE(nvarchar),

    + dbo.RE80_MOVSCHPUNTI.RE80_INDTIPOMOVSCH(INT),

    + dbo.RE14_DOCTESTATA.RE14_IDNEGOZIO_RE05 (INT)

    FROM dbo.RE80_MOVSCHPUNTI INNER JOIN

    dbo.RE14_DOCTESTATA ON dbo.RE80_MOVSCHPUNTI.RE80_IDDOCTESTATA_RE14 = dbo.RE14_DOCTESTATA.RE14_ID

    WHERE (dbo.RE80_MOVSCHPUNTI.RE80_INDTIPOMOVSCH = 1) AND (dbo.RE80_MOVSCHPUNTI.RE80_DATAMOV = CONVERT(DATETIME, 'DATA RANGE', 102)) AND

    (dbo.RE14_DOCTESTATA.RE14_IDNEGOZIO_RE05 = 1)

    Result that i need it's this

    Need SUM dbo.RE80_MOVSCHPUNTI.RE80_VALORE(nvarchar) of a DATA RANGE

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply