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.
source to share
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
source to share