Help find the difference (delta) from the value returned from the last two records

I am using MS SQL 2005 and I created a CTE query to return values ​​from the last two records. I then use that to find the delta of two shapes. I have a working query, but I'm having problems getting nothing but a delta shape.

here is my request:

;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 need is to return Delta and NetObjectID. Every 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 to add a group, etc., at the end of the request, I get an additional error, complaining about the word "group".

I am relatively new to SQL and I pick things up as I go. Any help would be greatly appreciated.

0


source to share


2 answers


see if something like this would 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

      

0


source


Sorry I'm very new here and I'm not sure about the CTE's answers, but it looks like after you have defined Data, you choose the case ... like Delta FROM .... If you only have Delta in your choose operator. Again, sorry if I leave the base.



0


source







All Articles