7

I'm trying to create a view. I have some data in my SQL Server database, and I want to order them by their mark1 and mark2. Now when I sort them I want to select the mark1 who has the lowest mark2 and calculate what's the deviation from other mark1s. The table is looking right now like this, but without deviation.

|   mark1    |    mark2    |      mass    ||    deviation |
|:-----------|------------:|:------------:||:------------:|
| 0001       |      10000  |     10,0     ||    min0001   |
| 0001       |      15000  |     12,0     ||    +20%      |
| 0002       |      11200  |     18,0     ||    min0002   |
| 0002       |      13000  |     35,5     ||    +97.2%    |
| 0002       |      14565  |     85,5     ||    375%      |
| 0003       |      15004  |     15,4     ||    min0003   |
| 0003       |      21250  |     17,4     ||    +12.9%    |
| 0003       |      60764  |     16,4     ||    6.49%     |

And now, as you see it's ordered by mark1 and mark2. Now, my problem is the deviation column, I need it to take mark1, Let's say 0001 (his mark2 is 10000) and find the lowest mass and find the deviation from other 0001 (15000). As you can see the in the table.

3
  • 1
    Which version of SQL Server are you using? The newer versions have some functions that would make this much easier. May 15, 2015 at 12:54
  • SQL 2014 version...I hope it helps...
    – sharp
    May 15, 2015 at 12:58
  • Do you want to compare with the lowest mass or with the mass of the lowest mark2? May 17, 2015 at 11:45

2 Answers 2

8

FIRST_VALUE (Transact-SQL)

Returns the first value in an ordered set of values in SQL Server 2014.

Partition on mark1 and order by mark2.

Something like this should do it for you:

select T.*,
       100 * (T.mass - T.FirstMass) / T.FirstMass as Deviation
from (
     select T.*,
            first_value(T.mass) over(partition by T.mark1 order by T.mark2) as FirstMass
     from dbo.YourTable as T    
     ) as T;

SQL Fiddle

3
  • 2
    Mikael beat me to it :) May 15, 2015 at 13:23
  • Yes, realized it and removed my comment ;) The data and wording suggest your solution (except for that "lowest mass", they need to clarify.) May 17, 2015 at 11:47
  • 1
    @ypercube what comment :) May 17, 2015 at 11:48
0

I think this query will give the results:

SELECT 
    *,
    (t.mass / MIN(mass) OVER (PARTITION BY mark1) - 1) * 100 AS devValue,
    CASE 
        WHEN t.mass = MIN(mass) OVER (PARTITION BY mark1) THEN 'min' + mark1 
        ELSE 
            CASE 
                WHEN t.mass > MIN(mass) OVER (PARTITION BY mark1) THEN '+'
                ELSE '-' 
            END +
            CONVERT(varchar, CONVERT(DECIMAL(10,2), (t.mass / MIN(mass) OVER (PARTITION BY mark1) - 1) * 100)) + '%'
     END AS deviation
FROM
    t
ORDER BY
    t.mark1, t.mark2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.