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

brad2575

You will need to build it out with the correct table logic but this will work

-- this will select the highest number that is NOT the first number
Select top 1 * From table A
Where UID NOT IN (
 -- this will select the highest number
 Select Top 1 UID from Table A 
)

Open in new window

laneybeal

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.

looper8

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.

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

Open in new window

ASKER CERTIFIED SOLUTION

brad2575

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial