CODEWITHSUNDEEP
sqlsql-servert-sql
Harsh Gupta
Harsh Gupta

Reputation: 327

in Sql, how to calculate the values based on range?

I created a table and get a count column after performing other operations. Now, I want to calculate the value based on the range in which the count lies. If it crosses the range then consider the max range.

For example: Example 1

Count  | Min  |  Max |  Amount
----------------------------------
  4    | 1    |   1  |    5
  4    | 2    |   2  |    10
  4    | 3    |   5  |    12
  4    | 6    |   8  |    15

Example 2

Count  | Min  |  Max |  Amount
---------------------------------
  4    | 1    |   1  |    5
  4    | 2    |   2  |    10

Results: What I want the results. For Example 1: Count (4) lies between Min(3) and Max(4) so we need to consider the amount from that row and the result like below. 

Count  | Min  |  Max |  Amount  |  Total Amount
-----------------------------------------------
  4    | 3    |   5  |    12    |     48       (Count * Amount)

For Example 2: Count (4) crosses all the limits so we need to consider the amount from higher range row and the result like below. 

Count  | Min  |  Max |  Amount  |  Total Amount
-----------------------------------------------
  4    | 2    |   2  |    10    |     20        (Max * Amount)

Upvotes: 1

Views: 2385

Answers (3)

Mahesh Kola
Mahesh Kola

Reputation: 21

Is this what you are expecting ?

Sample Data 

SELECT * INTO #TAB FROM (
 SELECT 4 AS COUNTNO ,1 AS MINNO,1 AS MAXNO ,5  AS AMOUNT
    UNION ALL 
 SELECT 4,2,2,10
   UNION ALL 
 SELECT 4,3,5,12
   UNION ALL 
 SELECT 4,6,8,15 )AS A

Query:

SELECT T.*,
IIF(COUNTNO BETWEEN MINNO AND MAXNO , CountNO * Amount ,MAXNO*AMOUNT)ExpectedOp FROM  #TAB T

Upvotes: 2

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

SQL Fiddle

MS SQL Server 2017 Schema Setup:

create table test(count int ,
                  min int,
                  max int,
                 amount int)
insert test values(4,3,5,12),
                  (4,2,2,10)

Query 1:

SELECT *,CASE
            WHEN count >min and count<=max
               THEN Count * Amount
               ELSE Max * Amount
       END as TotalAmount
FROM test

Results:

| count | min | max | amount | TotalAmount |
|-------|-----|-----|--------|-------------|
|     4 |   3 |   5 |     12 |          48 |
|     4 |   2 |   2 |     10 |          20 |

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I think you want:

select top (1) t.*, (amount * count)
from t
order by (case when count between min and max then 1 else 2 end),
          amount desc;

Note: count, min, and max are poor choices for column names because they are SQL built-in functions.

Upvotes: 3

Related Questions