question

ridgebuilder avatar image
ridgebuilder asked

Compare multiple columns from multiple rows in single table

USE dev_db GO CREATE TABLE T1_VALS ( [SITE_ID] [int] NULL, [LATITUDE] [numeric](10, 6) NULL, [UNIQUE_ID] [int] NULL, [COLLECT_RANK] [int] NULL, [CREATED_RANK] [int] NULL, [UNIQUE_ID_RANK] [int] NULL, [UPDATE_FLAG] [int] NULL ) GO INSERT INTO T1_VALS (SITE_ID,LATITUDE,UNIQUE_ID,COLLECT_RANK,CREATED_RANK,UNIQUEID_RANK) VALUES (207442,40.900470,59664,1,1,1) (207442,40.900280,61320,1,1,2) (204314,40.245220,48685,1,2,2) (204314,40.245910,59977,1,1,1) (202416,39.449530,9295,1,1,2) (202416,39.449680,62264,1,1,1) I generated the COLLECT_RANK and CREATED_RANK fields from two date fields (not shown here) and the UNIQUEID_RANK field from the UNIQUE_ID which is used here. I used a SELECT OVER clause with Ranking Function to genterate these fields. A _RANK value of 1 means the latest date or greates UNIQUE_ID value. I thought my solution would be pretty straight forward using these rank values via array and cursor processing but I seem to have painted myself into a corner. MY problem: I need to choose LONGITUDE value and its UNIQUE_ID based upon the following business rules and set the update value, (1), for that record in its UPDATE_FLAG field: Select the record w/most recent Collection Date (i.e. RANK value = 1) for a given SITE_ID. If multiple records exist w/same Collection Date (i.e. same RANK value), select the record w/most recent Created Date (RANK value =1) for a given SITE_ID. If multiple records exist w/same Created Date, select the record w/highest Unique ID for a given SITE_ID (i.e. RANK value = 1). Your suggestions would be most appreciated.
sql-server-2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.