Merge And Sum Similar Records

Nov 20, 2012

I have the below in a table - TABLEA

Ref, Date, TIME, Code, Minutes
01117,2012-01-02, 541,BASIC,240.0
01117,2012-01-02, 541,BASIC,105.0

And I am trying to insert this into another table TABLEB but it wont allow as I am getting a duplicates error because of the unique Indexing on the table.

Ref,Date,Time,Code

Ideally if I could run a query on TABLEA so that it would merge and sum the minutes where REF,Date,TIME,CODE are the same.

i.e., the above would become

Ref , Date , TIME, Code, Minutes
01117,2012-01-02, 541,BASIC,345.0

Is this possible?

Another option that would work for me is the TIME column info isnt required to remain at 541.

If there was a count increment on the rows it would allow the import to rum.

ie if the above became

Ref, Date, TIME, Code, Minutes
01117,2012-01-02, 1,BASIC,240.0
01117,2012-01-02 ,2BASIC,105.0

It would also import correctly.

The first option is which I would prefer.

View 2 Replies

SQL Server 2014 :: Merge Similar Records From Excel?

May 19, 2015

I am working on a project that will require me to get a flat data file (excel spreadsheet) with hundreds of thousands of records. Each record is an Owner, and specifically what they own. There will be a field for OwnerName that I want to figure out a way to pull the data into a database like;

Table(Owners) - make sure owner is listed only once

Table(Properties) - joined to owners showing all properties that person owns

Now the tricky part, the owner names might not be exactly the same. Some records might have;

Smith, John
John Smith
Smith, John T
etc.

To make matters worse, this will be a continuous process. I will receive updated excel spreadsheets from time to time and will need to import the new records, many times overwriting the old data. For the good news, there should be an OwnerID that will be unique within the excel data. So as I am merging similar records into the Owners table, I should have a list of OwnerID's that can forever be used to link to the owner.

View 3 Replies View Related

Transact SQL :: Script To Merge Similar Content?

Jun 17, 2015

I have a table which contains single legs. E.g.

Row Leg-ID From To On DateFrom DateTo DOW

1 ABC123 AAA BBB CCC 01JAN15 01JAN15 1

2 ABC123 AAA BBB CCC 07JAN15 07JAN15 1

3 ABC123 AAA BBB CCC 14JAN15 14JAN15 1

4 ABC123 XXX YYY ZZZ 21JAN15 21JAN15 1

I now want to merge those single records, which follow a pattern. For the above case this would be Row 1+2+3, so the result should be:

Row Leg-ID
From To
On DateFrom
DateTo DOW
1 ABC123
AAA BBB
CCC 01JAN15
14JAN15 1
3 ABC123
XXX YYY
ZZZ 14JAN15
14JAN15 1

The pattern is, that the legs from Row 1 and 2 have identical attributes (Leg-ID, From, To, On, DOW) and are on consecutive weeks on the same weekday. I was doing this through a while-look:

check if there is a record matching the following week (a.DateTo = dateadd(d, 7, b.DateFrom))if there is a match, then update the previous week record DateTo with the following week DateFromdelete the following week record but this is very slow, for 50T rows it runs approx. 6 hrs to shrink everything..

View 4 Replies View Related

Count Records With Almost Similar Names

Oct 1, 2015

I want to count records which has almost similar name. Suppose record has company name like comcast and other record has company name like Comcast-xfinity, so how would I count this 2 records as Comcast?

View 4 Replies View Related

Searching Similar Records Within A Table

Oct 8, 2007

Hi,

I have 4 tables :

Code Block
Create Table #Request ( [requestid] int , [stateno] nvarchar(5) , [cityno] int , Callid int, UniqueNo int);
Create Table #RequestDetail(reqdetailid int, [customername] Varchar(20), [customerage] int, requestid int);
Create Table #Call(Callid int,Calltype int,callDetailid int )
Create Table #CallDetail(callDetailId int,empid int)

Insert into #CallDetail VALUES(12123,1)
Insert into #CallDetail VALUES(53423,1)
Insert into #CallDetail VALUES(6532,1)
Insert into #CallDetail VALUES(82323,1)
Insert into #CallDetail VALUES(124235,1)

Insert Into #Call VALUES(111,1,12123)
Insert Into #Call VALUES(112,1,53423)
Insert Into #Call VALUES(114,1,6532)
Insert Into #Call VALUES(123,2,6532)
Insert Into #Call VALUES(134,1,124235)

Insert Into #request Values('324234','SA023',12,111,0);
Insert Into #request Values('223452','SA023',12,112,0);
Insert Into #request Values('456456','SA024',12,114,0);
Insert Into #request Values('22322362','SA024',44,123,0);
Insert Into #request Values('22654392','SA023',12,134,0);

Insert into #requestdetail values(1, 'Bill',23,'324234')
Insert into #requestdetail values(2, 'Tom',25,'223452')
Insert into #requestdetail values(3, 'Bobby',27,'456456')
Insert into #requestdetail values(4, 'Guck',29,'22322362')
Insert into #requestdetail values(5, 'Bobby',33,'22654392')

1. My stored proc will take the CallDetailID.

2. I have to find out the requests made on this calldetail.

3. After getting the request, i want to take the Customername, cityno of the request/requestdetail and pass it again to #request table to search for any duplicates within the request.

4. If found return the details of the original record :

[A similar requirement was solved earlier, but the structure has changed. This is a seperate requirement with different table strucure. Hence a new post. ]

thanks

View 7 Replies View Related

Returning Random Records And NOT Similar (random Questions)

Jul 20, 2005

Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke

View 1 Replies View Related

How To Basically Copy Tables With New Names Rather Than Create Similar Tables From Similar Manual Input.

May 26, 2007

I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.

View 4 Replies View Related

How To Merge Records Together

Jul 2, 2014

If I have a table similar to below

ID Field1 Field2 Field3
01 Red
01 Green
01 Blue
02 Blue
02 Yellow
02 Red

How would I merge the records together to appear as below

ID Field1 Field2 Field3
01 Red Green Blue
02 Blue Red Yellow

Note there will never be 2 Field1's populated for the same ID and also there are many columns so ideally I dont want to have to reference field1 field 2 etc.

View 10 Replies View Related

Merge Sub-records, Is This Possible ?

Feb 25, 2008

Good afternoon,

Our application is a kind of Wiki in 3 languages. We store it in 2 tables:
- tblMenu (MenuID, TitleEN, TitleFR, TitleSP, ...)
- tblTopic (TopicID, MenuID, Language, Description, Date, ...)

We would like to have side by side the 3 descriptions. Is this possible to *merge* the 3 corresponding records in tblTopic (let's say with MenuID = 1) into one output record ?

Thank you,
Phil.

View 2 Replies View Related

Query To Merge Duplicate Records

Jun 6, 2007

Hello,
I have the following Query:
1    declare @StartDate char(8)2    declare @EndDate char(8)3    set @StartDate = '20070601'4    set @EndDate = '20070630'5    SELECT Initials, [Position], DATEDIFF(mi,[TimeOn],[TimeOff]) AS ProTime6    FROM LogTable WHERE 7    [TimeOn] BETWEEN @StartDate AND @EndDate AND8    [TimeOff] BETWEEN @StartDate AND @EndDate9    ORDER BY [Position],[Initials] ASC
The query returns the following data:
Position                                           Initials ProTime     -------------------------------------------------- -------- ----------- ACAD                                               JJ       127         ACAD                                               JJ       62          ACAD                                               KK       230         ACAD                                               KK       83          ACAD                                               KK       127         ACAD                                               TD       122         ACAD                                               TJ       127

What I'm having trouble with is the fact that I need to return a results that has the totals for each set of initials for each position. For Example, the final output that I'm looking to get is the following:
Postition                       Initials                 ProTime
ACAD                           JJ                       189ACAD                          KK                       440ACAD                          TD                        122ACAD                          TJ                         127
Any assistance greatly appreciated.

View 3 Replies View Related

Merge Join - Output 0 Records

Apr 14, 2008

I have a sql statement that joins two tables and I get back a few thousand records when I run it in query tool in management studio.

But when I use SSIS merge join to join the two tables my output is 0 records.

I did sort the key column in both tables by setting 'sortkeyposition' property to 1 in advanced editor for output of both tables.

however the merge join returns nothing to my destination tables. Also I am doing a inner join. The task runs without error but returns nothing as well.. any ideas?

View 5 Replies View Related

Sql Server Merge Replication Deleting Records

Mar 23, 2006

Hello,

I am having problems with my sql merge replication. Whenever a user syncs up to my main database, most of their records are deleted instead of being merged.
Or the records on the main database are inserted and it replaces the whole table with the records on the remote laptops. Is there a way to prevent this from happening? Someone please help me.

Corey

View 7 Replies View Related

MERGE Statement - Skipping Locked Records

Aug 27, 2012

skip locked records in a MERGE statement and output the list of skipped records.

Through the documentation, internet posts and testing, I believe it is NOT possible. MERGE acts like a single atomic DML statement, and therefore cannot avoid locked records.

I can use the READPAST hint, which will skip the row-locked records. However, it could actually insert duplicate keys in certain cases (as it is ignoring records, i would guess), which would not be acceptable.

View 1 Replies View Related

Delete And Merge Duplicate Records From Joined Tab

Oct 21, 2014

Im trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?

SELECT
a1z103acno AccountNumber
, a1z103frnm FirstName
, a1z103lanm LastName
, a1z103ornm OrgName
, a3z103adr1 AddressLine1
, A3z103city City

[Code] ....

Delete and merge duplicate records from joined tables? I am trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?

Select a1z103acno AccountNumber, a1z103frnm FirstName, a1z103lanm LastName, a1z103ornm OrgName, a3z103adr1 AddressLine1, A3z103city City, A3z103st State, A3z103zip Zip, a6z103area AreaCode, a6z103phon PhoneNumber, a8z103mail Email from proddta.fz103a1 with (nolock) inner join proddta.fz103a2 with (nolock) ON a1z103acno = a2z103acno INNER JOIN proddta.fz103a3 with (nolock) ON a2z103adid = a3z103adid and a2z103actv = 'Y' and a2z103prim = 'Y' LEFT OUTER JOIN proddta.fz103a5 with (nolock) ON a1z103acno = a5z103acno and a5z103actv = 'y' and a5z103prim = 'Y' INNER JOIN proddta.fz103a6 with (nolock) ON a5z103phid = a6z103phid LEFT OUTER JOIN proddta.fz103a8 with (nolock) ON a1z103acno = a8z103acno and a8z103actv = 'Y' and a8z103prim = 'Y'

View 2 Replies View Related

Merge: Records Associated By A JOIN Filter Not Being Sent To A Subscriber

Jan 25, 2007

Hi,

I have a merge (SQL 2005 Standard -> Express) topolgoy which is having problems

The main problem is that the join filters don't seem to work for one area and I am hoping someone can help me with some troubleshooting advice

There are 140+ tables in the topology but the ones causing particular pain are a parent child relationship where the child is actually a bridge/linking table to another table.

Therefore although it is a parent child in the replication filters it is the reverse. i.e. the child has the paramterised filter on it and the parent is one level down joined by it's id. There are other tables joined to this parent table but it stays at three levels deep. The @join_unique_key therefore is set to 0 as is the partition options for the parent /child relationship.

However, when we synchronise we have a problem. The rows get inserted in to the database in RI order but only the child records are replicated down to the subscriber.

The child table with the parameterised filter has 13 articles joined to it in total and one of the other branches of join filters go down as deep as four levels. Most though do not.

Does anyone have any suggestions as to why this might be happening? Any help would be greatly appreciated.

Cheers, James

P.S. I should add this problem only occurs when the edits are made at the publisher. If new records are added at the subscriber everything is fine.

View 9 Replies View Related

Integration Services :: Not Able To INSERT Records With MERGE Query

Nov 23, 2015

I have a source table #source with columns 'source', 'patientcode' ,'patientdesc' and it has 4 records as below

source patientcode patientdesc
canada abc patient1
canada efg patient2
canada hij patient3
canada klm patient4

I have a target table and it has 2 records as below.

source prefix tgt_patientcode tgt_patientdesc
canada cn abc patient1
canada cn efg patient2

Now, I want to merge the source data with target table -that means, if the records are already avaible in target, then ignore and if it does not available then INSERT.

This is the query i used but new records are not getting inserted.

MERGE #target T
USING #source S
ON S.SOURCE=T.Source
WHEN NOT MATCHED BY TARGET THEN
INSERT ( Source, Prefix ,tgt_patientcode ,tgt_patientdesc)
VALUES ('Canada' , 'cn' , s.patientcode, s.patientcode);

I want the output as below

source prefix tgt_patientcode tgt_patientdesc
canada cn abc patient1
canada cn efg patient2
canada cn hij patient3
canada cn klm patient4

DDL as below :

create table #target (source varchar(100),prefix varchar(2),tgt_patientcode varchar(100),tgt_patientdesc varchar(100))
insert into #target values ('canada','cn','abc','patient1')
insert into #target values ('canada','cn','efg','patient2')

[Code] ....

View 2 Replies View Related

Dec 13, 2004

Folks, i have to create four fields in every user table within my database:

CREATED_BY VARCHAR(25), CREATED_DATE [DATETIME], MODIFIED_BY VARCHAR(25), MODIFIED_DATE [DATETIME]

There are more than hundred tables, so i wanna automate this. i am tryin to do this in a cursor: please guide!

declare @name VARCHAR (50)
declare cur cursor
fast_forward
for select name from sysobjects where type='u' and status not like '-%'
open cur
WHILE (1=1)
BEGIN
FETCH NEXT
FROM cur
INTO @name
IF @@fetch_status = 0
BEGIN
ALTER TABLE @name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD created_date [DATETIME]
GO
ALTER TABLE @name
ADD modified_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD modified_date [DATETIME]
END
ELSE
BREAK
END
DEALLOCATE cur

I also want that if one column for a table exists; the other columns should be created rather than it quits.

Howdy!

View 4 Replies View Related

Select Similar Data

Aug 15, 2012

finding is queries to find duplicate data.Basically I have a picklist table in a database and I have discovered that there are what looks like duplicate data (because the name is the same) but there is a different number on the end, as you can see from an example below.

NO, ENTRY
24, John Doe|26|05768
24, John Doe|26|5768

Do you know if there is a sql query that can be ran against this table that will look through the ENTRY column and select fields that are similar and not duplicates (duplicates can't exist due to PK constraints)

View 8 Replies View Related