looper8
asked on
SQL query help needed: Getting next highest record
I have a table with data as follows (simplified version):
UID AssetNo. MoveDate From To
312513 149551 19/12/2008 12:20:00 1621 1305
283660 149551 29/06/2008 00:52:00 1442 1621
283029 149551 23/06/2008 16:32:00 40 2093175289
283032 149551 23/06/2008 16:32:00 2093175289 1442
A query on this table returns just the first of the rows shown above. The To field will always be 1305. From that query how can I requery the table to give me the MoveDate and From fields in the 2nd row? The 2nd row will always be the next record chronologically. But although the records will be in this order in the table they may or may not be the first two records.
Thanks
Peter
UID AssetNo. MoveDate From To
312513 149551 19/12/2008 12:20:00 1621 1305
283660 149551 29/06/2008 00:52:00 1442 1621
283029 149551 23/06/2008 16:32:00 40 2093175289
283032 149551 23/06/2008 16:32:00 2093175289 1442
A query on this table returns just the first of the rows shown above. The To field will always be 1305. From that query how can I requery the table to give me the MoveDate and From fields in the 2nd row? The 2nd row will always be the next record chronologically. But although the records will be in this order in the table they may or may not be the first two records.
Thanks
Peter
Could I get some clarification.
Your query is only showing the first row?
You'd like to query the table to give you the MoveDate and From fields in the second row?
If that is all, the query is:
SELECT movedate, from
FROM table
WHERE UID = 283660;
If you are trying to get a piece of code that finds the 'next' record relative to the record you just pulled (cycling through the table), you will need to create a cursor in a PL/SQL format.
I think answering your question will be simpler if you could include the query code you are using.
Your query is only showing the first row?
You'd like to query the table to give you the MoveDate and From fields in the second row?
If that is all, the query is:
SELECT movedate, from
FROM table
WHERE UID = 283660;
If you are trying to get a piece of code that finds the 'next' record relative to the record you just pulled (cycling through the table), you will need to create a cursor in a PL/SQL format.
I think answering your question will be simpler if you could include the query code you are using.
ASKER
Thanks guys ...
brad2575: The first row may be the latest row added and so have the highest UID, but it might not be.
laneybeal: What I've given is just sample data so the UID number could be anything.
I've attached the full code below.
At --1 data is inserted into a temp table, this is like the data I showed before.
This is to do with stock movements. When an item is moved from anywhere to a certain location (the 1305 I mentioned before, although it's a few other places too) then this bit of code puts a record of that event into the temp table. Now I've been asked to identify not just the movement that put it in that place but also the previous movement.
brad2575: The first row may be the latest row added and so have the highest UID, but it might not be.
laneybeal: What I've given is just sample data so the UID number could be anything.
I've attached the full code below.
At --1 data is inserted into a temp table, this is like the data I showed before.
This is to do with stock movements. When an item is moved from anywhere to a certain location (the 1305 I mentioned before, although it's a few other places too) then this bit of code puts a record of that event into the temp table. Now I've been asked to identify not just the movement that put it in that place but also the previous movement.
declare @EMail varchar(100)
--declare @strEMail varchar(400)
declare @Spacer char(2)
set @Spacer = ' '
declare @SQLStart varchar(200)
set @SQLStart = convert(char(8), 'Contract')+ @Spacer + convert(char(40),'Part Code / Description') + @Spacer + convert(char(15), 'Serial No.') + @Spacer + convert(char(10), 'Asset No.') + @Spacer + convert(char(50),'Moved To / From / By')
declare @SQLPart varchar(250)
declare @strSQL nvarchar(4000)
set @strSQL = ''
--set @strEMail = 'steve.dawson@telent.com, lloyd.hutchinson@telent.com, samantha.peters@telent.com, sharon.backhouse@telent.com, alhaji.carew@telent.com, paul.instone@telent.com, krishna.pentayya@telent.com, michael.hayward@telent.com'
--set @strEmail = 'peter.fender@telent.com'
create table #tmpResults
(
fldAssetUID int,
fldFromLocationUID int,
fldToLocationUID int,
fldName char(25)
)
-- 1
insert #tmpResults
select AM.fldAssetUID, fldFromLocationUID, fldTolocationUID, left(rtrim(fldSurname) + ', ' + rtrim(fldFirstName),25)
from tblAssetMovement AM
inner join tblAssets A
on A.fldAssetUID = AM.fldAssetUID
inner join tblContract C
on A.fldContractUID = C.fldContractUID
inner join tblStaff S
on AM.fldUserUID = S.fldStaffUID
where fldTolocationTypeUID = 1 -- Stores
and fldToLocationUID in (1305, 3146, 3147, 3148, 3149) -- NOSC - Lost, NOSC - Duplicate, NOSC - Cannabalised, NOSC - Project Services, NOSC - Administrative
and fldMovementDate between getdate() - 132 and getdate()
and C.fldContractGroupUID = 1 -- LUL
declare Results cursor
for
select distinct convert(char(8), fldContractCode) + @Spacer + convert(char(40),fldPartCode) + @Spacer + convert(char(15), fldSerialNo) +@Spacer + convert(char(10), isnull(fldLULAssetNo,'')) + @Spacer + convert(char(50), 'From: ' + (SELECT fldStoresLocation FROM tblStoresLocation WHERE tmpResults.fldFromLocationUID = tblStoresLocation.fldStoresLocationUID) + @Spacer + 'To: ' + (SELECT fldStoresLocation FROM tblStoresLocation WHERE tmpResults.fldToLocationUID = tblStoresLocation.fldStoresLocationUID)) + char(13) + char(10) + convert(char(8), '') + @Spacer + convert(char(40),fldPart) + @Spacer + convert(char(15), '') +@Spacer + convert(char(10), '') + @Spacer + convert(char(25),fldName) + char(13) + char(10)
from #tmpResults tmpResults
inner join tblAssets
on tblAssets.fldAssetUID = tmpResults.fldAssetUID
inner join tblPart
on tblAssets.fldPartUID = tblPart.fldPartUID
inner join tblContract C
on tblAssets.fldContractUID = C.fldContractUID
order by 1
declare @CurrentContract varchar(10)
declare @PreviousContract varchar(10)
declare @CSM varchar(35)
declare @Subject varchar(35)
set @CurrentContract = ''
set @PreviousContract = ''
set @Subject = 'Parts Moved to NOSC: '
open Results
fetch next from results into @SQLPart
while @@fetch_Status = 0
begin
set @CurrentContract = LEFT(@SQLPart, 6)
--print @CurrentContract + ', ' + @PreviousContract
if @strSQL = ''
begin
set @strSQL = @SQLPart
end
else
begin
if @CurrentContract <> @PreviousContract
begin
select @CSM = (SELECT s.fldEmail FROM tblContract c JOIN tblStaff s ON c.fldContractManagerUID = s.fldStaffUID WHERE c.fldContractCode = @PreviousContract)
set @Subject = @Subject + @PreviousContract
print @CSM
set @strSQL = @SQLStart + char(13) + char(10) + char(13) + char(10) + @strSQL
exec sp_send_cdosysmail 'DoNotReply@telent.com', @CSM, @Subject, @strSQL, 'lloyd.hutchinson@telent.com, steve.dawson@telent.com', 'peter.fender@telent.com'
--exec sp_send_cdosysmail 'DoNotReply@telent.com', 'peter.fender@telent.com', @Subject, @strSQL, 'peter.fender@telent.com'
set @strSQL = ''
set @Subject = 'PLEASE IGNORE THIS EMAIL: IT IS A TEST'
--set @Subject = 'Parts Moved to NOSC: '
end
set @strSQL = @strSQL + char(13) + char(10) + @SQLPart
end
set @PreviousContract = @CurrentContract
fetch next from results into @SQLPart
end
close results
deallocate results
if @strSQL <> ''
begin
select @CSM = (SELECT s.fldEmail FROM tblContract c JOIN tblStaff s ON c.fldContractManagerUID = s.fldStaffUID WHERE c.fldContractCode = @PreviousContract)
set @Subject = @Subject + @CurrentContract
print @CSM
set @strSQL = @SQLStart + char(13) + char(10) + char(13) + char(10) + @strSQL
exec sp_send_cdosysmail 'DoNotReply@telent.com', @CSM, @Subject, @strSQL, 'lloyd.hutchinson@telent.com, steve.dawson@telent.com', 'peter.fender@telent.com'
--exec sp_send_cdosysmail 'DoNotReply@telent.com', 'peter.fender@telent.com', @Subject, @strSQL, 'peter.fender@telent.com'
end
--select * from #tmpresults
drop table #tmpResults
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window