Post reply

calculation

  • October 13, 2017 at 5:26 pm

    #329162

    Hi,

    TOTAL  datatype is (numeric(10,0),null)
    STR  datatype is (numeric(20,15),null)

    REPLACE(FORMAT(CAST(ROUND((
          CASE
           WHEN SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
           ELSE  SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.TOTAL * a.STR  ELSE 0 END) /  SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.STR  ELSE 0 END)
          END),0) AS INT),'C'), '.00', '') AS TOTALVALUE

    Result is not accurate , sometimes  its giving one value low with above code.
    Example:  Result should be $1452, but i am getting $1451 .

    Any suggestions please ?.

    Thanks.

  • October 13, 2017 at 8:16 pm

    #1963664

    adisql - Friday, October 13, 2017 5:26 PM

    Hi,

    TOTAL  datatype is (numeric(10,0),null)
    STR  datatype is (numeric(20,15),null)

    REPLACE(FORMAT(CAST(ROUND((
          CASE
           WHEN SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
           ELSE  SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.TOTAL * a.STR  ELSE 0 END) /  SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.STR  ELSE 0 END)
          END),0) AS INT),'C'), '.00', '') AS TOTALVALUE

    Result is not accurate , sometimes  its giving one value low with above code.
    Example:  Result should be $1452, but i am getting $1451 .

    Any suggestions please ?.

    Thanks.

    Without example data for STR_Weight and STR, there's little we can do to help you fix the problem BUT, I can tell you what the basis of the problem is.  Please refer to the article at the following link.
    https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 13, 2017 at 8:56 pm

    #1963670

    Jeff Moden - Friday, October 13, 2017 8:16 PM

    adisql - Friday, October 13, 2017 5:26 PM

    Hi,

    TOTAL  datatype is (numeric(10,0),null)
    STR  datatype is (numeric(20,15),null)

    REPLACE(FORMAT(CAST(ROUND((
          CASE
           WHEN SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
           ELSE  SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.TOTAL * a.STR  ELSE 0 END) /  SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.STR  ELSE 0 END)
          END),0) AS INT),'C'), '.00', '') AS TOTALVALUE

    Result is not accurate , sometimes  its giving one value low with above code.
    Example:  Result should be $1452, but i am getting $1451 .

    Any suggestions please ?.

    Thanks.

    Without example data for STR_Weight and STR, there's little we can do to help you fix the problem BUT, I can tell you what the basis of the problem is.  Please refer to the article at the following link.
    https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql

    Thank you for the response.
    Here are the sample data of these three columns.

    STR_WEIGHT                 STR  TOTAL
    1.683427321.6834273216619
    2.797591932.797591938000
    1.998580731.998580731000
    2.8474697392.84746973915000
    1.969871441.969871440
    2.1808446462.1808446465500
    5.9570956655.9570956651000
    2.4756053882.475605388600
    3.612190823.612190826285
    2.408030612.408030611000
    2.923469982.923469988250
    1.311926351.3119263528250
    3.9109883213.9109883211500
    2.8474697392.8474697394250
    2.4266799442.4266799440
    1.084818671.084818671500
    1.115753561.115753561500
    2.5323853282.5323853284938
    1.534264181.534264181400
    4.2681758294.2681758299500
    1.442404871.442404871000
    1.444978251.444978251150
    1.605588531.605588532000
    5.8062894695.8062894690
    2.375475252.3754752515000

    �{

  • October 14, 2017 at 2:14 am

    #1963676

    adisql - Friday, October 13, 2017 8:56 PM

    Thank you for the response.
    Here are the sample data of these three columns.

    STR_WEIGHT                 STR  TOTAL
    1.683427321.6834273216619
    2.797591932.797591938000
    1.998580731.998580731000
    2.8474697392.84746973915000
    1.969871441.969871440
    2.1808446462.1808446465500
    5.9570956655.9570956651000
    2.4756053882.475605388600
    3.612190823.612190826285
    2.408030612.408030611000
    2.923469982.923469988250
    1.311926351.3119263528250
    3.9109883213.9109883211500
    2.8474697392.8474697394250
    2.4266799442.4266799440
    1.084818671.084818671500
    1.115753561.115753561500
    2.5323853282.5323853284938
    1.534264181.534264181400
    4.2681758294.2681758299500
    1.442404871.442404871000
    1.444978251.444978251150
    1.605588531.605588532000
    5.8062894695.8062894690
    2.375475252.3754752515000

    �{

    And now something usable:
    CREATE TABLE #Sample
      (STR_WEIGHT numeric(20,15), --Guessed this
      [STR] numeric(20,15),
      [TOTAL] numeric(10,0));
    GO

    INSERT INTO #Sample
    VALUES
      (1.68342732,1.68342732,16619),
      (2.79759193,2.79759193,8000),
      (1.99858073,1.99858073,1000),
      (2.847469739,2.847469739,15000),
      (1.96987144,1.96987144,0),
      (2.180844646,2.180844646,5500),
      (5.957095665,5.957095665,1000),
      (2.475605388,2.475605388,600),
      (3.61219082,3.61219082,6285),
      (2.40803061,2.40803061,1000),
      (2.92346998,2.92346998,8250),
      (1.31192635,1.31192635,28250),
      (3.910988321,3.910988321,1500),
      (2.847469739,2.847469739,4250),
      (2.426679944,2.426679944,0),
      (1.08481867,1.08481867,1500),
      (1.11575356,1.11575356,1500),
      (2.532385328,2.532385328,4938),
      (1.53426418,1.53426418,1400),
      (4.268175829,4.268175829,9500),
      (1.44240487,1.44240487,1000),
      (1.44497825,1.44497825,1150),
      (1.60558853,1.60558853,2000),
      (5.806289469,5.806289469,0),
      (2.37547525,2.37547525,15000);
    GO

    SELECT *
    FROM #Sample;
    SELECT REPLACE(FORMAT(CAST(ROUND((
           CASE WHEN SUM(CASE WHEN TOTAL IS NOT NULL THEN STR_WEIGHT ELSE 0 END) = 0 THEN 0
                ELSE SUM(CASE WHEN TOTAL IS NOT NULL THEN TOTAL * STR ELSE 0 END) /
                    SUM(CASE WHEN TOTAL IS NOT NULL THEN STR ELSE 0 END)
           END),0) AS INT),'C'), '.00', '') AS TOTALVALUE 
    FROM #Sample;
    GO
    DROP TABLE #Sample;
    GO

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • October 14, 2017 at 2:20 am

    #1963677

    Also, for the values you have supplied, what is the expected value for your expression? The TotalValue is 4,944 for the sample data and expression you've supplied, which doesn't match that in your original post.

    Thanks.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • October 14, 2017 at 9:16 am

    #1963692

    adisql - Friday, October 13, 2017 8:56 PM

    Jeff Moden - Friday, October 13, 2017 8:16 PM

    adisql - Friday, October 13, 2017 5:26 PM

    Hi,

    TOTAL  datatype is (numeric(10,0),null)
    STR  datatype is (numeric(20,15),null)

    REPLACE(FORMAT(CAST(ROUND((
          CASE
           WHEN SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
           ELSE  SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.TOTAL * a.STR  ELSE 0 END) /  SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.STR  ELSE 0 END)
          END),0) AS INT),'C'), '.00', '') AS TOTALVALUE

    Result is not accurate , sometimes  its giving one value low with above code.
    Example:  Result should be $1452, but i am getting $1451 .

    Any suggestions please ?.

    Thanks.

    Without example data for STR_Weight and STR, there's little we can do to help you fix the problem BUT, I can tell you what the basis of the problem is.  Please refer to the article at the following link.
    https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql

    Thank you for the response.
    Here are the sample data of these three columns.

    STR_WEIGHT                 STR  TOTAL
    1.683427321.6834273216619
    2.797591932.797591938000
    1.998580731.998580731000
    2.8474697392.84746973915000
    1.969871441.969871440
    2.1808446462.1808446465500
    5.9570956655.9570956651000
    2.4756053882.475605388600
    3.612190823.612190826285
    2.408030612.408030611000
    2.923469982.923469988250
    1.311926351.3119263528250
    3.9109883213.9109883211500
    2.8474697392.8474697394250
    2.4266799442.4266799440
    1.084818671.084818671500
    1.115753561.115753561500
    2.5323853282.5323853284938
    1.534264181.534264181400
    4.2681758294.2681758299500
    1.442404871.442404871000
    1.444978251.444978251150
    1.605588531.605588532000
    5.8062894695.8062894690
    2.375475252.3754752515000

    �{

    In the future, please take the extra bit of time to make the data "readily consumable" so that we can help more quickly.  Please see the article at the first link in my signature line below under "Helpful Links" for one way to do such a thing.  It also gives us great clues as to what the correct datatypes and other things may be without having to go back and forth with clarification questions, etc.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 14, 2017 at 12:59 pm

    #1963699

    Jeff Moden - Saturday, October 14, 2017 9:16 AM

    adisql - Friday, October 13, 2017 8:56 PM

    Jeff Moden - Friday, October 13, 2017 8:16 PM

    adisql - Friday, October 13, 2017 5:26 PM

    Hi,

    TOTAL  datatype is (numeric(10,0),null)
    STR  datatype is (numeric(20,15),null)

    REPLACE(FORMAT(CAST(ROUND((
          CASE
           WHEN SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
           ELSE  SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.TOTAL * a.STR  ELSE 0 END) /  SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.STR  ELSE 0 END)
          END),0) AS INT),'C'), '.00', '') AS TOTALVALUE

    Result is not accurate , sometimes  its giving one value low with above code.
    Example:  Result should be $1452, but i am getting $1451 .

    Any suggestions please ?.

    Thanks.

    Without example data for STR_Weight and STR, there's little we can do to help you fix the problem BUT, I can tell you what the basis of the problem is.  Please refer to the article at the following link.
    https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql

    Thank you for the response.
    Here are the sample data of these three columns.

    STR_WEIGHT                 STR  TOTAL
    1.683427321.6834273216619
    2.797591932.797591938000
    1.998580731.998580731000
    2.8474697392.84746973915000
    1.969871441.969871440
    2.1808446462.1808446465500
    5.9570956655.9570956651000
    2.4756053882.475605388600
    3.612190823.612190826285
    2.408030612.408030611000
    2.923469982.923469988250
    1.311926351.3119263528250
    3.9109883213.9109883211500
    2.8474697392.8474697394250
    2.4266799442.4266799440
    1.084818671.084818671500
    1.115753561.115753561500
    2.5323853282.5323853284938
    1.534264181.534264181400
    4.2681758294.2681758299500
    1.442404871.442404871000
    1.444978251.444978251150
    1.605588531.605588532000
    5.8062894695.8062894690
    2.375475252.3754752515000

    �{

    In the future, please take the extra bit of time to make the data "readily consumable" so that we can help more quickly.  Please see the article at the first link in my signature line below under "Helpful Links" for one way to do such a thing.  It also gives us great clues as to what the correct datatypes and other things may be without having to go back and forth with clarification questions, etc.

    Thank you .
    Actually i am looking if any issues with rounding or any syntax problem with this script code.
    Please suggest if any issues with this script calculation.

  • October 14, 2017 at 1:48 pm

    #1963700

    adisql - Saturday, October 14, 2017 12:59 PM

    Thank you .
    Actually i am looking if any issues with rounding or any syntax problem with this script code.
    Please suggest if any issues with this script calculation.

    Issues as in..? That's very vague.

    You started off by saying you were getting the wrong results. Is it giving the right results now? What did you change so it does? Do we have an older version..?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • October 14, 2017 at 5:32 pm

    #1963706

    adisql - Saturday, October 14, 2017 12:59 PM

    Jeff Moden - Saturday, October 14, 2017 9:16 AM

    adisql - Friday, October 13, 2017 8:56 PM

    Jeff Moden - Friday, October 13, 2017 8:16 PM

    adisql - Friday, October 13, 2017 5:26 PM

    Hi,

    TOTAL  datatype is (numeric(10,0),null)
    STR  datatype is (numeric(20,15),null)

    REPLACE(FORMAT(CAST(ROUND((
          CASE
           WHEN SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.STR_WEIGHT ELSE 0 END) = 0 THEN 0
           ELSE  SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.TOTAL * a.STR  ELSE 0 END) /  SUM(CASE WHEN a.TOTAL IS NOT NULL THEN a.STR  ELSE 0 END)
          END),0) AS INT),'C'), '.00', '') AS TOTALVALUE

    Result is not accurate , sometimes  its giving one value low with above code.
    Example:  Result should be $1452, but i am getting $1451 .

    Any suggestions please ?.

    Thanks.

    Without example data for STR_Weight and STR, there's little we can do to help you fix the problem BUT, I can tell you what the basis of the problem is.  Please refer to the article at the following link.
    https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql

    Thank you for the response.
    Here are the sample data of these three columns.

    STR_WEIGHT                 STR  TOTAL
    1.683427321.6834273216619
    2.797591932.797591938000
    1.998580731.998580731000
    2.8474697392.84746973915000
    1.969871441.969871440
    2.1808446462.1808446465500
    5.9570956655.9570956651000
    2.4756053882.475605388600
    3.612190823.612190826285
    2.408030612.408030611000
    2.923469982.923469988250
    1.311926351.3119263528250
    3.9109883213.9109883211500
    2.8474697392.8474697394250
    2.4266799442.4266799440
    1.084818671.084818671500
    1.115753561.115753561500
    2.5323853282.5323853284938
    1.534264181.534264181400
    4.2681758294.2681758299500
    1.442404871.442404871000
    1.444978251.444978251150
    1.605588531.605588532000
    5.8062894695.8062894690
    2.375475252.3754752515000

    �{

    In the future, please take the extra bit of time to make the data "readily consumable" so that we can help more quickly.  Please see the article at the first link in my signature line below under "Helpful Links" for one way to do such a thing.  It also gives us great clues as to what the correct datatypes and other things may be without having to go back and forth with clarification questions, etc.

    Thank you .
    Actually i am looking if any issues with rounding or any syntax problem with this script code.
    Please suggest if any issues with this script calculation.

    In that case, take a look at the link I posted because the rounding issues are given in that link.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

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