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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Jan 30, 2006
Dear All,
I need to identify duplicate records in a table. TableA [ id, firstname, surname] I’d 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
I’m not sure how to write this query, can someone help? Thanks in advance!
View 3 Replies
View Related
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
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
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
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
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
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
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
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
