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.

  • 1
    Which version of SQL Server are you using? The newer versions have some functions that would make this much easier. – Chris Woods May 15 '15 at 12:54
  • SQL 2014 version...I hope it helps... – FiN May 15 '15 at 12:58
  • Do you want to compare with the lowest mass or with the mass of the lowest mark2? – ypercubeᵀᴹ May 17 '15 at 11:45
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

  • 2
    Mikael beat me to it :) – Chris Woods May 15 '15 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.) – ypercubeᵀᴹ May 17 '15 at 11:47
  • 1
    @ypercube what comment :) – Mikael Eriksson May 17 '15 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.