Sql-server – SQL Server – Round a number when it’s one decimal before .50

sql servert-sql

I would like to know if there is a way to round a number if it's one decimal before 50.

i.e,

I am using the below code and it works, it gives 8.50:

select Round(8.49,1) = 8.50

The problem comes when I want to round the below one, I would expect this one to be only 8.48 but results in 8.50 as well.

select Round(8.48,1) = 8.50

The idea is to keep the 8.48 as it is, but when it's 8.49, it needs to be 8.50.

Best Answer

If you want something other than a standard rounding, you will probably need to use a nested CASE statement.

Maybe take something like the below and tweak it:

DECLARE @Num DEC (10,2) = 8.49 --8.48 

SELECT 
CASE 
WHEN @Num - FLOOR(@Num) >= 0.5 THEN ROUND(@Num,1)
WHEN @Num - FLOOR(@Num) = 0.49 THEN FLOOR(@Num) + 0.5 
ELSE @Num
END AS NewValue

Related Solutions

SQL Server – Understanding Semaphore Usage

Generally your current code shouldn't allow two concurrent transactions to process the same @Sbs_Type simultaneously.

Two concurrent threads can't both acquire U locks on the rows matching WHERE Sbs_Trf_Type = @Sbs_Type.

If two threads simultaneously call GetSubscribeNo with the same @Sbs_Type when there are no matching rows whilst meanwhile a third transaction inserts a row for that @Sbs_Type I can see how this would fail however.

One or both of the GetSubscribeNo calls would need to get past the UPDATE statement before the insert and thus fail to lock anything that would block the other one.

A better way of doing this anyway would be with the OUTPUT clause.

ALTER PROCEDURE [dbo].[GetSubscribeNo] @Sbs_Type    INT,
                                       @subscribeNo INT OUTPUT
AS
  BEGIN
      DECLARE @Out TABLE (Subscribe_No INT);

      SET XACT_ABORT ON;
      BEGIN TRAN T1;

      WITH T
           AS ((SELECT TOP(1) *
                FROM   UNIT_SBS_NOS WITH (ROWLOCK, READPAST)
                WHERE  Sbs_Trf_Type = @Sbs_Type))
      DELETE FROM T
      OUTPUT DELETED.FREE_SBS_NO INTO @Out;

      /*Default to -1 if no rows*/
      SELECT @subscribeNo = ISNULL(MAX(Subscribe_No), -1)
      FROM   @Out

      INSERT INTO All_Subscribe
                  (Subscribe_No)
      VALUES      (@subscribeNo);

      COMMIT TRAN T1;
  END

T-SQL – Why ALTER and UPDATE Statements Only Run When Separated into Own Batch

I suspect that there's a database error that you're not seeing here.

If you remove the GO statement right before the two final UPDATE statements, I'd imagine that the query crashes with something like "unknown column 'group_id'".

This happens because an execution plan is generated for each batch at a time, i.e. every time you pass GO. In my experience, this plan does not always consider statements that make schema changes (except SELECT .. INTO operations and index operations). If you try to generate a query plan that includes the group_id column before the ALTER TABLE statements have actually been executed, that column won't actually exist yet.

My best advice may not work for every conceivable scenario, but it will for yours: create the temp table correctly from the start, in this case by including the columns that you're ADDing in the original CREATE TABLE statement at the top, so you won't have to ALTER it later on in the code.

Best Answer

Related Solutions

SQL Server – Understanding Semaphore Usage

T-SQL – Why ALTER and UPDATE Statements Only Run When Separated into Own Batch

Related Question