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
ADVERTISEMENT
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 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
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
Feb 4, 2015
Query should only return less than 3000 records but its returning over 4M. It needs to show all duplicates records.... All the info are on the same table VENDFIl, so I used a self join but it seems to be looping..
SELECT A.FEDTID, B.VENDOR, C.NPI_NUMBER
FROM VENDFIL A, VENDFIL B, VENDFIL C
GROUP BY A.FEDTID, B.VENDOR
View 5 Replies
View Related
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
View Related
Jun 10, 2006
Hi,Say I have a table Job with columns name, date, salary . I want to getthe name ,date and salary for the date when that person earned maximumsalary. I am using something likeSELECT X.name,X.date,X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);The problem is ; if a person earns maximum salary on two dates, both ofthe dates are printed. I just want to get any one of those two rows.I triedSELECT X.name,Min(X.date),X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);but it gives error.Can anybody please suggest a solution?Regards,Aamir
View 4 Replies
View Related
Jun 16, 2015
I have three tables, Accounts, AccountCustomer and Customers, and the data-relationshiop between are defined according to the image below:
I created also a query (the sql-query below), displaying the customers for every account that is on the table "Accounts", and I got the results, as we can see in the image below:
SELECT A.AccountID,
c.CustomerNo,
c.Surname,
c.Name,
c.TaxNum
FROM Accounts A
left join AccountCustomer ac on ac.AccountID = A.AccountID
left join Customers c on c.CustomerNo = ac.CustomerNo
order by A.AccountID;
As we understand, an "AccountID" have multiple customers, so I want to transform tha multiple results to one row, grouping by AccountID (one account belongs to one or many Customers), like the image below:
I tried to use row_number()-expression to get this, but I didn't make it. So my question is, how can I alter my sql-query to get the final result like image above?
View 6 Replies
View Related
Nov 30, 2007
I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them.
each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field.
Ideas?
Thanks,
Ad.
View 7 Replies
View Related
Oct 2, 2007
Hello Everyone:
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
How do I fix this?
TIA
View 4 Replies
View Related
Apr 1, 2007
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies
View Related
Mar 25, 2015
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
View 4 Replies
View Related
Feb 12, 2008
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
View 1 Replies
View Related
May 14, 2008
Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Thanks
dotnetdev1
View 4 Replies
View Related
Jun 18, 2008
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
BrianAustinHope83432230.7187500.7352941.15625014612201221
GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519
MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218
RobBennettMelville83029240.8275860.8333331.55172411904102117
AdamBiesenthalSafe Haven82929210.9130430.9130431.56521712631001015
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
View 2 Replies
View Related
Oct 19, 2007
Hi,
I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().
I would like to give my users to option of removing individual people from this list but cannot find a way to do this.
I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.
Is there any way to do this? I am still new to stored procedures so any advice would be helpful.
Thanks
View 3 Replies
View Related
Jan 30, 2008
Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.
View 6 Replies
View Related
Aug 17, 2007
I've begun to get the above error from my package. The error message refers to two output columns.
Anyone know how this could happen from within the Visual Studio 2005 UI? I've seen the other posts on this subject, and they all seemed to be creating the packages in code.
Is there any way to see all of the columns in the data flow? Or is there any other way to find out which columns it's referring to?
Thanks!
View 3 Replies
View Related
Mar 9, 2001
Hi,
The DTS or jobs failed due to the ignore duplicate key message. How do
I turn this dup key message off or ignore it so that the jobs will succeed?
thanks,
Rachel
View 1 Replies
View Related
Apr 22, 1999
How can I compare numerous rows in the same table that have the same fname, lname and access_id but different identity values (and other columns) and then keep only the one row which is the most current based on a date col?
This seems so easy, but today I just can't come up with it. Do I have to use a cursor of some kind?
Troy
View 1 Replies
View Related
Jan 12, 2007
Hi guys
How can i get id which are duplicate in a table.
Thanks
View 1 Replies
View Related
Jun 21, 2006
Hi guys,
i have the following tables:
Table1:
[PKID] WITH INDEX "IGNORE DUPLICATE KEY"
Table2:
[FKID]
and i have the following SQL statement:
insert into Table1
select distinct FKID
from Table2
where FKID not in (select PKID from Table1)
The above SQL statement is in a DTS package which raises "Duplicate key ignored" error... i can't see how that can happen since i am already checking the key if it already exists. ..
Please help !!!
TNT :s
View 4 Replies
View Related
Feb 10, 2007
Hi!
Very grateful for some advice... duplicate key.. but how do I solve it.
By Lookup ?
[OLE DB Destination [1647]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".
OLE db SQL: SELECT DISTINCT convert (char(8),date,112) as day, cast(datepart(year, date) as varchar(4)) + cast(datepart(week, date) as
varchar(2)) as weeknr, cast(datepart(year, date) as varchar(4)) + Substring(convert(varchar(10), date, 121), 6, 2) as month, YEAR(date) AS year FROM Purchase
View 5 Replies
View Related
Oct 25, 2007
I'm currently working on a Game DB, To where i can set Primary Key's and such, otherwise the Emu reads it wrong. The current table i am working on, has duplicate row's of every row. (3600 rows instead of 1800) and the duplicates have the same ID as the original row. How would i go about Deleting the Duplicate rows without harming the original? (Sql 2005)
Regards
Vision
View 1 Replies
View Related
Mar 22, 2006
I have a stored procedure that inserts records into a table with a Unique Clustered Index with ignore_Dup_Key ON.
I can run the stored procedure fine, and get the message that duplicate keys were ignored, and I have the unique data that I want.
When I try to execute this in a DTS package, it stops the package execution because an error message was returned.
I have tried setting the fail on errors to OFF, but this has no effect.
I found the bug notification that says this was corrected with service pack 1, and have now updgraded all the way to service pack 4, and still get the issue.
I tried adding the select statement as described as a work-around in the bug, and still can't get it past the DTS.
I have verified the service pack, re-booted, etc.....
I am trying this in MSDE 2000a.
Thoughts or comments? Thanks!
View 4 Replies
View Related
Dec 11, 2006
Hello guys! Is it possible to duplicate a primary key?
I would like my database to accept data with the same primary key.
Is it possible?
How do you declare ON DUPLICATE KEY UPDATE?
Please help me. Thanks in advance.
View 1 Replies
View Related
Mar 2, 2007
Hello,
I have the following query,
SELECT GroupInfo.GroupID, GroupInfo.GroupName
FROM GroupInfo INNER Join DeviceGroup ON(DeviceGroup.GroupID=Groupinfo.GroupID)
INNER Join Deviceinfo ON (Deviceinfo.SerialNumber=DeviceGroup.SerialNumber )
It's out put is as follow:
Group ID GroupName
0 Abc
1 Beta
0 Abc
0 Abc
0 Abc
1 Beta
2 Alpha
Now, I want to make such query which will give me result as a Group ID and Group Name but not in repeating manner, Like,
Group ID GroupName
0 Abc
1 Beta
2 Alpha
Hope I explained what I need to see in result pane.
Thanks,
Junior
View 1 Replies
View Related
Feb 15, 2008
hello everyone, how to prevent putting same value into the column in the sqldatabase? while inserting value into the table from asp.net page dynamically,i dont want user to insert same data again and again. is there anyway i can make check the column in the database to look for the duplication value. if not then only i want to make it insert. thanks. jack.
View 1 Replies
View Related
Jul 1, 2004
I want to be able to duplicate a row of data in sql....Does anyone know if there is a sql command that will do that. I have a table with an auto increment primary key and I want to duplicate everything except the key into a new record.
Thanks.
View 2 Replies
View Related
Jun 25, 2001
I used the following select statement to get duplicate records on Case_number column
select cases.distinct case_link, cases.case_number
from cases
group by case_link
having case_number > 1
I got the error message that
"'cases.warrant_number' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
AND
cases.case_number' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Any idea on a better statement to use. THANKS FOR YOUR HELP!
View 3 Replies
View Related
Jun 29, 2001
Hi,
I have a table and this is what i did to get the desired result
Select A.col1,count(A.col1)
from Tab1
group by col1
having count(A.Col1) > 1
i tried this - but it didnot worked - it returned col1 as blanks -
Select A.col1,B.Col2,count(A.col1)
from Tab1 A, Tab2 B
where A.col1 = B.col1
group by A.col1 , b.col2
having count(A.Col1) > 1
As I was looking for all the rows that are apperaing more than once.
Now - The problem -
I have to join this table to another table Tab2 to get the other details.
My Tab2 is a table from where I have to pull the Customer DEtails like name,address etc.
How should I write this query?
Any thinuhts?
TIA
View 1 Replies
View Related
