September 4, 2013 at 1:54 pm
I have a query that "Mickey" on here helped me with that I made some changes and it works wonderfully for what we need. I want to create a associated Zip Code for my cpt codes just as I have given them a Geography lat/long. My brain must be drained or I just cant think straight because I cant figure it out and I bet its super simple:crazy:
The Geography query below works like a charm. I would like to duplicate it however with the following modification.
CREATE TABLE #TempLocation
(Code Varchar(5) PRIMARY KEY
,Location Varchar)
INSERT INTO #TempLocation
VAlUES ('OR001', ------this is where I'm loosing it, I want to assign this a Zip Code like I have below with Geography points)
---The one below this comment works very well
CREATE TABLE #TempLocation
(Code Varchar(5) PRIMARY KEY
,Location Geography)
INSERT INTO #TempLocation
VALUES ('OR001',Geography::Point(35.136195,-106.658707, 4326))
INSERT INTO #TempLocation
VALUES ('OR002',Geography::Point(35.083956,-106.688232, 4326))
INSERT INTO #TempLocation
VALUES ('OR003',Geography::Point(35.105305,-106.5193182, 4326))
INSERT INTO #TempLocation
VALUES ('CS001',Geography::Point(35.132264,-106.712952, 4326))
INSERT INTO #TempLocation
VALUES ('CS002',Geography::Point(35.132260,-106.712951, 4326))
INSERT INTO #TempLocation
VALUES ('CS003',Geography::Point(35.132262,-106.712951, 4326))
SELECT tl.Location
,[Visit: Visit Type]
,[Visit: Visit ID]
,[Procedure: Code]
,[Date: Service date of the Charge]
,[Insurance: Charge Primary Ins. Plan]
,[Patient: Age]
,[Patient: First Name]
,[Patient: Last Name]
,[Patient: Patient ID]
,[Patient: Patient Name]
,[Patient: Preferred Provider]
,[Patient: Sex]
,[Patient: Zip Code]
,[Patient: Date Of Birth]
,[Patient: Address Line 1]
,[Patient: Middle Initial]
,[Patient: Phone Primary]
,[Patient: Race]
,[Patient: SSN]
,[Patient: State]
,[Procedure: Description]
,[Provider: Billable]
,[Provider: Rendering]
FROM [PrimeRecord130010].[dbo].[vCHCCharges] v
JOIN #TempLocation tl ON v.[Procedure: Code] = tl.Code
--Where [Date: Service date of the Charge] between '01/01/13' and '06/01/13'
GO
DROP TAble #TempLocation
***SQL born on date Spring 2013:-)
September 4, 2013 at 2:41 pm
Never mind it works! was able to import into Map wizard with accurate counts.
CREATE TABLE #TempLocation
(Code Varchar(5) PRIMARY KEY
,Location Varchar(10))
INSERT INTO #TempLocation
VAlUES ('OR001', '87120')
------------------------------------
87120ArtStreet241542OR0012013-06-11 00:00:00.000No Insurance
87120ArtStreet243318OR0012013-07-02 00:00:00.000No Insurance
87120ArtStreet243321OR0012013-07-02 00:00:00.000No Insurance
87120ArtStreet243322OR0012013-07-02 00:00:00.000No Insurance
87120ArtStreet243324OR0012013-07-02 00:00:00.000No Insurance
87120ArtStreet243325OR0012013-07-02 00:00:00.000No Insurance
87120ArtStreet246853OR0012013-08-13 00:00:00.000No Primary Insurance Plan
87120ArtStreet246854OR0012013-08-13 00:00:00.000No Primary Insurance Plan
87120ArtStreet246855OR0012013-08-13 00:00:00.000No Primary Insurance Plan
87120ArtStreet246856OR0012013-08-13 00:00:00.000No Primary Insurance Plan
***SQL born on date Spring 2013:-)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply