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).
View 9 Replies
ADVERTISEMENT
Sep 3, 2014
I have data:
Ticket User Priority
A ME 1
B ME 1
C ME 2
C ME 3
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
H YOU 3
I ME 1
Essentially if Ticket and User are the same I just want the min priority returned.
SO:
Ticket User Priority
A ME 1
B ME 1
C ME 2
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
I ME 1
I've tried partition and rank but can't get it to return the right output.
View 5 Replies
View Related
May 20, 2015
Assuming I have a table similar to the following:
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?
View 3 Replies
View Related
Oct 2, 2014
I have a MySql Database i need to extract data from based on matching info in a local SQL Server database,
I will ultimately need to cycle through 20 of these MySql databases , but the query below is taking 1 minute and 20 plus seconds...Just for one..
Is there a simple tweak that would speed it up?
USE [CAS]
GO
DECLARE @PhoneDB varchar(max),
@SQL NVARCHAR(MAX);
set @PhoneDB = '[PHONEDB_PHI]';
set @SQL = 'SELECT
[Code]....
View 4 Replies
View Related
Oct 22, 2014
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,
[code]....
View 9 Replies
View Related
May 8, 2008
Hi,
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.
I'd appreciate it if you could help me on this?
Thanks in advance.
View 19 Replies
View Related
Jul 10, 2014
I have a database of employees and pay rates.
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?
View 1 Replies
View Related
Oct 13, 2005
Hi,
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
COMMIT TRAN
GO
View 2 Replies
View Related
Apr 2, 2014
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.
9.72732e+009
View 2 Replies
View Related
Jun 9, 2014
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:
View 1 Replies
View Related
Jul 16, 2015
I would like to know how to split the phone number into two columns based on - symbol Dynamically.
for example
Phone Number
123-12323
1234-1222
so output should be
code number
123 12323
1234 1222
View 1 Replies
View Related
Aug 22, 2014
This is my code, table and result,
declare @t_JobNoticeID table (cvid int, JobNoticeID int)
insert into @t_JobNoticeID values(2456, 24);
insert into @t_JobNoticeID values(4000, 124);
insert into @t_JobNoticeID values(245, 9);
insert into @t_JobNoticeID values(2456, 19);
insert into @t_JobNoticeID values(4000, 904);
insert into @t_JobNoticeID values(4000, 11);
insert into @t_JobNoticeID values(24, 19);
[Code] ....
My question is -
How update statement looks like compare RESULT A1 with x_JobMatching?
So, my FINAL RESULT shown as follow,
CVIDJobNoticeIDisMatch
1925450
6590690
459130
2456191
4569110
40009041
24560
900240
24191
View 5 Replies
View Related
Mar 29, 2007
I'm getting this warning each time I auto-generate my model. I'm using named queries with logical primary keys.
The relation property of the role "xxx" refers to the target end of relation "xxx" which is not bound to a set of uniquely contrained columns.
I've searched and can't figured out what I am being warned about and whether I need to fix something. Can you help?
Thanks,
Toni
View 1 Replies
View Related
Apr 28, 2015
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
Table 2
Pay Month Pay YearPay Amount
5 2014 300
6 2014 1000
10 2014 500
11 2014 2000
12 2014 300
1 2015 900
create table tbl1
(
saleNo int
,saleDate date
,saleAmt float
)
insert into tbl1 (saleNo, saleDate, saleAmt)
[Code] ....
View 5 Replies
View Related
May 6, 2006
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.
View 7 Replies
View Related
May 20, 2014
We have 2 tables (table a and b)
select a.* from table a inner join table b
on a.col1<> b.col2
I would like to have column names where the values are not matching.
View 4 Replies
View Related
Apr 9, 2015
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.
create table #vehicle(id int, vehiclemake varchar(10), vehiclemodel varchar(10), classtype varchar(1))
create table #class(id int, classtype varchar(1), value int)
insert into #vehicle values(1, 'AUDI', 'R8', 'A')
insert into #vehicle values(2, 'AUDI', null, 'B')
insert into #class values(1, 'A', 100)
insert into #class values(2, 'B', 1)
[Code] ....
Using the above example, if VehicleModel is anything other than 'R8' is specified then I want it to return the other class type record.
This is going to be used as a join within a bigger statement, so I'm not sure ordering and returning top 1 is going to work.
View 9 Replies
View Related
Jul 16, 2015
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 (''),
[code]....
View 2 Replies
View Related
Apr 2, 2015
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.
View 1 Replies
View Related
Apr 11, 2006
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
View 7 Replies
View Related
Aug 10, 2015
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:
1)
Table1.Firstname=Table2.Firstname,Table1.Lastname=Table2.Lastname,Table1.StartDate=Table2.StartDate,Table1.EndDate=
Table2.EndDate
2)
Table1.Firstname=Table2.Firstname,Table1.Lastname=Table2.Lastname,Table1.StartDate=Table2.StartDate,Table1.EndDate!=
Table2.EndDate
3)
Table1.Firstname!=Table2.Firstname,Table1.Lastname!=Table2.Lastname,Table1.StartDate!=Table2.StartDate,
Table1.EndDate=Table2.EndDate
Table 1 & Table 2 contains the following data:
Table1
First Name
Last Name
Start Date
End Date
City
[code]...
I want this output by writing a single query.
View 4 Replies
View Related
Mar 10, 2014
I have a temperature table with a column that shows the how many hours old a newborn was when his/her temperature was taken.
Example lets say once per hour.
I want to join to a table called Weight that records the newborns weight at any given time.
Example lets say 3x during the day.
1 @ 8:45am
2 @ 11:15am
3 @ 4:30pm
I want to figure out which weight recording is the closest to a given temperature recording and return that one row.
View 2 Replies
View Related
Sep 13, 2014
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,
View 1 Replies
View Related
Apr 8, 2008
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')
But when I run it, it says "0 rows affected".
View 5 Replies
View Related
Oct 29, 2007
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?
View 6 Replies
View Related
Oct 19, 2007
Hello All,
I have one table that contains information about invoices.
However I need to find out if invoice numbers have been used more than once.
I've tried to join the table to itself and find matching numbers, however I can't seem to get it to work properly.
Any help will be appreciated.
Thanks,
View 7 Replies
View Related
Sep 15, 2015
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)
View 3 Replies
View Related
Mar 30, 2015
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
[Code]....
View 2 Replies
View Related
Apr 10, 2014
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 ?
View 9 Replies
View Related
Apr 2, 2015
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...
View 9 Replies
View Related
Apr 15, 2008
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
View 18 Replies
View Related
Jul 19, 2004
Hi
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.
Hope you can help.
Thanks
View 2 Replies
View Related
Dec 11, 1999
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.
View 1 Replies
View Related
