January 9, 2002 at 11:04 am
The following query shows movements for each liability
select t1.LiabilityID,t1.DateTimePassed,t1.PassedTo
from tblliabilitymovement as t1
Order BY t1.LiabilityID,t1.DateTimePassed DESC
and results are:
LiabilityID DateTimePassed PassedTo
----------- ------------------ ---------
11431 Nov 21 2001 5:43PM 5
11431 Nov 21 2001 5:41PM 4
11431 Nov 4 2001 1:11PM 7
I'm trying to generate a query whereby
- each liabilityID is compared with next row to check if it's the same
- if they are the same then PassedTo field is added to new column(From Team) and the PassedTo
from previous row is added to (ToTeam) column
-if the liabilityID's don't match then FromTeam = 0
LiabilityID FromTeam ToTeam
----------- ------------- -----------
11431 4 5
11431 7 4
11431 0 7
This is possible by VB using ADO recorsets but is it possible via
SQL Querying without the use of cursors
January 9, 2002 at 12:22 pm
It should look something like this. I changed your column names so that I could use them myself when writing this. Ultimately this is called Correlated SubQueries and is based on Set Based Operations which is what SQL is best at. See article by Itzik Ben-Gan in November 2001 of SQL Server Mag for great lesson. Hope this helps.
select
a.LiabilityID,
a.DateAndTime,
(select b.PassedTo
from LiabilityMvmt b
where
b.LiabilityID = a.LiabilityID
and
b.DateAndTime = (select max(c.DateAndTime)
from LiabilityMvmt c
where
c.LiabilityID = a.LiabilityID
and
c.DateAndTime < a.DateAndTime)
) as FromTeam,
a.PassedTo
from LiabilityMvmt a
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply