[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2008-02-04
10
Medium Priority
?
230 Views
Last Modified: 2010-03-19
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

0
Comment
Question by:myleseven
  • 8
  • 2
10 Comments
 
LVL 10

Expert Comment

by:dis1931
ID: 20819857
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
 

Author Comment

by:myleseven
ID: 20819892
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
 

Author Comment

by:myleseven
ID: 20819922
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:myleseven
ID: 20819933
But the above code snippet does not do what I want it to do
0
 
LVL 10

Expert Comment

by:dis1931
ID: 20820078
what is the criteria?
0
 

Author Comment

by:myleseven
ID: 20820080
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
 

Author Comment

by:myleseven
ID: 20820133
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
 

Author Comment

by:myleseven
ID: 20820252
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
 

Author Comment

by:myleseven
ID: 20820391
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
 

Accepted Solution

by:
myleseven earned 0 total points
ID: 20820577
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question