Databases
StackOverflow2013
Postsdbo
POHelp with finding the difference (delta) from a value returned from the last two records
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHelp with finding the difference (delta) from a value returned from the last two records
    primarykey
    data
    text
    <p>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. </p> <p>here is my query:</p> <pre><code>;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&lt;=2 ) SELECT case when max(case rn when 1 then RawStatus end) &gt; 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) &gt; 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) &gt;= 1 </code></pre> <p>What I'm after is to get the Delta &amp; NetObjectID returned. Each time I try, I get errors. <code>data.NetObjectID is invalid in the select list because it is not contained in either an aggregate function or the group by clause.</code></p> <p>If I try adding group by etc.. to the end of the query I get further error complaining about the word 'group'. </p> <p>I'm relatively new to SQL and I am picking things up as I go. Any help would be gratefully received.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAndomar
    1. USMarkKneen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload