Serg,
The actual life tables are similar to what I initially posted, with the exception that dim_lead doesn't actually contain latitude and longitude columns. I'm don't know why I included lat/long in the original sample ddl, because I handled for the association of leads to lat/long in a temporary query.
Dim_leads = all attributes of leads
Dim_geography = zip, city, state, latitude, longitude
Goal: return the leads and cities in which they dwell that are within given distance (report parameter #1) to given city center (report parameter#2) and state (report parameter #3).
Table definitions and data:
create table dim_lead
(
dim_lead_key int,
date_created datetime,
[contact_first_name] varchar(20),
[contact_last_name] varchar(20),
lead_id int,
[contact_city] varchar(20),
[contact_state] varchar(2),
[contact_zip] varchar(10),
sub_status varchar(5));
insert into dim_lead values
('2014-11-12 00:00:00.000', 'Leah', 'Loan', 1234, 'Boulder', 'CO', '80302', 'R'),
('2014-11-12 00:00:00.000', 'Mary', 'Morris', 4321, 'Boston', 'MA', '02133', 'R'),
('2014-11-12 00:00:00.000', 'Nancy', 'Nham', 3241, 'Colorado Springs', 'CO', '80949','R'),
('2014-11-12 00:00:00.000', 'Olga', 'Ofram', 2314, 'Denver', 'CO', '80201', 'R'),
('2014-11-12 00:00:00.000', 'Patty', 'Phillips', 3421, 'Greeley', 'CO', '80631', 'R'),
('2014-11-12 00:00:00.000', 'Queen', 'Quon', 2143, 'New York', 'NY', '10025', 'R'),
('2014-11-12 00:00:00.000', 'Rebecca', 'Rhode', 2431, 'Pueblo', 'CO', '81001', 'R');
CREATE TABLE [dbo].[dim_geography](
[dim_geography_key] [int] IDENTITY(1,1) NOT NULL,
[zip] [varchar](10) NOT NULL,
[city] [varchar](50) NULL,
[state] [varchar](2) NULL,
[country] [varchar](2) NULL,
[county] [varchar](40) NULL,
[latitude] [float] NULL,
[longitude] [float] NULL,
[rn] [bigint] NULL
) ON [PRIMARY]
INSERT [dbo].[dim_geography] ([dim_geography_key], [zip], [city], [state], [country], [county], [latitude], [longitude], [rn]) VALUES (47683, N'02133', N'Boston', N'MA', N'US', N'Suffolk', 42.358631, -71.056702, 1)
INSERT [dbo].[dim_geography] ([dim_geography_key], [zip], [city], [state], [country], [county], [latitude], [longitude], [rn]) VALUES (65413, N'80302', N'Boulder', N'CO', N'US', N'Boulder', 40.015739, -105.279243, 1)
INSERT [dbo].[dim_geography] ([dim_geography_key], [zip], [city], [state], [country], [county], [latitude], [longitude], [rn]) VALUES (254402, N'80949', N'Colorado Springs', N'CO', N'US', N'El Paso', 38.83345, -104.821808, 1)
INSERT [dbo].[dim_geography] ([dim_geography_key], [zip], [city], [state], [country], [county], [latitude], [longitude], [rn]) VALUES (111044, N'80201', N'Denver', N'CO', N'US', N'Denver', 39.740009, -104.992264, 1)
INSERT [dbo].[dim_geography] ([dim_geography_key], [zip], [city], [state], [country], [county], [latitude], [longitude], [rn]) VALUES (60117, N'80631', N'Greeley', N'CO', N'US', N'Weld', 40.423279, -104.694458, 1)
INSERT [dbo].[dim_geography] ([dim_geography_key], [zip], [city], [state], [country], [county], [latitude], [longitude], [rn]) VALUES (2851992, N'10025', N'New York', N'NY', N'US', N'New York', 40.71455, -74.007118, 1)
INSERT [dbo].[dim_geography] ([dim_geography_key], [zip], [city], [state], [country], [county], [latitude], [longitude], [rn]) VALUES (204835, N'81001', N'Pueblo', N'CO', N'US', N'Pueblo', 38.263859, -104.612373, 1)
SET IDENTITY_INSERT [dbo].[dim_geography] OFF
I am using eririkur's cross join query because it doesn't exclude leads having a city = NULL...but it takes 1 1/2 = 2 minutes given the volume of leads it must search.
query
;WITH LEAD_DATA AS
(
SELECT
DL.contact_first_name
,DL.contact_last_name
,DL.contact_city
,DL.contact_state
,DL.contact_zip
,DL.sub_status
,Geography::Point(COALESCE(DL.latitude,0),COALESCE(DL.longitude,0), 4326) AS GEO_POINT
FROM dbo.dim_lead DL
)
,GEO_DATA AS
(
SELECT
DG.city
,DG.state
,Geography::Point(COALESCE(DG.latitude,0),COALESCE(DG.longitude,0), 4326) AS GEO_POINT
FROM dbo.dim_geography DG
WHERE DG.city = 'Denver'
AND DG.state = 'CO'
)
,FINAL_SET AS
(
SELECT
LD.contact_first_name + CHAR(32) + LD.contact_last_name AS FULL_NAME
,LD.contact_city
,LD.contact_state
,LD.contact_zip
,LD.sub_status
,GD.city
,GD.state
--,(LD.GEO_POINT.STDistance(GD.GEO_POINT) * 0.001) AS DIST_METER
,(LD.GEO_POINT.STDistance(GD.GEO_POINT) * 0.0006213) AS DIST_MILE
FROM LEAD_DATA LD
CROSS JOIN GEO_DATA GD
)
SELECT
FS.FULL_NAME
,FS.contact_city
,FS.contact_state
,FS.contact_zip
,FS.sub_status
,FS.city
,FS.state
,FS.DIST_MILE
FROM FINAL_SET FS
WHERE FS.DIST_MILE < 250;