SQL Server 2014 :: Duplicate Record Results On 2 One To Many Tables?

Feb 1, 2015

I have 3 Tables

TableA - TAID, Name, LastName
TableB - MaleFriendsName, MaleFriendsLastName [One to many]
TableC - FemaleFriendsName, FemaleFriendsLastName [one to many]

A query returns duplicate results from TableB as well as TableC

TableB and TableC have nothing in common and should not interfere with each other.

with TwoTables as (
select
a.QuickSpec as QuickSpecId,

[Code].....
Resultset returns duplicate values on TableB AND only for 1 record in the results [As per Lynn examples]

View 1 Replies


ADVERTISEMENT

SQL Server 2014 :: Eliminate Duplicate Rows When Joining Multiple Tables

Jun 8, 2015

We have the below query which is pulling in Sales and Revenue information. Since the sale is recorded in just one month and the revenue is recorded each month, we need to have the results of this query to only list the Sales amount once, but still have all the other revenue amounts listed for each month. In this example, the sale is record in year 2014 and month 10, but there are revenues in every month as well for the rest of 2014 and the start of 2015 but we only want to the sales amount to appear once on this results set.

SELECT
project.project_number,
project.country_code,
project.project_desc,
gsl.global_service_line_desc,
buy.buyer_desc,

[Code] ....

View 9 Replies View Related

SQL Server 2014 :: Exclude Duplicate And Fetch Max Of X Column

Sep 11, 2014

I want to fetch max of Field2 if duplicate records in Field1 and rest of the values of field1 , below is the sample format.

Field1 Field2 Field3 Field4
32 375 abc-xyz A
32 379 xyz-efg A
55 405 abc-xyz B
55 407 xyz-efg B
132 908 abc-xyz C
132 999 xyz-efg C
152 800 abc-xyz D
152 850 xyz-efg D
155 900 abc-xyz E
156 925 abc-xyz F
157 935 abc-xyz G

View 2 Replies View Related

SQL Server 2014 :: Getting Error When Trying To Duplicate Some Code Off Of A Website?

Jan 19, 2015

I found an article on the CodeProject website named T-SQL MapReduce. I'm trying to duplicate the SQL code there into a SQL Server 2914 Developer Edition database I've got, and also the C# code using Visual Studio. I've defined the user data types fine (e.g.: word_t, words_t and so on), but I got an error when I tried to create the UDF dbo.mapper from the page. The error I got was:

Msg 102, Level 15, State 1, Procedure mapper, Line 5

Incorrect syntax near 'CALLER'.

The SQL code I used is straight off of the page; it looks like this:

[code=sql]
create function dbo.mapper(@documents DocumentsWithAutoIncrementIndexTable READONLY, @sep nvarchar)
returns @t TABLE ( wordindex int, word word_t )
WITH EXECUTE AS CALLER
[/code]

What have I done wrong? I'm guessing that the author has done something like left off a variable declaration or something like that. what am I missing?

View 6 Replies View Related

SQL Server 2014 :: Find Duplicate Rows Like Same Entries More Than One Time?

Sep 11, 2014

i have a table like below

create table staff_attendance
(
attendance_id int,
attendace_date datetime,
staff_id int,
working_year int,
hours int
)

values like

1 2014-06-30 00:00:00.0ST10121
2 2014-06-30 00:00:00.0ST10122
3 2014-06-30 00:00:00.0ST10122 ----same entry like previous one
4 2014-07-01 00:00:00.0ST10121
5 2014-07-01 00:00:00.0ST10122
6 2014-07-02 00:00:00.0ST10121
7 2014-07-02 00:00:00.0ST10122
8 2014-06-30 00:00:00.0ST10221
9 2014-06-30 00:00:00.0ST10222
10 2014-07-01 00:00:00.0ST1022 1
11 2014-07-01 00:00:00.0ST102 22
12 2014-07-02 00:00:00.0ST102 21
13 2014-07-02 00:00:00.0ST102 22

I Need to find the duplicate rows like same entries which is having more than 1 rows.... how do i find?

View 3 Replies View Related

TOUGH INSERT: Copy Sale Record/Line Items For Duplicate Record

Jul 20, 2005

I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria

View 6 Replies View Related

SQL Server 2014 :: Count Duplicate Records Based Upon Account Number?

Jul 16, 2014

I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable.i need to check whether count > 0 or not in stored procedure.I have used below query.It is not working.

SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0
IF (@_Stat_Count >0)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END

View 9 Replies View Related

SQL Server 2014 :: Error On Unique Index - Duplicate Key When Insert / Select

Aug 4, 2014

I have an issue where I am getting an error on an unique index.

I know why I am getting the error but not sure how to get around it.

The query does a check on whether a unique value exists in the Insert/Select. If I run it one record at a time (SELECT TOP 1...) it works fine and just won't update it if the record exists.

But if I do it in a batch, I get the error. I assume this is because it does the checking on the file before records are written out and then writes out the records one at a time from a temporary table.

It thinks all the records are unique because it compares the records one at a time to the original table (where there would be no duplicates). But it doesn't check the records against each other. Then when it actually writes out the record, the duplicate is there.

How do I do a batch where the Insert/Select would write out the records without the duplicates as it does when I do it one record at a time.

CREATE TABLE #TestTable
(
Name varchar(50),
Email varchar (40)
)
Insert #TestTable (Name,Email) Values('Tom', 'tom@aol.com')

[Code] .....

View 1 Replies View Related

SQL Server 2014 :: Update Row Always Has Results But Still Doesn't Work

Jun 11, 2014

I have this script in my database, but it always gives 2054 rows back and if I actually DO change something it doesn't even notice...

UPDATE a
SET a.[omschrijving]=SP.[omschrijving]
,a.[verkoopprijs]=SP.[verkoopprijs]
,a.[gewijzigd]=getDate()
FROM [artikelen] a
LEFT OUTER JOIN [Hofstede].[dbo].[sparepartsupdate] SP
ON a.PartNrFabrikant = sp.PartNrFabrikant
WHERE ((A.omschrijving != SP.[omschrijving]) OR (A.[verkoopprijs] != SP.[verkoopprijs]))

View 9 Replies View Related

SQL Server 2014 :: Truncates When Attaching Query Results

Jul 6, 2014

Installed SQL Server 2014 CU1. While testing sp_send_dbmail I noticed the query results, when attached are cut off or truncated. Max file size has been 64k -65k. I set the max file size to 104857600 and set @query_no_truncate = 1.

View 9 Replies View Related

SQL Server 2014 :: CLR Function And NET Framework Return Different Results

Nov 1, 2014

I have rather simple CLR function:

[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString GetUserName()
{
return (SqlString)WindowsIdentity.GetCurrent().Name;
}

When I get result from .NET console app, I get correct answer "JungleSektor". However, when SQL Server executes this code, it gives me "NT ServiceMSSQL $ SQL2014". How to get correct result?

View 1 Replies View Related

SQL Server 2012 :: Finding Duplicates And Show Original / Duplicate Record

Nov 3, 2014

There are many duplicate records on my data table because users constantly register under two accounts. I have a query that identify the records that have a duplicate, but it only shows one of the two records, and I need to show the two records so that I can reconcile the differences.The query is taken from a post on stack overflow. It gives me 196, but I need to see the 392 records.

How to identify the duplicates and show the tow records without having to hard code any values, so I can use the query in a report, and anytime there are new duplicates, the report shows them.

SELECT
[groom_first_name]
,[groom_last_name]
,[bride_first_name]
,[bride_last_name]

[code]....

View 5 Replies View Related

SQL Server 2014 :: Pivot Data Results - Concatenate All Products Of Client Together

Mar 26, 2015

I am trying to pivot my data results so that instead of showing multiple rows for each product a client has it will show one line for each client and concatenate all of their products together.

For example, if I run the following query:

SELECT
[Year],
[Client_Name],
[Product_Name]
FROM My.dbo.Table
GROUP BY
[Year],
[Client_Name],
[Product_Name]

I get the following result set:

YearClient_NameProduct_Name
2014BobHosting
2014BobDevelopment
2014BobTesting
2014FredDevelopment
2014FredConsulting
2014MaxConsulting
2015BobHosting
2015BobTesting

What I want to get back as the result set is the following:

YearClient NameProduct-List
2014BobHosting-Development-Testing
2014FredDevelopment-Consulting
2014MaxConsulting
2015BobHosting-Testing

So, I would only get one record back for each Client for each Year with a list of all of their products concatenated together.

View 2 Replies View Related

SQL Server Admin 2014 :: Gathering Results From Extended Events Trace

Jun 16, 2015

I need to find any stored procedures that have not been used over a certain time period.

I have set up an extended events session to gather sp_statement_starting and _completed.

The trace returns the object_id's of many stored procedures. I then query the sys.procedures and plug in the object_id to return the stored proc name.

Do I have to repeat this process for every different object_id or is there a way I can query the trace results, using the object_id as my search criteria as one query ?

View 9 Replies View Related

SQL Server Admin 2014 :: Full Text Search Not Returning Certain Results?

Oct 28, 2015

We are running SQL 2014 SP1. We are using defined Full text indexes on several tables in the database. However, on one specific set of servers, a certain search will not return any data. This exact same search works on another set of servers built identically. The first responses I'm sure will be stop list, but I have dropped and recreated the FTI multiple times with different stop lists or no stop list at all and get the same results.

The specific word being searched on is YUM. If I change the value to YUMk, it actually returns, and if I change the data to TUM it returns, but YUM does not. This exact query is working on multiple other systems, so it seems to be something environmental, but I haven't been able to pinpoint it.

View 3 Replies View Related

SQL Server 2014 :: Missing One Record When Selecting By Date Range

May 26, 2015

When I run this query:

SELECT orderno, *
FROM _order
WHERE order_date >= '5/14/2015 00:00:00'
AND order_date < '5/15/2015 11:59:59'
ORDER BY order_date asc

I get a result of:

A1G7222015-05-14 13:00:11.143
A1G7232015-05-14 13:33:35.407
A1G7242015-05-14 13:39:16.657
A1G7252015-05-14 14:25:43.507
A1G7262015-05-14 14:29:18.050
A1G7272015-05-14 15:38:12.263

But I know there is one more record that falls into 05/15/2015

A1G7282015-05-15 12:26:52.807

Can you see what I am missing in my query in order for me to retrieve the missing record A1G728?

View 9 Replies View Related

SQL Server 2012 :: How To Hide Specific Record(s) From Results

Nov 2, 2015

I have written a SQL statement.There is a table called customer.It contains all customer data with customerid as PK.There is another table called logs and it contains customerid as foreign key and it contains a field to keep more than 90 days older user accounts.That field name is "Checked"

What I need get all records from these 2 tables and remove/hide more than 90 days older customers from record set.See my illustration.

I have written this code but I dont understand how to remove more than 90 days older user from result (because customer table doesnt contain a record called "Checked")

SELECT *
FROM [dbo].[Customers],[dbo].[VIESLog] WHERE [dbo].[VIESLog].[Checked] < DATEADD(day, -90, GETDATE())

View 6 Replies View Related

SQL Server 2014 :: Selecting And Merging Records For Singular Complete Record

Jan 24, 2015

I have a database full of different types of leads some for company A some for company B and so on, each doing a different service. However the leads from B can be used for A and leads from A can be used for B, so I want to merge the data.

Example:

Phone Number Name Home Owner Credit Insurance
727-555-1234 Dave Thomas Yes B
727-555-1234 Dave Thomas Gieco

I would like the end result to be one record:

Phone Number Name Home Owner Credit Insurance
727-555-1234 Dave Thomas Yes B Gieco

Since these were imported into SQL they all have a unique ID, here are the current labels

ID,phone_ number,first_ name,last_name,address1, address2, address3,city,state,postal_code,HOME_OWNR,HH_INCOME,CREDIT_RATING,AGE,MATCH,source_id,
title,comments,dnc_flag,provider,vehicle,coverage,alt_phone,email,marital status,dob

View 8 Replies View Related

SQL Server 2014 :: Stored Procedure - Add A Record To Local Database Executing In Cloud Environment

May 20, 2015

Is there a way using a stored procedure in a local database to add a record to a database executing in a cloud environment when both entities reside in different domains?

View 2 Replies View Related

Duplicate Results

Mar 22, 2007

Hello,

I'm new to SQL and tried to develop a simple query on multiple tables of a database. The query works but returns duplicate records even though there aren't duplicate records in the database.

Here is the script:

SELECT LelyDevice.iAddress, LelyDeviceVisit.tEndVisitDateTime, LelyCow.dBirthDate, LelyCow.iUserNumber, LelyAstronautVisit.lRefusal,
LelyAstronautVisit.lFailedMilking, LelyMilkVisitData.nMilkYield, LelyLactationProduction.dCalvingDate
FROM LelyDevice INNER JOIN LelyDeviceVisit ON LelyDevice.PK_LelyDevice = LelyDeviceVisit.FK_LelyDevice
INNER JOIN LelyCow ON LelyDeviceVisit.FK_LelyCow = LelyCow.PK_LelyCow
INNER JOIN LelyAstronautVisit ON LelyDeviceVisit.PK_LelyDeviceVisit = LelyAstronautVisit.FK_LelyDeviceVisit
INNER JOIN LelyMilkVisitData ON LelyDeviceVisit.PK_LelyDeviceVisit = LelyMilkVisitData.FK_LelyDeviceVisit
INNER JOIN LelyLactationProduction ON LelyCow.PK_LelyCow = LelyLactationProduction.FK_LelyCow

WHERE LelyDeviceVisit.tEndVisitDateTime BETWEEN '2006-07-29 03:20:00.000' AND '2006-08-03 08:58:59.000' AND LelyAstronautVisit.lFailedMilking = 1
ORDER BY LelyDeviceVisit.tEndVisitDateTime DESC


and here is the result:
13012006-08-03 08:57:39.0002002-07-03 00:00:00.000209015.12005-06-02 00:00:00.000
13012006-08-03 08:57:39.0002002-07-03 00:00:00.000209015.12004-06-06 00:00:00.000
13012006-08-02 20:21:54.0002002-07-03 00:00:00.000209012.72005-06-02 00:00:00.000
13012006-08-02 20:21:54.0002002-07-03 00:00:00.000209012.72004-06-06 00:00:00.000
11012006-08-02 19:26:44.0002002-11-20 00:00:00.000221010.02005-03-27 00:00:00.000
13012006-08-02 19:16:33.0002002-11-20 00:00:00.000221012.32005-03-27 00:00:00.000

As you can see the first 2 lines are the same date and time and the next 2 and so on.

View 5 Replies View Related

Duplicate Results

Jun 13, 2007

I have the following query. I want it to find how many minutes used by MDN (which is the column for the phone numbers in my database). It keeps giving me duplicate MDN's. How can I get it to give me the total minutes used by an MDN per day?

SELECT DISTINCT MDN,sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM VoiceCallDetailRecord
WHERE Durationseconds >0 and CallDate >= '02/19/2007' and calldate < '02/20/2007'
and NOT (Left(Endpoint,3) IN ('011')
or (Left(Endpoint,4) IN ('1340','1876','1868','1809',
'1246','1242','1780','1403',
'1250','1604','1807','1519',
'1204','1506','1709','1867',
'1902','1705','1613','1416',
'1905','1902','1514','1450',
'1418','1819','1306','1867')))
AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59'))
AND DATEPART(weekday, CallDate) in (2,3,4,5,6))
Group By MDN
UNION
SELECT DISTINT MDN,sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM ZeroChargeVCDRecord
WHERE Durationseconds > 0 and CallDate >= '02/19/2007' and calldate < '02/20/2007'
and NOT (Left(Endpoint,3) IN ('011')
or (Left(Endpoint,4) IN ('1340','1876','1868','1809',
'1246','1242','1780','1403',
'1250','1604','1807','1519',
'1204','1506','1709','1867',
'1902','1705','1613','1416',
'1905','1902','1514','1450',
'1418','1819','1306','1867')))
AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59'))
AND DATEPART(weekday, CallDate) in (2,3,4,5,6))
Group By MDN
order by mdn

View 9 Replies View Related

Delete Duplicate Rows From Two Tables With Same Structure In Sql Server 2000

Aug 20, 2007

Hi

I want to delete the duplicate rows from two tables and get the resultant non-duplicate rows from both the tables into another table

View 4 Replies View Related

Accidental Duplicate Results...

Jun 4, 2007

ALTER PROCEDURE discussions_GetTopics(@board_id as int)ASSELECT     discussions_Topics.*, discussions_Posts.*, user_1.UserName AS Topic_Author_Username,                       user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_IDFROM         discussions_Topics INNER JOIN                      discussions_Posts ON discussions_Topics.topic_id = discussions_Posts.topic_id INNER JOIN                      aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN                      aspnet_Users AS user_2 ON user_1.UserId = discussions_Posts.poster_idWHERE     (discussions_Topics.board_id = @board_id)  I am simply trying to return a result for each topic, that has user info for both the author of the topic and the author of the last post (user_1, user_2) The problem is, it will return multiple datarows with the same topic, and each of them have a different last post author..  when there can only be one last poster...   idk..  im confused..  help?

View 3 Replies View Related

Duplicate Query Results

May 15, 2001

I have duplicate results in from my query. I am using the distinct clause but this does not seem to be working. In the example below, I do not want the 79.15 to repeat multiple times eventhough the first column has all distinct values. I am using distinct in my select but this is not stopping the duplicates on the second row. Any guidance would be greatly appreciated. Thanks

820.2179.15
820.2279.15
997.379.15
48679.15
038.979.15
428.079.15
263.979.15
276.579.15
250.0079.15
276.879.15
401.979.15
396.379.15
397.079.15
31179.15
365.979.15
E88579.15
E849.779.15
663.3173.59
646.6173.59
599.073.59
V27.073.59

View 3 Replies View Related

Duplicate Record

Jan 30, 2006

Dear All,

I need to identify duplicate records in a table. TableA [ id, firstname, surname] Id like to see records that may be duplicates, meaning both firstname and surname are the same and would like to know how many times they appear in the table

Im not sure how to write this query, can someone help? Thanks in advance!

View 3 Replies View Related

Duplicate Record

Jan 14, 2007

Hi guys how do you hide duplicate records, how would I do a select statement for that

In (SELECT [AccountNo] FROM [2006_CheckHistory] As Tmp GROUP BY [AccountNo] HAVING Count(*)>1 )

I have about had it with this database I have been asked to make a report out of

View 14 Replies View Related

Removing Duplicate Results With One Column Different?

Aug 9, 2012

I am trying to get people from my table that have closed accounts. However, in my table many people have more than one account. They will have multiple closed accounts and some active accounts. I need to get the people with only closed accounts.

Values in the table

Code:
name surname status Closed Number
----------- --------- ----------- ------------- ----------------------------
Jeff Burns closed 2012/01/01 142
Tina Drewmor closed 2008/05/20 546
Jeff Burns active 1900/01/01 354
Kyle Higgin active 1900/01/01 851
Tina Drewmor closed 2009/04/14 154

The query I am using so far is:

Code:
select
d.name,
d.surname,
s.status,
s.closed,
s.number
from
d d inner join s s on d.number = s.number
where
s.status = 'closed'

What I need to see in the results

Code:
name surname status Closed Number
----------- --------- ----------- ------------ -----------------------------
Tina Drewmor closed 2008/05/20 546
Tina Drewmor closed 2009/04/14 154

View 4 Replies View Related

Duplicate Results In 2 Columns But Reversed

Feb 17, 2004

I have to write a query which extracts everyone from a table who has the same surname and forenames as someone else but different id's.

The query should have a surname column, a forenames column, and two id columns (from the person column of the table).

I need to avoid duplicates i.e. the first table id should only be returned in the first id column and not in the second - which is what i am getting at the mo.

This is what i have done

select first.surname, first.forenames, first.person, second.person
from shared.people first, shared.people second
where first.surname= second.surname
and first.forenames = second.forenames
and not first.person = second.person
order by first.surname, first.forenames

and i get results like this

Porter Sarah Victoria 9518823 9869770
Porter Sarah Victoria 9869770 9518823 - i.e. duplicates

cheers

View 5 Replies View Related

SQL Server 2014 :: Create Auto Relationship Between Tables

May 12, 2014

I would like to create a auto relationship between tables.

Currently I am using Northwind DB with tables (Orders, OrderDetails, Customers)

Orders ( OrderId, Customerid)
OrderDetails(OrderId)
Customers(CustomerID)

Now, if the user wants to generate a relationship automatically based on SAME FIELD Names.

What is the approach?

View 9 Replies View Related

SQL Server Admin 2014 :: Rebuild CMS Tables From XML File?

Jul 23, 2014

I had to reinstall my local copy of SQL a few weeks ago, which naturally overwrote the

msdb.dbo.sysmanagement_shared_server_groups_internal and
msdb.dbo.sysmanagement_shared_registered_servers_internal tables.

However I still have the local XML file that SSMS reads so I can still access the groups, I just get weird errors when trying to re-register my install as the new CMS. How to rebuilt those tables from the XML file or know of a way to repopulate?

View 3 Replies View Related

SQL Server 2014 :: Select Columns From Different Tables Without Join

Jan 20, 2015

How to join 2 heap tables with out any common fields.

for example tbl1 has
col1
1
2
3
4
5

and tbl1 2 has
col2
a
b
c

I want the output like
col1 col2
1 a
2 b
3 c
4
5

is this possible with out using row_number()?

View 9 Replies View Related

SQL Server 2014 :: Using Data From Two Tables In A Single Script?

Jan 20, 2015

Basically I've got two tables with a common column (in this case it's UserID)

The structure of the table Users is basically:

UserID FirstName LastName
1 Jim Smith
2 Nancy Orr
3 Ken Stephenson

And the structure of the classes table is

UserID ClassID
1 AAA
1 BBB
2 AAA
3 BBB

What I'd like to do is select the FirstName and LastName the users table who have a UserID in both Classes and Users.

The tricky part of it is that I only want to return the FirstName and LastName of userID who have multiple ClassID in the classes table. (hopefully I'm making sense when I explain this)

In this case I'd like it to return just Jim Smith's name because he's the only userID who has two ClassID entries.

So far I've come up with

select UserId, count(*) as MultipleClasses
from classes
group by UserID
having count(*) > 1;

which outputs the UserID and a colum called MultipleClasses with how many classes each ID returned has.

how would I do something like

Select firtname, lastname
from users
where Userid=

The output from that initial bit finding users with multiple classes.

View 3 Replies View Related

SQL Server Admin 2014 :: Storing Very Large Tables?

Jan 24, 2015

We are in the middle of re-designing few tables (namely transaction tables) that would store very large data and would be hosted on cloud (Azure). The old design of this product breaks transaction tables into monthly tables. i.e. say ORDERS Table would be physically broke into twelve monthly tables over a year like ORDERS0115 (mmyy), ORDERS0215 and so on.

We are in the opinion that keeping the entire transactions in one Table is better. Would like to know what's the best practices for transaction tables like the one mentioned above? Is it better to use one table with partitions. I read somewhere that partitions can slow down SELECT queries if not designed and thought properly.Since this would be hosted on cloud (Azure), do you think some additional things are to be taken care? How a site like Amazon keeps their transactions tables?

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved