CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Oliver
Oliver

Reputation: 817

Sql server difference instead of sum

I have a table with 5 columns : 

PK_Column, FK_MeterID, Date, Index, Value

and I have this query which is working fine : 

SELECT FK_MeterID,
       sum(Value) as 'Total',
       @p_Month,
       @p_Year
FROM ValuesHistory
WHERE month(Date) = @p_Month
      and year(Date)  = @p_Year
GROUP BY  FK_MeterID

However instead of summing the values over a month, I would like to do

a difference of indexes between the last index of the month and the first index of the

month. (It should return the same result)

How can I make this query using a difference ?

Thanks

Upvotes: 1

Views: 878

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460228

Sounds as if you were looking for aggregate functions.

WITH CTE AS
(
    SELECT RN = ROW_NUMBER() OVER (
                  PARTITION BY FK_MeterID
                  ORDER BY Date, PK_Column),
           TOTAL  = SUM(Value) OVER (PARTITION BY FK_MeterID),
           MINVAL = MIN(Value)  OVER (PARTITION BY FK_MeterID),
           MAXVAL = MAX(Value)  OVER (PARTITION BY FK_MeterID),
           PK_Column, FK_MeterID, [Date], [Index], Value
    FROM ValuesHistory
    WHERE month(Date) = @p_Month
      AND year(Date)  = @p_Year
)
SELECT DIFFERENCE = MAXVAL - MINVAL, CTE.*
FROM CTE

Upvotes: 1

Related Questions