SQL Server : special calculation for value in a view

user_6499321 0
Viewed 51

I would like to calculate the column (calculated) as below:

enter image description here

The Calculated value is twice the Value, as long as there is not a lower value that has a Calculated value that is higher than Value, otherwise it should be zero.

Basically the column Calculated should have as its MAX a value that would be at least half the size of all values of its category (Column1).

I have a view in a SQL Server database, that means for me that I can not use ORDER BY.

My example values:

CREATE TABLE test_table2020 
(
    Column1 INT,
    Column2 VARCHAR(1),
    Value INT
)

I populate it like:

INSERT INTO test_table2020 
VALUES  (1, 'A', 10), (1, 'A', 12),
        (1, 'A', 22), (1, 'A', 23),
        (1, 'A', 13), (1, 'A', 24),
        (2, 'B', 15), (3, 'C', 16),
        (3, 'C', 28), (3, 'C', 31),
        (4, 'D', 9) , (4, 'D', 9)

I think that I from here would be able to get the max(calculated) for every different Column1.

I do not really have any idea what to do, that will work in the case with a few rows.

This will show the calculated values without the zeros:

SELECT *, value * 2 AS Calculated FROM test_table2020

The thing is for calculating what value stuff should be uppgraded to without overdoing it.

partition case view sql-server
1 Answers

The window functions in concert with a CASE will help here

EDIT: Not clear if Column2 should be embedded in the partition the pattern is a little ambiguous.

Example

Select *
       ,Calculated =  case when (Value*1.5<max(Value) over (partition by column1) 
                                 and  sum(1) over (partition by column1) > 1 )
                                 or ( avg(value) over (partition by column1) = value   )
                           then Value * 2
                           else 0 end
 From  test_table2020

Returns

enter image description here

user_15700000
Related