SQL - how to select records without using a cursor?

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

Where  this second dataset shows the resultset of a select that I would like to apply to the first larger dataset.

How could I go about doing this?

any help appreciated
Myles
mylesevenAsked:
Who is Participating?
 
ee_rleeConnect With a Mentor Commented:
another correction
SELECT 
     ClientAssetLinkID, ClientAssetSnapshotID, SnapshotDate, UnitsHeld 
FROM urtable AS A
WHERE 
     UnitsHeld > (SELECT TOP 1 B.UnitsHeld FROM urtable AS B 
                         WHERE B.SnapshotDate<A.SnapshotDate AND B.ClientAssetLinkID=A.ClientAssetLinkID
                               AND B.UnitsHeld <> A.UnitsHeld
                         ORDER BY B.SnapshotDate DESC) AND
     UnitsHeld > (SELECT TOP 1 C.UnitsHeld FROM urtable AS C
                         WHERE C.SnapshotDate>A.SnapshotDate AND C.ClientAssetLinkID=A.ClientAssetLinkID
                         ORDER BY C.SnapshotDate ASC)

Open in new window

0
 
YiogiCommented:
miles can you be more specific? How do you know when there was and when there wasn't a reduction in units held?
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 in answer to your question Yiogi, 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.

Is that a bit clearer?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
YiogiCommented:
Much clearer thank you :) And sorry for typing your name wrong in the first post.

You can use a udf where u'd pass the client asset linkid and snapshot id and that would check the record you passed with the next record in your table. But that would be more strain to the SQL Server than simply using a cursor and saving what you want in a temporary table to be returned.

Hope that helps you, I can't think of any other way.
0
 
mylesevenAuthor Commented:
Thanks for your help Yiogi,
I will have a play round with your suggestion.
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
 
ee_rleeCommented:
what is the pk for the table?
are the records with the same clientassetlinkid always grouped together when sorted by the pk?
0
 
mylesevenAuthor Commented:
The Pk is the ClientAssetLinkID,
The records are ordered by the ClientAssetLinkID.

0
 
ee_rleeCommented:
try this
SELECT 
     ClientAssetLinkID, ClientAssetSnapshotID, SnapshotDate, UnitsHeld 
FROM urtable AS A
WHERE 
     UnitsHeld > (SELECT TOP 1 B.UnitsHeld FROM urtable AS B 
                         WHERE B.SnapshotDate<A.SnapshotDate ORDER BY C.SnapshotDate DESC) AND
     UnitsHeld < (SELECT TOP 1 C.UnitsHeld FROM urtable AS C
                         WHERE C.SnapshotDate>A.SnapshotDate ORDER BY C.SnapshotDate ASC)

Open in new window

0
 
mylesevenAuthor Commented:
Sorry i made another mistake the PK is the ClientAssetSnapshot,
and they are ordered by the ClientAssetLinkID and the CLientAssetSnapshotID
0
 
mylesevenAuthor Commented:
Thanks very much for your suggestion ee_rlee.
I think you are heading in the right direction, I got an error when I tried your script though because you accidentally put 'ORDER BY C.SnapshotDate DESC' where 'C' had not been defined then.
I changed it to read ORDER BY A.SnapshotDate DESC
but the result set was not correct...
0
 
ee_rleeCommented:
made a mistake, pls try again
SELECT 
     ClientAssetLinkID, ClientAssetSnapshotID, SnapshotDate, UnitsHeld 
FROM urtable AS A
WHERE 
     UnitsHeld > (SELECT TOP 1 B.UnitsHeld FROM urtable AS B 
                         WHERE B.SnapshotDate<A.SnapshotDate ORDER BY B.SnapshotDate DESC) AND
     UnitsHeld < (SELECT TOP 1 C.UnitsHeld FROM urtable AS C
                         WHERE C.SnapshotDate>A.SnapshotDate ORDER BY C.SnapshotDate ASC)

Open in new window

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
      select * from TestSnapshot where SnapshotID in (5, 9, 11, 13, 16)
0
 
mylesevenAuthor Commented:
thanks again, but still no luck,
I tried your query on the test data, modifying it slightly, but it also did not retun what I wanted, but I think we are making progress:

SELECT 
ClientAssetLinkID, SnapshotID, 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) AND
UnitsHeld < (SELECT TOP 1 C.UnitsHeld 
FROM TestSnapshot AS C
WHERE C.CLientAssetLinkID = A.ClientAssetLinkID
and C.SnapshotDate > A.SnapshotDate 
ORDER BY C.SnapshotDate ASC)

Open in new window

0
 
ee_rleeCommented:
i missed something on the query, can you try again? i cannot test it right now bec i don't have sql srvr installed on this comp.
SELECT 
     ClientAssetLinkID, ClientAssetSnapshotID, SnapshotDate, UnitsHeld 
FROM urtable AS A
WHERE 
     UnitsHeld > (SELECT TOP 1 B.UnitsHeld FROM urtable AS B 
                         WHERE B.SnapshotDate<A.SnapshotDate AND B.ClientAssetLinkID=A.ClientAssetLinkID
                         ORDER BY B.SnapshotDate DESC) AND
     UnitsHeld < (SELECT TOP 1 C.UnitsHeld FROM urtable AS C
                         WHERE C.SnapshotDate>A.SnapshotDate AND C.ClientAssetLinkID=A.ClientAssetLinkID
                         ORDER BY C.SnapshotDate ASC)

Open in new window

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:
Wow you are doing pretty well without having SQL server in front of you!

However I tested it on the test data above and it only returned the Snapshot record 7
0
 
ee_rleeCommented:
i found where it went wrong, pls try again
SELECT 
     ClientAssetLinkID, ClientAssetSnapshotID, SnapshotDate, UnitsHeld 
FROM urtable AS A
WHERE 
     UnitsHeld > (SELECT TOP 1 B.UnitsHeld FROM urtable AS B 
                         WHERE B.SnapshotDate<A.SnapshotDate AND B.ClientAssetLinkID=A.ClientAssetLinkID
                               AND B.UnitsHeld <> A.UnitsHeld
                         ORDER BY B.SnapshotDate DESC) AND
     UnitsHeld < (SELECT TOP 1 C.UnitsHeld FROM urtable AS C
                         WHERE C.SnapshotDate>A.SnapshotDate AND C.ClientAssetLinkID=A.ClientAssetLinkID
                               AND C.UnitsHeld <> A.UnitsHeld
                         ORDER BY C.SnapshotDate ASC)

Open in new window

0
 
mylesevenAuthor Commented:
sorry it still only returns record 7...
0
 
ee_rleeCommented:
i again saw another mistake, pls try again
SELECT 
     ClientAssetLinkID, ClientAssetSnapshotID, SnapshotDate, UnitsHeld 
FROM urtable AS A
WHERE 
     UnitsHeld > (SELECT TOP 1 B.UnitsHeld FROM urtable AS B 
                         WHERE B.SnapshotDate<A.SnapshotDate AND B.ClientAssetLinkID=A.ClientAssetLinkID
                               AND B.UnitsHeld <> A.UnitsHeld
                         ORDER BY B.SnapshotDate DESC) AND
     UnitsHeld > (SELECT TOP 1 C.UnitsHeld FROM urtable AS C
                         WHERE C.SnapshotDate>A.SnapshotDate AND C.ClientAssetLinkID=A.ClientAssetLinkID
                               AND C.UnitsHeld <> A.UnitsHeld
                         ORDER BY C.SnapshotDate ASC)

Open in new window

0
 
mylesevenAuthor Commented:
Thanks ee_rlee
yours works well done,
I also found that it works with out the second and statement (see below)

however the points are yours, thanks alor for your help


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 )
0
 
ee_rleeCommented:
glad that it worked.

but without the 2nd line, you would get all records which increased in units held. thus if it increased for 2 consecutive date, both will be returned.
0
All Courses

From novice to tech pro — start learning today.