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

I have a recordset that looks like this:
ClientAssetLinkID ClientAssetSnapshotID SnapshotDate              UnitsHeld
990350928         9303104               2005-11-28 08:57:27.720      0.0000
990350928         9313962               2005-11-29 12:20:15.830      557.0000
990350928         9527044               2005-12-29 08:30:51.537      574.0000
990350928         9786282               2006-02-28 16:32:38.407      574.0000
990350928         9990382               2006-03-27 12:37:37.047      0.0000
990350928         10260128              2006-05-01 11:22:46.023      586.0000
990350928         10841440              2006-07-08 13:27:08.443       604.0000
990350928         10915964              2006-07-13 09:37:56.320       1281.0000
990350928         11829349              2006-10-13 16:27:27.347      1281.0000
990350928         12708331              2007-01-13 17:14:32.507      1343.0000
990350928         12710651              2007-01-15 08:34:23.843      1201.0000
990350928         13495591              2007-04-02 16:42:37.760       1201.0000
990350928         13538445              2007-04-04 12:39:20.487      1074.0000
990350928         14130544              2007-06-05 01:32:01.507      1074.0000
990350929         9303104               2005-11-28 08:57:27.720      0.0000
990350929         9313962               2005-11-29 12:20:15.830      557.0000
990350929         9990382               2006-03-27 12:37:37.047      0.0000
990350929         10260128              2006-05-01 11:22:46.023      586.0000
990350929         12708331              2007-01-13 17:14:32.507      1343.0000
990350929         14130544              2007-06-05 01:32:01.507      1074.0000

I need to select the records from this dataset that appeared before there was a reduction in UnitsHeld eg:
ClientAssetLinkID ClientAssetSnapshotID SnapshotDate              UnitsHeld
990350928         9313962               2005-11-29 12:20:15.830      557.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

How could I go about doing this?

any help appreciated
Myles

mylesevenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dis1931Commented:
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
0
mylesevenAuthor Commented:
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?
0
mylesevenAuthor Commented:
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)

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mylesevenAuthor Commented:
But the above code snippet does not do what I want it to do
0
dis1931Commented:
what is the criteria?
0
mylesevenAuthor Commented:
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.
0
mylesevenAuthor Commented:
I asked a similar question a while ago:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23019833.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
0
mylesevenAuthor Commented:
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
0
mylesevenAuthor Commented:
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) 

Open in new window

0
mylesevenAuthor Commented:
I solved it:
here is the answer
SELECT 
SNAPSHOTID, CLIENTASSETLINKID, UNITSHELD, SNAPSHOTDATE 
FROM TESTSNAPSHOT AS A
WHERE 
A.UNITSHELD > (SELECT TOP 1 B.UNITSHELD 
FROM TESTSNAPSHOT AS B 
WHERE B.SNAPSHOTDATE > A.SNAPSHOTDATE 
AND B.CLIENTASSETLINKID=A.CLIENTASSETLINKID
ORDER BY B.SNAPSHOTDATE ) 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.