I am trying to handle the dynamic rounding in Snowflake using ROUND Function.

I have two tables, one is BUCKET_TABLE with below structure

Other table is PRODUCT_SIZES

Lastly I need to create a below table - Expected Result Table

What I am getting is this way.

Here is the query I am using.

SELECT 
COUNTRY,
CASE WHEN LENGTH(PRODUCT_SIZE)>2 THEN 1 ELSE 0.01 END   AS SIZE_RANGE_ALLOCATOR, 
CASE WHEN LENGTH(PRODUCT_SIZE)>2 THEN 0 ELSE 2 END  AS SIZE_RANGE_ROUNDER, 

CASE when PRODUCT_SIZE >= BCKT.bucket1 and PRODUCT_SIZE < BCKT.bucket2 
then concat(BCKT.bucket1,'-',round(BCKT.bucket2-SIZE_RANGE_ALLOCATOR,SIZE_RANGE_ROUNDER))
END AS SIZE_RANGE

from PRODUCT_SIZES pz
inner join BUCKET_TABLE BCKT 
on  pz.country = bckt.country

When I am hardcoding 0 in round function then I am getting expected results for Canada but gets wrong results for US.

round(BCKT.bucket2-SIZE_RANGE_ALLOCATOR,0)

Is there another way to handle the rounding Dynamically in Snowflake SQL ?

CodePudding user response：

both are "longer than 2"

feels like:

PRODUCT_SIZE >= 100 THEN 1 ELSE 0.01

would be better logic, but if you use >3 it works as you want:

WITH BUCKET_TABLE as (
    SELECT * FROM VALUES 
        ('US'   ,1  ,2),
        ('CANADA'   ,100    ,200)
        t(COUNTRY,  BUCKET1,    BUCKET2)
), PRODUCT_SIZES as (
    SELECT * FROM VALUES 
        ('US'   ,1.5),
        ('CANADA'   ,150)
        t(COUNTRY,  PRODUCT_SIZE)
)
SELECT 
    pz.COUNTRY,
    PRODUCT_SIZE,
    LENGTH(PRODUCT_SIZE),
    CASE WHEN LENGTH(PRODUCT_SIZE)>3 THEN 1 ELSE 0.01 END   AS SIZE_RANGE_ALLOCATOR, 
    
    CASE WHEN LENGTH(PRODUCT_SIZE)>3 THEN 0 ELSE 2 END  AS SIZE_RANGE_ROUNDER, 

    CASE when PRODUCT_SIZE >= BCKT.bucket1 and PRODUCT_SIZE < BCKT.bucket2 
        then concat(BCKT.bucket1,'-', IFF(LENGTH(PRODUCT_SIZE)>3, round(BCKT.bucket2 - SIZE_RANGE_ALLOCATOR,0)::text, round(BCKT.bucket2 - SIZE_RANGE_ALLOCATOR,2)::text) )
    END AS SIZE_RANGE2
    
from PRODUCT_SIZES pz
inner join BUCKET_TABLE BCKT 
on  pz.country = bckt.country