How to find second position after decimal

Question

How to find second position after decimal

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
November 16, 2023 at 2:53 pm

#4328929

Have a numeric column HRS Numeric(5,2)
Need to add error if the value after decimal point second digit is not 0
Example - 50.10 is acceptible
50.77 is not acceptable (Second digit should be Zero after decimal point)

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

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

ScottPletcher SSC Guru Points: 101042 More actions · Answer 1

;WITH cte_data AS (
    SELECT CAST(50.10 AS numeric(5, 2)) AS HRS
    UNION ALL
    SELECT 50.77
)
SELECT HRS
FROM cte_data
WHERE HRS LIKE '%.[0-9][^0]%' --<<--

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101042 More actions · Answer 2

(Dup post, removed)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Collins SSCrazy Points: 2132 More actions · Answer 3

You could test if the modulo .1 of HRS equals zero

;WITH cte_data AS (
    SELECT CAST(50.10 AS numeric(5, 2)) AS HRS
    UNION ALL
    SELECT 50.77
)
SELECT HRS
FROM cte_data
WHERE HRS%.1=0;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Michael L John One Orange Chip Points: 27052 More actions · Answer 4

mcfarlandparkway wrote:

Have a numeric column HRS Numeric(5,2)
Need to add error if the value after decimal point second digit is not 0
Example - 50.10 is acceptible
50.77 is not acceptable (Second digit should be Zero after decimal point)

This is a strange requirement. I'm curious what the use case is.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Bedeencion40 Newbie Points: 7 More actions · Answer 5

To check the second digit after the decimal, try this:

sqlCopy code

IF (HRS * 100) % 10 <> 0 THEN

-- Add your error handling here

-- This means the second digit after the decimal is not zero

END IF;

This code multiplies the number by 100 and checks if the second digit after the decimal isn't zero. If it's not zero, an error can be handled as needed.

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 6

Nearly duplicate post with an incorrect improvement calculation removed.

--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)

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 7

Steve Collins wrote:

You could test if the modulo .1 of HRS equals zero

;WITH cte_data AS (
    SELECT CAST(50.10 AS numeric(5, 2)) AS HRS
    UNION ALL
    SELECT 50.77
)
SELECT HRS
FROM cte_data
WHERE HRS%.1=0;

Being in love with the terms "SQL Death by a Million Cuts" and "Milliseconds Matter", your method tests out at being 31% faster than the "LIKE" method previously used.

--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)

Steve Collins SSCrazy Points: 2132 More actions · Answer 8

Thank you Jeff. 44.9% or 31%? Either way there's no implicit type conversion 🙂

Ken McKelvey's solution in the "Pivot with grouping intervals" topic is interesting. It cross join's a 2 row virtual table { (1), (-1) } and then (in a single start/end column) uses cross apply to make the 1 row the start date and the -1 row the end date. Then the query orders and aggregates across the start/end column. It's not so intuitive yet tho. My current method (as seen on SSC dozens of times) of dealing with overlapping timespans expands the intervals and looks for gaps using lead/lag. In my head I have a generic scenario that goes along with it. For Ken's method it's basically an unpivoting of the intervals

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 9

Steve Collins wrote:

Thank you Jeff. 44.9% or 31%? Either way there's no implicit type conversion 🙂
Ken McKelvey's solution in the "Pivot with grouping intervals" topic is interesting. It cross join's a 2 row virtual table { (1), (-1) } and then (in a single start/end column) uses cross apply to make the 1 row the start date and the -1 row the end date. Then the query orders and aggregates across the start/end column. It's not so intuitive yet tho. My current method (as seen on SSC dozens of times) of dealing with overlapping timespans expands the intervals and looks for gaps using lead/lag. In my head I have a generic scenario that goes along with it. For Ken's method it's basically an unpivoting of the intervals

It's 31% savings. I did the original calculation "upside down" (I originally did Old/New-1 instead of the correct New/Old-1) . Here are the run results from the previous posted test code.

--===== Using the Modulo Method
 SQL Server Execution Times:
   CPU time = 2296 ms,  elapsed time = 2323 ms.

--===== Using the LIKE method
 SQL Server Execution Times:
   CPU time = 3375 ms,  elapsed time = 3367 ms.

--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)