T-SQL (SS2K8) :: Find Matching Phone Of Person Based On Relation Type - Duplicates
Aug 11, 2014
I have a patient record and emergency contact information. I need to find duplicate phone numbers in emergency contact table based on relationship type (RelationType0 between emergency contact and patient. For example, if patient was a child and has mother listed twice with same number, I need to filter these records. The case would be true if there was a father listed, in any cases there should be one father or one mother listed for patient regardless. The link between patient and emergency contact is person_gu. If two siblings linked to same person_gu, there should be still one emergency contact listed.
Below is the schema structure:
Person_Info: PersonID, Person Info contains everyone (patient, vistor, Emergecy contact) First and last names
Patient_Info: PatientID, table contains patient ID and other information
Patient_PersonRelation: Person_ID, patientID, RelationType
Address: Contains address of all person and patient (key PersonID)
Phone: Contains phone # of everyone (key is personID)
The goal to find matching phone for same person based on relationship type (If siblings, then only list one record for parent because the matching phones are not duplicates).
Auto_ID Account_ID Account_Name Account_Contact Priority 1 3453463 Tire Co Doug 1 2 4363763 Computers Inc Sam 1 3 7857433 Safety First Heather 1 4 2326743 Car Dept Clark 1 5 2342567 Sales Force Amy 1 6 4363763 Computers Inc Jamie 2 7 2326743 Car Dept Jenn 2
I'm trying to delete all duplicate Account_IDs, but only for the highest priority (in this case it would be the lowest number).
I know the following would delete duplicate Account_IDs:
DELETE FROM staging_account WHERE auto_id NOT IN (SELECT MAX(auto_id) FROM staging_account GROUP BY account_id)
The problem is this doesn't take into account the priority; in the above example I would want to keep auto_ids 2 and 4 because they have a higher priority (1) than auto_ids 6 and 7 (priority 2).
How can I take priority into account and still remove duplicates in this scenario?
I have a table with 22 million Business records. I can see that there are duplicates when I group by BusinessName and Address and Phone. I'd like to place only the duplicates into a table, with a ranking, oldest business key gets a ranking of 1.
As a bonus I'd like each group to have a distinct group name (although not necessary, just want to know how to do this)
Later after I run more verifications to make sure these are not referenced elsewhere I'll delete everything with a matchRank > 1 out of the main Business table.
DROP TABLE [dbo].[TestBusiness]; GO CREATE TABLE [dbo].[TestBusiness]( [Business_pk] INT IDENTITY(1,1) NOT NULL, [BusinessName] VARCHAR (200) NOT NULL, [Address] VARCHAR(MAX) NOT NULL,
I'm trying to create a case function for home phone ,work phone and cell phone. The thing is some of the home phone numbers either null, zero or less than 10 digits then i'd like to get either cell phone or work phone if they are not null, zero or less than 10 digits.
One employee has two pay rates for two different jobs:
Job A: Rate $10.00 Job B: Rate $15.00
I will be updating their record so that they only have one job going forward, Job C. I need Job C to equal their HIGHER of the two existing jobs.
I have a select statement to find what the higher rate is. However, I am not sure how I can apply the rate to be the new job's rate. Here's what I used to find the highest rate for one single person:
SELECT max(rate), employeeID FROM JobsTable inner join IDTable on JobsID2 = IDID2
WHERE JobCode in ('JOBA','JOBB') and EmployeeID = '12345' GROUP BY EmployeeID
(this returns the employee ID from one table, and the highest rate from Jobs A and B from another table)
I can get it to update to add JobC -- how can I get it to assign the result from the above query to be the rate used for Job C?
I'm having problems with a stored procedure, that i'm hoping someone can help me with.
I have a table with 2 columns - Username (varchar), LastAllocation (datetime)
The Username column will always have values, LastAllocation may have NULL values. Example
Username | LastAllocation ------------------------ Greg | 02 October 2005 15:30 John | 02 October 2005 18:00 Mike | <NULL>
My stored procedure needs to pull back a user name with the following criteria:
If any <NULL> dates send username of first person where date is null, sorted alphabetically, otherwise send username of person with earliest date from LastAllocation
Then update the LastAllocation column with GETDate() for that username.
This SP will be called repeatedly, so all users will eventually have a date, then will be cycled through from earliest date. I wrote an SP to do this, but it seems to be killing my server - the sp works, but I then can't view the values in the table in Enterprise Manager. SP is below - can anyone see what could be causing the problem, or have a better soln? Thanks Greg ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ CREATE PROCEDURE STP_GetNextSalesPerson AS DECLARE @NextSalesPerson varchar(100)
BEGIN TRAN
IF (SELECT COUNT(*) FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL) > 0 BEGIN SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL ORDER BY eUserName ASC END ELSE BEGIN SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam ORDER BY LeadLastAllocated ASC END
SELECT @NextSalesPerson UPDATE REF_SalesTeam SET LeadLastAllocated = GETDATE() WHERE eUserName = @NextSalesPerson
I seem to always get the "Fun Stuff" to try and figure out. I have an entire table that was pumped out of Oracle. I even hate saying that word!
There are a couple columns that are Float data type, and they are storing phone numbers as a Float data type. I am not able to CAST these into anything that is legible.
This is one of the values that I made up that look like some of the others.
I have 2 tables in a 1: n relation. How can i get a select statement that the field in the n-relation with outputs, separated by a semicolon; Example: One person have many Job Titles
Table1 (tblPerson) Table2 (tblTitles) 1, "John", "Miller", "Employee; Admin; Consultant" 2, "Joan", "Stevens", "Employee, Software Engineer, Consultant" and so on .... 1 in select statement:
I have two tables - one with sales and another with payments against those. The payment may not match the exact amount of sales and I have to use FIFO method to apply payments. The payment month must >= sales month.
How can i write a query to do this? Examples are as below.
Table 1 Sales Sale DateSale Amt 1Jun-141200 2Oct-142400 3Dec-14600 4Feb-1512000
Users have to answer 17 simple yes/no questions and the answers are stored in an column for each question as tinyint 0/1 values.
At least that's what seems reasonable to me at the moment.
The table is under my control so I could change it if needed.
Now from several tenthousend or maybe hundreds of thousends of entries I need to find those with the closest match. Of course, I need all of the entries that have the exact same answers and this is no problem. But - at least if there are not enough full matches - then I need all records that have maybe 16,15,14... matches out of the 17 answers.
I have not yet the idea on how to handle this without quering 17*16 different answer schemes.
I'm working on a join between two tables where I only want one row returned... I'm matching on two columns between two tables. One of those columns in the target table could be null. I only want one record returned.
I've been matching some incoming contacts to existing contacts in a database and need to update, insert, or rematch based on the ifs below.
If name, phone, and email all provided for both, then use the highest Source. So if the Contact has a Source value of 5 and SourceContact has a ContactSource of 1, update Contact with SourceContact
If name, phone and email all provided, and source the same then update to new values.
If name and phone on SourceContact and name and Email on Contact then reset Contact_fk to -1 should not have matched, but should be an insert
If name and email on SourceContact and name and phone on Contact then reset Contact_fk to -1 should not have matched, but should be an insert
If name and phone on SourceContact and name and/or Phone is blank in Contact then update
If name and email on SourceContact and name and/or email is blank in Contact then update
If phone numbers can be different, just update record to SourceContact if it has a same or higher ContactSource
If email address do not match then set SourceContacts to -1 Contact_fk it was computed incorrectly. Both have a non blank email
If Contact_fk is 0 then it is a new contact and just insert it.
IF OBJECT_ID('dbo.SourceContact', 'U') IS NOT NULL DROP TABLE [dbo].[SourceContact]; CREATE TABLE [dbo].[SourceContact] ( [SourceContact_pk]INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_SourceContact PRIMARY KEY CLUSTERED, [ContactLastName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactLastName DEFAULT (''), [ContactFirstName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactFirstName DEFAULT (''),
I have tables in my database, tblNames1, tblNames2, tblNames3 for ex, and for addresses tblAddresses, can I match the address IDs in the names tables to the address table based on the values of a field? In this case CompanyName. Can they be matched automatically? My issue is that I have a huge CSV file with all the address information that needs to be assigned a foreign key based on the company name field which is also present in all the names tables, all the names are normalized.
Given an ID (column B), I need to find which IDs have identical data.That is, given '200', I want the desired result to be:100The idea is that the system sees that id=200 has 5 records with theindicated data in cols C and D.It should then find any other ids with the exact same data for thosecolumns.Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,40:5) so they match. 300 and 400 should NOT be returned.Any bright ideas out there? Thanks!DECLARE @a TABLE(A int, B int, C int, D int)DECLARE @b TABLE(A int, B int, C int, D int)INSERT INTO @a (A, B, C, D) VALUES (1, 100, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (2, 100, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (3, 100, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (4, 100, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (5, 100, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (6, 200, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (7, 200, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (8, 200, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (9, 200, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (10, 200, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (11, 300, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (12, 300, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (13, 300, 40, 3)INSERT INTO @a (A, B, C, D) VALUES (14, 400, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (15, 400, 40, 5)SELECT * FROM @a
I have two tables in my database. Some matching rules are associated with these tables and I want a query which will retrieve those data based on the matching rules provided below:
I have the piece of sql code here below that keeps giving out duplicates. How to resolve this.
isnull((select distinct (SUM(a1.ActualDebit) - SUM(a1.ActualCredit) ) from #MainAccount a1 LEFT OUTER JOIN #BudgetAccount bb ON aa.AccountID = bb.AccountID AND a1.PeriodStartdate = bb.PeriodStartDate and a1.DateMonth=bb.DateMonth and a1.Budget = bb.Budget WHERE a1.AccountID = aa.AccountID and a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE GROUP BY a1.group1, a1.Group2),0) As Actual_CurrentMonth,
I have 4 rows below in file tblTEST, and I want to be able to transfer the CODE from the MAIN location to the INT location (replacing all existing "A" codes), preceeded by an "I".
ID LOC CODE -- ----- ------ 11 MAIN B 11 INT A 22 MAIN C 22 INT A
I want the result to be:
ID LOC CODE -- ----- ------ 11 MAIN B 11 INT IB 22 MAIN C 22 INT IC
I am stumped as to how to do this - any help or advice would be appreciated.
The only thing I've come up with is:
UPDATE S SET s.code = B.code FROM tbltest B LEFT OUTER JOIN tbltest S ON B.id = S.id WHERE (S.loc = 'INT')
I have a table called MessageBoard. It has a column called Messages. A user can type text including any html tags through a text area ans when he saves it by clicking a button, the content typed by the user is saved in the MessageBoard Table (in the Messages) column. So once saved, the html tags are kept intact. If I have to find and replace certain html tags, what kind of SQL Query I have to write? For example I want to find all the <pre> </pre> tags and replace it with <p> </p> tags. How do I do this?
We have an application where we want to check to see if the vehicle part on a job matches to our internal parts inventory (PartsInventory table) before we order it. The problem is that sometimes the part number matches exactly and sometimes the part number has '-' or space but if those are removed, will match to our internal part number. Below is what I have so far but it only matches exact part numbers. One example would be if our part number was 1013738-00-C but the job (in RepairOrderLines) had a part number of 101373800C we should consider it a match. Both PartNumbers are varchar(30).
SELECT dbo.PartsInventory.PartNumber, dbo.PartsInventory.PartDescription, dbo.PartsInventory.VehicleMake FROM dbo.PartsInventory INNER JOIN dbo.RepairOrderLines ON dbo.PartsInventory.PartNumber = dbo.RepairOrderLines.PartNumber INNER JOIN dbo.RepairOrder ON dbo.RepairOrderLines.RecordID = dbo.RepairOrder.RecordID INNER JOIN dbo.Vehicles ON dbo.RepairOrder.VehicleID = dbo.Vehicles.VehicleID AND dbo.PartsInventory.VehicleMake = dbo.Vehicles.VehicleMake WHERE (dbo.RepairOrderLines.RecordID = 46001)
In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?
if OBJECT_ID('tempdb..#left_table') is not null drop table #left_table; if OBJECT_ID('tempdb..#right_table') is not null drop table #right_table; create table #left_table
WITH cte_OrderProjectType AS ( select Orderid, min(TypeID) , min(CTType) , MIN(Area) from tableA A inner join tableB B ON A.PID = B.PID left join tableC C ON C.TypeID = B.TypeID LEFT JOIN tableD D ON D.AreaID = B.ID group by A.orderid )
This query uses min to eliminate duplicates. It takes 1.30 seconds to complete..
Is there any way I can improve the query performance ?
I have tables in my database, tblNames1, tblNames2, tblNames3, and a main addresses table (currently empty). Once I've imported the address data I need to match the addressIDs in the names tables to the Primary ID in the address table based on the values of a field CompanyName (which is common to all the tables) My issue is that I have a huge CSV file with the master address information but obviously SQL server needs to assign foreign keys so the names tables can linked to corresponding rows in address table. It's a a many to 1 relationship as their will be one address with multiple name entries. All the names are normalized so everything can be matched up...
Hi, I have table which stores the fund name and its data. We get quarterly information from the fund co. Suppose if the user wants to add a fund thats not in our database we let then add a ClientFundId and a FundName. But may be after sometime the fund company may add that fund in the next quarter.. So how do i get rid of Duplicated Data.. In the ClientFundId column we can a 9 letter Aplhanumeric or a 5 letter character but if the fund co.. provides those values the 5 letter characters are stored in Ticker column and the 9 letter words are stored in Cusip column.. So i just wrote this query hoping i could retrieve the duplicate values but it didnt list any..but i found one this is my query.. Select FundId, Cusip, Ticker, ClientFundId, FundName, ShortName From Fund Where
ClientFundId = Ticker or ClientFundId = Cusip Any help will appreciated Thanks Karen
I am trying to find when a name has been entered more than once into 1 database table.
I'm currently doing something like this (can't remember exactly, not at work)
SELECT COUNT(*) AS Cnt, Name FROM tblTable GROUP BY Name ORDER BY Cnt Desc
This brings back all the Names in the database and tells me which are duplicates but I want to just have the results of the duplicate values and not the single values.
does someone have a querry to display the duplicate records in a table.
Table: zipcode dma
My data upload is failing because there is a primary key on zipcode and the source data (42k records) has about 50 duplicate zipcode records in it. It is possible that there is a unique combo of zipcode / dma but I need to identify the duplicate records to determine that.
