How do I select only records where there has been a reduction in value?

Hello myleseven,

Not sure I understand what you are trying to find out?  The ones below seem like they have the same UnitsHeld as the dataset above?

Thanks,

dis1931

Sorry I wasn't very clear.
The second dataset shows the resultset of a select that I would like to apply to the first dataset.
does that make sence?

I.e. if we called the 1st dataset ClientAssetSnapshot then the select might start to look like the code snippet supplied

select cas.*
from ClientAssetSnapshot cas
where 
cas.ConfirmedUnitsHeld  >= (select top 1 b.ConfirmedUnitsHeld 
from ClientAssetSnapshot  b
where cas.ClientAssetLinkID = b.ClientAssetLinkID 
and cas.SnapshotDate < b.SnapshotDate 		
order by b.ClientAssetLinkID,b.ClientAssetSnapshotID)

Select allOpen in new window

But the above code snippet does not do what I want it to do

what is the criteria?

Sorry when I looked closer at the second dataset I can see that I made a mistake, it should return these values:
ClientAssetLinkID ClientAssetSnapshotID SnapshotDate              UnitsHeld
990350928         9786282               2006-02-28 16:32:38.407      574.0000
990350928         12708331              2007-01-13 17:14:32.507      1343.0000
990350928         13495591              2007-04-02 16:42:37.760       1201.0000
990350929         9313962               2005-11-29 12:20:15.830      557.0000
990350929         12708331              2007-01-13 17:14:32.507      1343.0000

So to tell when there was and when there wasn't a reduction in units held we need to be able to loop through each row and when the UnitsHeld of the next row is less than the UnitsHeld of the previous Row and they are both of the SAME ClientAssetLinkID then we need to return this row.

I asked a similar question a while ago:
https://www.experts-exchange.com/questions/23019833/SQL-how-to-select-records-without-using-a-cursor.html
As you can see a similar thing can be done inside one select and I thought I would be able to apply similar logic here but I can't think how. What do you think?
Myles

Here is some MetaData we can test with:
set nocount on  
 drop Table TestSnapshot
      Create TABLE TestSnapshot(      SnapshotID            int IDENTITY (1, 1) NOT NULL ,
                                        ClientAssetLinkID   int Null,
                                        UnitsHeld              numeric(15,4) null,
                                        SnapshotDate        DateTime Null)

      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1001,0, '2006-28-04 15:52:02.010')
      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1001,0, '2006-28-04 15:52:02.023')
      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1001,0, '2006-09-05 16:54:47.090')
      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1001,5721.36, '2006-12-05 08:53:47.090')
      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1001,5721.36, '2006-09-08 15:52:02.010')
      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1001,0, '2006-11-09 08:24:02.010')
      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1001,1097.56, '2006-11-10 15:52:02.010')
      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1001,5721.36, '2006-16-10 15:52:02.010')
      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1001,5721.36, '2006-20-10 15:52:02.010')
      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1001,1097.56, '2006-25-11 15:52:02.010')

      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1002,5721.36, '2006-09-08 15:52:02.010')
      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1002,0, '2006-11-09 08:24:02.010')
      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1002,1097.56, '2006-11-10 15:52:02.010')
      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1002,500, '2006-12-10 15:52:02.010')
      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1002,5721.36, '2006-16-10 15:52:02.010')
      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1002,5721.36, '2006-20-10 15:52:02.010')
      insert into TestSnapshot(ClientAssetLinkID, UnitsHeld, SnapshotDate)
      Values(1002,1097.56, '2006-25-11 15:52:02.010')

--dataset1
      select * from TestSnapshot
--end result

If I do this (see code below) I get the records where the change was made, but I need the records before this change...

SELECT 
SnapshotID, ClientAssetLinkID, SnapshotDate, UnitsHeld 
FROM TestSnapshot AS A
WHERE 
UnitsHeld < 
(SELECT TOP 1 B.UnitsHeld 
FROM TestSnapshot AS B 
WHERE B.CLientAssetLinkID = A.ClientAssetLinkID
and B.SnapshotDate < A.SnapshotDate 
ORDER BY B.SnapshotDate DESC) 

Select allOpen in new window