Please help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please help

Author

Topic

SteevR
Starting Member

18 Posts

Posted - 2008-09-25 : 10:45:52

Hi,

I have an asset tracker system set up. I want to create an index of assets (bound ASP.Net grid) and show their current location. I have the following three simplified tables:

tblAssetManagement (main table)
assetID
assetTag
serialNumber

tblAssetTracker (tracks asset location history)
trackID
assetID
trackDate
locationID

tblLocations
locationID
location

Now, I have bound to the grid by using:

SELECT assetTag, serialNumber,
(SELECT TOP 1 tblLocations.location FROM tblAssetTracker LEFT OUTER JOIN tblLocations ON tblAssetTracker.locationID = tblLocations.locationID WHERE tblAssetTracker.assetID = tblAssetManagement.assetID ORDER BY tblAssetTracker.trackDate DESC) AS currentLocation,
(SELECT TOP 1 trackDate FROM tblAssetTracker WHERE tblAssetTracker.assetID = tblAssetManagement.assetID ORDER BY tblAssetTracker.trackDate DESC) AS currentDate
FROM tblAssetManagement LEFT OUTER JOIN tblAssetTracker ON tblAssetManagement.assetID = tblAssetTracker.assetID
ORDER BY assetTag

However, if I want to filter the grid by adding the "WHERE tblAssetTracker.locationID = 1" it will display all the records that are at that location and assets currently at another. I hope I am making sense. Basically, I want to display all the assets by their current location selected by the user.

TIA,
Steve

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 10:48:28

Are you using SQL Server 2000 or 2005?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 10:49:50

[code]SELECT *
FROM tblAssetManagement am
INNER JOIN tblAssetTracker at
ON at.assetID=am.assetID
INNER JOIN tblLocations l
ON l.locationID=at.locationID
WHERE l.locationID=@locationID[/code]

rememeber to replace * by actual columns

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 10:54:28

[code]SELECT assetTag,
serialNumber,
currentLocation,
currentDate
FROM (
SELECT am.assetTag,
am.serialNumber,
ROW_NUMBER() OVER (PARTITION BY am.assetID ORDER BY at.trackDate DESC) AS RecID
lo.location AS currentLocation,
at.trackDate AS currentDate
FROM tblAssetManagement AS am
LEFT JOIN tblAssetTracker AS at ON at.assetID = am.assetID
AND at.locationID = 1
LEFT JOIN tblLocations AS lo ON lo.locationID = at.locationID
) AS d
WHERE RecID = 1
ORDER BY assetTag[/code]

E 12°55'05.63"
N 56°04'39.26"

SteevR
Starting Member

18 Posts

Posted - 2008-09-25 : 10:55:35

Peso: Sorry... its SQL Server 2000.

visakh16: I will give it try and get back to you.

Jeezus, quick responses. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 10:57:08

quote:

Originally posted by Peso

SELECT		assetTag,
		serialNumber,
		currentLocation,
		currentDate
FROM		(
			SELECT		am.assetTag,
					am.serialNumber,
					ROW_NUMBER() OVER (PARTITION BY am.assetID ORDER BY at.trackDate DESC) AS RecID
					lo.location AS currentLocation,
					at.trackDate AS currentDate
			FROM		tblAssetManagement AS am
			LEFT JOIN	tblAssetTracker AS at ON at.assetID = am.assetID
						AND at.locationID = 1
			LEFT JOIN	tblLocations AS lo ON lo.locationID = at.locationID
		) AS d
ORDER BY	assetTag

E 12°55'05.63"
N 56°04'39.26"

@Peso
what is RecID for?

SteevR
Starting Member

18 Posts

Posted - 2008-09-25 : 11:06:49

Hi visakh16,

This produced the assets to list several times if there were multiple entries at the same location. I would like the asset to list once but show its current location (newest trackDate).

@Peso: sorry this produces an error. Maybe not supported on SQL Server 2000?

Thanks,
Steve

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 11:16:13

Thanks. Fixed the prevous code with red.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 11:17:59

[code]SELECT am.assetTag,am.serialNumber,
l.location AS CurrentLocation,
at.trackDate AS CurrentDate
FROM tblAssetManagement am
INNER JOIN tblAssetTracker at
ON at.assetID=am.assetID
INNER JOIN (SELECT assetID,MAX(trackDate) AS LatestDate
FROM tblAssetTracker
GROUP BY assetID) at1
ON at1.assetID=at.assetID
AND at1.LatestDate=at.trackDate
INNER JOIN tblLocations l
ON l.locationID=at.locationID
WHERE l.locationID=@locationID[/code]

SteevR
Starting Member

18 Posts

Posted - 2008-09-25 : 11:27:16

Hi,

visakh16: marry me... That works perfectly. Thank you so much.

Peso: Sorry, I could not get yours to work. Error in list of function arguments: 'OVER' not recognized.

Thanks,
Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 11:31:56

quote:
Originally posted by SteevR

Hi,

visakh16: marry me... That works perfectly. Thank you so much.

Peso: Sorry, I could not get yours to work. Error in list of function arguments: 'OVER' not recognized.

Thanks,
Steve

You're welcome

ROW_NUMBER() function is avialble only from SQL 2005 onwards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources