August 8, 2011 at 8:43 am
I've got a relational database structure where "assets" are defined in one table and their latitude-longitude information in a location table and their association is made by a cross reference table.
Once an asset has been defined, frequent positions for this asset are created in a locations table and a cross-reference record is created to associate each location with an asset.
I need to create a stored procedure for our developers which returns a list of assets to them. Each record about an asset should contain a latitude-longitude pair reflecting the most recently created location record for that asset.
Here are the table definitions and some initial data loads for them to demonstrate.
-- create asset table
CREATE TABLE [dbo].[assets](
[asset_id] [int] IDENTITY(1,1) NOT NULL,
[asset_name] [nvarchar](100) NOT NULL,
[status_id] [int] NOT NULL,
[asset_type_id] [int] NOT NULL,
[deleted_flag] [int] NULL,
[created_dt] [datetime] NULL
CONSTRAINT [PK_assets] PRIMARY KEY CLUSTERED
(
[asset_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT dbo.assets ON
INSERT INTO dbo.assets
( asset_id,
asset_name ,
status_id ,
asset_type_id ,
deleted_flag ,
created_dt
)
VALUES ( 1,
N'Asset_1' , -- asset_name - nvarchar(100)
1 , -- status_id - int
5 , -- asset_type_id - int
0 , -- deleted_flag - int
'2011-08-08 12:00:00' -- created_dt - datetime
)
SET IDENTITY_INSERT dbo.assets OFF
Next one:
-- create the locations table
CREATE TABLE [dbo].[locations](
[location_id] [int] IDENTITY(1,1) NOT NULL,
[device_serial] [varchar](50) NULL,
[latitude] [decimal](18, 10) NULL,
[longitude] [decimal](18, 10) NULL,
[speed] [decimal](8, 2) NOT NULL,
[altitude] [decimal](18, 10) NULL,
[created_dt] [datetime] NOT NULL,
[deleted_flag] [int] NULL
CONSTRAINT [PK_locations] PRIMARY KEY CLUSTERED
(
[location_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT dbo.locations ON
INSERT INTO dbo.locations
( location_id,
device_serial ,
latitude ,
longitude ,
speed ,
altitude ,
created_dt ,
deleted_flag
)
SELECT 1, '' , 29.3149584000 , -95.4476928711 , 0.0 , 0.0 , '2011-08-08 13:00:00' , 0
UNION
SELECT 2, '' , 29.3149584010 , -95.4476928711 , 0.0 , 0.0 , '2011-08-08 13:00:10' , 0
UNION
SELECT 3, '' , 29.3149584020 , -95.4476928711 , 0.0 , 0.0 , '2011-08-08 13:00:20' , 0
SET IDENTITY_INSERT dbo.locations OFF
And the cross-reference table:
-- create the asset_locations_xref table
CREATE TABLE [dbo].[asset_location_xref](
[asset_location_id] [int] IDENTITY(1,1) NOT NULL,
[asset_id] [int] NOT NULL,
[location_id] [int] NOT NULL,
[created_dt] [datetime] NULL,
[status_id] [int] NULL,
[deleted_flag] [int] NOT NULL
) ON [PRIMARY]
INSERT INTO dbo.asset_location_xref
( asset_id ,
location_id ,
created_dt ,
status_id ,
deleted_flag
)
SELECT1 , 1 , '2011-08-08 13:00:00' , 1 , 0
UNION
SELECT1 , 2 , '2011-08-08 13:00:00' , 1 , 0
UNION
SELECT1 , 3 , '2011-08-08 13:00:00' , 1 , 0
Here is the basics of what I'm trying to accomplish with a "get" stored procedure. The problem with this is that it returns 3 records - one for each associated location record when all I really want is the contents of location table's 3rd record.
ALTER PROCEDURE usp_Get_Assets
AS
BEGIN
SELECT
a.asset_id,
a.asset_name,
loc.latitude,
loc.longitude,
loc.speed,
loc.altitude
FROM dbo.asset_location_xref AS x
INNER JOIN dbo.assets AS a
ON x.asset_id = a.asset_id
INNER JOIN dbo.locations AS loc
ON x.location_id = loc.location_id
END
I've tried replacing the field definitions in the SELECT with a sub-SELECT like this:
( SELECT TOP 1
latitude AS latitude
FROM dbo.locations
INNER JOIN dbo.asset_location_xref AS xref
ON dbo.locations.location_id = xref.location_id
INNER JOIN dbo.assets AS ***
ON ***.asset_id = xref.asset_id
WHERE xref.asset_id = ***.asset_id
ORDER BY dbo.locations.created_dt DESC ) as Latitude
However it seems like a pretty "brute-force" way to obtain this data and it seems like it results in separate queries for each of the field elements I need: latitude, longitude, speed and altitude.
I'm wondering if it would be more efficient to retrieve a full location table entry into temp table and then select each of the elements I need out of the temp table.
Any thoughts or ideas about how to best solve this would be greatly appreciated.
Thanks!
Larry
August 8, 2011 at 10:47 am
Does this help?
WITH cte
AS (SELECT asset_id,
MAX(asset_location_id) AS max_assloc_no
FROM asset_location_xref
GROUP BY asset_id)
SELECT a.asset_id,
a.asset_name,
loc.latitude,
loc.longitude,
loc.speed,
loc.altitude
FROM assets AS a
INNER JOIN cte
ON a.asset_id = cte.asset_id
INNER JOIN locations AS loc
ON cte.max_assloc_no = loc.location_id
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 8, 2011 at 8:25 pm
You could also cross apply a subquery to get the TOP (1) row sorted by date descending, or use Row_Number() ordered by date descending where row_number() = 1. These techniques perform differently at different volumes as demonstrated here[/url].
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply