Help with finding the difference (delta) from a value returned from the last two records

Question

Hi,
I'm using MS SQL 2005 and I have created a CTE query to return values from the last two records. I then use this to find the delta of two figures returned. I have a working query of sorts but I'm having problems getting anything other than the delta figure.

here is my query:

;with data as(
    SELECT 
     NetObjectID,
     RawStatus,
     RowID,
     rn 
    from( 
     SELECT 
      CustomPollerAssignmentID AS NetObjectID,
      RawStatus,
      RowID,
      row_number() over(order by DateTime desc)as rn 
     FROM CustomPollerStatistics_Detail 
     WHERE
      (CustomPollerAssignmentID='a87f531d-4842-4bb3-9d68-7fd118004356')
    ) x where rn<=2
)
SELECT 
    case when 
     max(case rn when 1 then RawStatus end) > max(case rn when 2 then RawStatus end) 
    then 
     max(case rn when 1 then RawStatus end) - max(case rn when 2 then RawStatus end) 
    else 
     max(case rn when 2 then RawStatus end) - max(case rn when 1 then RawStatus end) 
    end as Delta
from data having 
(SELECT 
    case when 
     max(case rn when 1 then RawStatus end) > max(case rn when 2 then RawStatus end) 
    then 
     max(case rn when 1 then RawStatus end) - max(case rn when 2 then RawStatus end) 
    else 
     max(case rn when 2 then RawStatus end) - max(case rn when 1 then RawStatus end) 
    end
from data) >= 1

What I'm after is to get the Delta & NetObjectID returned. Each time I try, I get errors. data.NetObjectID is invalid in the select list because it is not contained in either an aggregate function or the group by clause.

If I try adding group by etc.. to the end of the query I get further error complaining about the word 'group'.

I'm relatively new to SQL and I am picking things up as I go. Any help would be gratefully received.

Answer 1

Sorry, I'm very new here and I'm unsure of CTE queries, however it looks like after you define Data, you are selecting case ... as Delta FROM.... Meaning you only have Delta in your select statement. Again, sorry if I'm way off base.

Answer 2

see if something like this will work.

;with data as
(    
    SELECT         
     NetObjectID,        
     RawStatus,        
     RowID,        
     rn     
    from
    (               
     SELECT                 
      CustomPollerAssignmentID AS NetObjectID,                
      RawStatus,                
      RowID,                
      row_number() over(order by DateTime desc)as rn         
     FROM CustomPollerStatistics_Detail         
     WHERE                
     (
      CustomPollerAssignmentID='a87f531d-4842-4bb3-9d68-7fd118004356'
     )    
    ) x 
    where rn<=2
)
select
    NetObjectID,
    max(RawStatus)-min(RawStatus) as Delta
from data
group by NetObjectID