Hi all,
I am seeking your expertise to create SQL codes (SQL server 2005) that can help me to answer the problem below.
I have two tables (points and station), presented in form of SQL codes below. I’d like to find the 6 closest panels for each of the station. As can be seen in the result table below, the 6 closest panel names are arranged from the first closest (P1) to the sixth closest (P6). Similar procedure also applies for the distance column arrangement. This distance column (D1 – D6) is the distance of panels P1 – P6 to the station. The distance between two points (with x-y coordinates) can be calculated using a simple Cartesian formula:
Distance = ( (X1 – X2)2 + (Y1 - Y2)2 ) 0.5 . As the sample, distance between station ‘A’ and panel ‘P19-04W’ is = ((737606.383 - 737599.964)2 + (9548850.844 - 9548856.856)2) 0.5 = 8.79.
The expected result of the work is presented in the table below:
Table 1:
create table 1 (
Panels varchar(20),
X_Coord float,
Y_Coord float
)
go
set nocount on
insert into 1 values('P19-03E','737640.722','9548882.875')
insert into 1 values('P19-04E','737630.166','9548868.3')
insert into 1 values('P19-05E','737619.611','9548853.726')
insert into 1 values('P19-06E','737609.054','9548839.15')
insert into 1 values('P19-07E','737598.495','9548824.571')
insert into 1 values('P19-08E','737587.941','9548809.998')
insert into 1 values('P19-09E','737577.386','9548795.425')
insert into 1 values('P19-10E','737563.359','9548776.163')
insert into 1 values('P19-11E','737552.795','9548761.578')
insert into 1 values('P19-12E','737542.256','9548746.919')
insert into 1 values('P19-13E','737531.701','9548732.345')
insert into 1 values('P19-14E','737521.146','9548717.772')
insert into 1 values('P19-03W','737610.519','9548871.43')
insert into 1 values('P19-04W','737599.964','9548856.856')
insert into 1 values('P19-05W','737589.404','9548842.275')
insert into 1 values('P19-06W','737578.849','9548827.702')
insert into 1 values('P19-07W','737568.294','9548813.128')
insert into 1 values('P19-08W','737554.274','9548793.77')
insert into 1 values('P19-09W','737543.718','9548779.195')
insert into 1 values('P19-10W','737533.157','9548764.614')
insert into 1 values('P19-11W','737522.603','9548750.041')
set nocount off
go
Table 2:
create table 2 (
Station varchar(20),
X_Coord float,
Y_Coord float
)
go
set nocount on
insert into 2 values('A','737606.383','9548850.844')
insert into 2 values('B','737575.41','9548806.838')
insert into 2 values('C','737544.437','9548762.832')
set nocount off
go
Thanks alot in advance!
Thanks for the DDL and sample data. Try:
Code Snippet
use tempdb
go
create table dbo.t1 (
Panels varchar(20),
X_Coord float,
Y_Coord float
)
go
set nocount on
insert into dbo.t1 values('P19-03E','737640.722','9548882.875')
insert into dbo.t1 values('P19-04E','737630.166','9548868.3')
insert into dbo.t1 values('P19-05E','737619.611','9548853.726')
insert into dbo.t1 values('P19-06E','737609.054','9548839.15')
insert into dbo.t1 values('P19-07E','737598.495','9548824.571')
insert into dbo.t1 values('P19-08E','737587.941','9548809.998')
insert into dbo.t1 values('P19-09E','737577.386','9548795.425')
insert into dbo.t1 values('P19-10E','737563.359','9548776.163')
insert into dbo.t1 values('P19-11E','737552.795','9548761.578')
insert into dbo.t1 values('P19-12E','737542.256','9548746.919')
insert into dbo.t1 values('P19-13E','737531.701','9548732.345')
insert into dbo.t1 values('P19-14E','737521.146','9548717.772')
insert into dbo.t1 values('P19-03W','737610.519','9548871.43')
insert into dbo.t1 values('P19-04W','737599.964','9548856.856')
insert into dbo.t1 values('P19-05W','737589.404','9548842.275')
insert into dbo.t1 values('P19-06W','737578.849','9548827.702')
insert into dbo.t1 values('P19-07W','737568.294','9548813.128')
insert into dbo.t1 values('P19-08W','737554.274','9548793.77')
insert into dbo.t1 values('P19-09W','737543.718','9548779.195')
insert into dbo.t1 values('P19-10W','737533.157','9548764.614')
insert into dbo.t1 values('P19-11W','737522.603','9548750.041')
set nocount off
go
create table dbo.t2 (
Station varchar(20),
X_Coord float,
Y_Coord float
)
go
set nocount on
insert into dbo.t2 values('A','737606.383','9548850.844')
insert into dbo.t2 values('B','737575.41','9548806.838')
insert into dbo.t2 values('C','737544.437','9548762.832')
set nocount off
go
;with dist
as
(
select
b.Station,
a.Panels,
cast(sqrt(square(b.X_Coord - a.X_Coord) + square(b.Y_Coord - a.Y_Coord)) as numeric(8, 2)) as distance,
row_number() over(partition by b.Station order by cast(sqrt(square(b.X_Coord - a.X_Coord) + square(b.Y_Coord - a.Y_Coord)) as numeric(8, 2)), a.Panels) as rn
from
dbo.t1 as a
cross join
dbo.t2 as b
),
pvt_dist
as
(
select
Station,
[1] as d1,
[2] as d2,
[3] as d3,
[4] as d4,
[5] as d5,
[6] as d6
from
(select Station, rn, distance from dist where rn < 7) as d
pivot
(
min(distance)
for rn in ([1], [2], [3], [4], [5], [6])
) as pvt
),
pvt_panels
as
(
select
Station,
[1] as p1,
[2] as p2,
[3] as p3,
[4] as p4,
[5] as p5,
[6] as p6
from
(select Station, rn, Panels from dist where rn < 7) as d
pivot
(
min(Panels)
for rn in ([1], [2], [3], [4], [5], [6])
) as pvt
)
select
coalesce(d.Station, p.Station) as Station,
p.p1,
p.p2,
p.p3,
p.p4,
p.p5,
p.p6,
d.d1,
d.d2,
d.d3,
d.d4,
d.d5,
d.d6
from
pvt_dist as d
full join
pvt_panels as p
on d.Station = p.Station
order by
coalesce(d.Station, p.Station)
go
drop table dbo.t1, dbo.t2
go
AMB|||Hi Hunchback,
Greatly appreciate your help...!
The query above works perfectly when the creation of both tables is part of the long SQL code. But, if I delete the table creation codes (do not need to create the tables since they are already available in database) and just take the last part of your SQL code in a new query page for running; I got an error. The result is : an error mentioning incorrect syntax near 'pivot'. It also suggests me to set the compatibility level of the database. I'm confused.....
Can you please suggest me the right procedure so that I can take and run only the half part of the SQL Querry (without the creating table’s part)? Table t1 and t2 are already available in database. Thanks alot!
The partial query that I use for running:
;with dist
as
(
select
b.Station,
a.Panels,
cast(sqrt(square(b.X_Coord - a.X_Coord) + square(b.Y_Coord - a.Y_Coord)) as numeric(8, 2)) as distance,
.... and so on...
Others are also welcome to give your thought. Thanks
|||
Can you tell us which version of SQL Server are you using and the compatibility level of your db, please?
AMB
|||Hi Hunchback,
I am using SQL 2005, but am not really sure about the compatibillity level of my SQL server. How can I checked it?
|||to check the compatibility level of the database, you can use:
use master
go
select compatibility_level
from sys.databases
where [name] = 'db_name'
go
90 - SS 2005
AMB
|||Hi Hunchback,
Thanks for the suggestion. Here is the database name and compatability level:
DOZ_Convg_Modified, 80
I look forward to having your further assistance. Thanks
|||You can run this to set your database to sql2k5 compatibility.
Code Snippet
exec sp_dbcmptlevel 'DOZ_Convg_Modified',90
No comments:
Post a Comment