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?
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.

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
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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
ee_rleeCommented:
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

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
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
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
Query Syntax

From novice to tech pro — start learning today.