How would I get three fields out of the database if they are duplicates?
I got it to work with one field but can't get it to work with the other two fields (BIC and PaymentAmt)
SELECT Claim,
COUNT(Claim) AS NumOccurrences
FROM Info
GROUP BY Claim
HAVING ( COUNT(claim) > 1 )
Hi all,
Could someone tell me how to get the data from all tables of the database in the form of insert script? We are moving our databse from SQL Server 2000 to SQL Server 2005. The scripts for the Database, Tables, Views , Procedures, Functions have been obtained and it is only the data that is remaining. Some are small tables with 5 to 6 columns but there are some with 50 odd columns. A friend of mine told me about a procedure that returns a dataset with INSERT statements by passing a table name as a parameter. Such procedure would be of great help.
Thank you
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]....
SQL question here:
I am setting up a blog, and I would like the data list to default to the most recent post ONLY. How would I define the filter to get the most recent post?
I have a publishing date in my table, and I can sort the post, but I have no clue how to select just one row to display.
I can get it to work by adding a dropdown list the is bound to the publishing date column, then retreving the post based on the control, but I don't want to use the control. (Making the control not visible is not an option.
Is there a query or a way to convert duplicates value in a column to non duplicates.
View 14 Replies View RelatedHello:
I have an app that needs to retrieve the list of tables in an SQL database. I see that there is a way to do this with the OLEdb provider (GetOleDbSchemaTable() ), but I can't find a sibling method in SQL. IS there one/ Any Ideas?
Mike
I am storing images within and image field in sql, however when I go to get the data I have two issues.
the first issue is that my image is linked to information on another table but the only way that i have seen getting the image back is to do a:
response.binarywrite(mydatareader.item("image"))
but i am hoping to be able to find a way to use the template functionality with datagrid to align the picture with the information on the table. i am storing the image type and size with the image as well.
the second problem might be related to the load, i am not sure. sometimes, for some images and it just shows the frame of the image with the red x in the middle. The load appears to work (no errors) but since we cannot view them properly we dont know.
Our application wants to store all data in sql so that we can secure it and relate it to projects. in addition, we hope to be able to use this function to upload all sorts of binary documents from jpg, gif, doc, xls, visio, and others as we can. any issues with that process???
we would like to be able to display them directly in the browser where we can but all users have standard office.
any suggestions on either and hopefully both of these situations? we have sort of hit a brick wall.
hi. I am at a new stage in our project and need to figure out how to insert/retreive MP3/Wav files. The way we want to do this is have the file stored on the harddrive while a link to its location is stored in the database.
I have posted this search in other newsgroups and have gotten a lot of philosophical responses, but what i really am looking for is code examples. Please, what i would like to see is "how" this id done in code. If you have done this on your site, i'd really appreciate your posting code here or emailing me examples. Or, if you know of a really good explainaition of how to go about storing/retreiving audio files in the way i mentioned using MS SQL Sever 2000, I'd be equally indebted.
I hope I have explained myself clearly. Thank you for your assistance!
have a peaceful day.
Last week I discovered a server runing a sql database for a vendor application. Last year sometime one of our former employees created a dts to populate some data in peoplesoft from this database. But, over this last weekend, the vendor came in and uninstalled and reinstalled sql server and only restored their specific database. All I have is backups of msdb and master and I'm not sure how to restore our dts package. Any clues would be appreciated.
View 4 Replies View RelatedI Somehow restored the latest database onto the test database. Can I now retrieve the lost data. If Yes , How is it possible.
Akash
Hi all
I have the following table schema and their details as follows:
Code Block
CREATE TABLE #Tbl_Project
(
ID varchar(40),
Name varchar(50)
)
GO
CREATE TABLE #Tbl_User_Project
(
ID int,
ProjectID varchar(40),
Role varchar(20)
)
GO
INSERT INTO #Tbl_Project VALUES('PRJ1','Project 1')
INSERT INTO #Tbl_Project VALUES('PRJ2','Project 2')
INSERT INTO #Tbl_Project VALUES('PRJ3','Project 3')
GO
INSERT INTO #Tbl_User_Project VALUES(1,'PRJ1','PM')
INSERT INTO #Tbl_User_Project VALUES(2,'PRJ1','TL')
INSERT INTO #Tbl_User_Project VALUES(3,'PRJ1','TM')
INSERT INTO #Tbl_User_Project VALUES(4,'PRJ2','PM')
INSERT INTO #Tbl_User_Project VALUES(5,'PRJ2','TL')
INSERT INTO #Tbl_User_Project VALUES(6,'PRJ3','PM')
INSERT INTO #Tbl_User_Project VALUES(7,'PRJ3','TL')
INSERT INTO #Tbl_User_Project VALUES(8,'PRJ3','TL')
GO
--Required Output
--ProjectID ProjectName PM:TL:TM Ratio HeadCount
--PRJ1 Project 1 1:1:1 3
--PRJ2 Project 2 1:0:1 2
--PRJ3 Project 3 1:0:2 3
DROP TABLE #Tbl_Project,#Tbl_User_Project
This is just sample of the values. The ratio obtained should be as follows:
Assuming for a Project 1 there are 2 - PM's , 6-TL's , 15 - TM's then the ratio should be 1:3:7.5 . So please help me in this regard.
Hi every one,
I have a serious problem struggling with sql query the problem is i have a table below
Sno CarId carcharacterstics Charactersticvalue
1. Mercides01 Color Red
2. Mercides01 EngineType Desile
3. Mercides01 Speed/Hr 350KM
4. Maruthi01 Color Red
5. Maruthi01 EngineType Desile
6. Maruthi01 Speed/Hr 250KM
7. BMW Gears Automatic
8. BMW EngineType Desile
9. BMW Speed/Hr 250KM
Now i need to retrieve the carid whose color is red and Engine type is desile the query i have written is
select carid from carchar where ((characterstics='Color' and charactersticvalue=Red) and (characterstics='EngineType' and charactersticvalue=Desile))
But I am not able to retrieve the values. I can retrieve the values correctly if i mention only one characterstics and its value but i cant retrieve if i mention more than that
Hi, im trying to insert values into 2 different tables. The primary key in the first table is an auto increment field, which is then the foreign key in the 2nd table.Is there any way to retrieve the autogenerated value before performing the second insert. I have done some searching on the matter, but to no avail.I tried using LAST_INSERT_ID() but i get the error message that it is not a recognized function name. The other option is using @@identity but i cant see how to put that into my SQL statement.Can anyone help please.Thanks in advanceGareth
View 2 Replies View Relatedhello dudes. i've got the following table:
table name: assassins
fields:
assassinid int, identity, primary key
assassinname nvarchar
assassincallsign nvarchar
knifekills int
sniperkills int
stealthkills int
fibergarotekills int
sample data:
1, Dana White, tapout, 12, 0, 12, 1
2, Chuck Liddell, iceman, 30, 0, 0, 2
3, Timothy Oliphant, FortySeven, 100, 20, 150, 30
supposed i want to retreive only those records whose type of kills is on a given list, how do i do that? the list is actually dynamic since the kill types is actually coming from a checkbox on my page:
[] Knife Kills
[] Sniper Kills
[] Stealth Kills
[] Fibergarrote Kills
supposed i will only check Knife Kills, it will only retreive those records whose knifekills > 0
or if i check Snipers Kills and Stealth Kills, then those records whose sniperkills and stealthkills is > 0.
any of you know how to do that?
U + U + D + D + L + R + L + R + Sel + Start...
We are having a Windows Installer Program (32 bit) which calls the SQLDMO to get the list of installed instances in a 64 bit 2005 sql server machine. This fails as the windows installer tries opens up the 32 bit sqldmo and there is no instance of 32 bit sql server on this machine.
Is there any way by which sqldmo can return all 64 bit sql server instance when called from a 32 bit Application?
Regards,
Ashish
Hi,
I'm new to SQL Server 2005 SSIS. I'm trying to do something very simple, but I cannot figure it out, PLEASE HELP!
I have a flat file, which I read and then insert the data in a database table, that works fine. The problem is that I don't want to insert duplicate records. For example; if I run the package again, it will appent to the table. What I need to do is that if the package runs again, check if the record already exist, based one two columns, date and hour, and do not insert the record.
Thank you,
Aldo
Due to circumstances beyond my control, I have to restore a full database backup from October 1, 2005 and then over 4400 transaction logs to bring a database up to Jan 01, 2006 on a development server (storage team lost all daily full backups from 10/02/2005 to 01/01/2006). Since the backups were not taken on this machine originally, the information I would pull from the sysjobhistory tables to get the transaction backup names is not available. Does anyone have a link or a script to pull the filenames from the backup directory into sql server so I can build a dynamic script to apply the tran logs instead of running all 4400+ of them by hand? Thanks, Tom
View 5 Replies View RelatedHi,
I'm trying to figure out how to retreive connection strings from the machine.config instead of storing the conn strings in a *.dtsconfig file. The reason that I'm trying to use the machine.config is for the simple reason that I'll be able to manage all of the conn strings in one location on each server. (Instead of mutliple dtsconfig files.) Is it posible to utilize the machine.config in SSIS? If so, could someone explain how, or better yet, have an example?
thanks for the help!
Hi all
I have a Table with Following structure ( a Tree Structure )
PK Parent Level Code
--- -------- ------- ------
1 0 0 100
2 1 1 101
3 1 1 102
4 2 2 103
5 3 2 104
6 4 3 105
The same as following Tree as you can see
1__
| 2__
| 4__
| 6
| __
3__
5
I need a query to return the following Result. I think it is possible only through Nested sub-Queries But i don't know how to do that
Could any one help me.?
PK Parent Level Value First-Parent' Code 2nd-Parent's Code 3rd-Parent's Code
---- -------- ------ ------- --------------------- ---------------------- -----------------------
1 0 0 100 NULL NULL NULL
2 1 1 101 100 NULL NULL
3 1 1 102 100 NULL NULL
4 2 2 103 101 100 NULL
5 3 2 104 102 100 NULL
6 4 3 105 103 101 100
Any help greatly would be appreciated.
Kind Regards.
I am having one querry regarding the same line.
In my stored procedure i am fetching the data from one table containing upto 5 to 6 million rows I made use of index in my database but then also I cant optimise my execution time of that sp.
Please help me out of this problem.
hi,i have a database where there is 10 feilds one of which is of datetimeI have to select a row from the table which has two search ConditionsFrist i have to select from the table who has ( say GroupId =1) and From these rows( whose GroupId=1) I have to select at the row that is last entered Max(Date) Thanks InAdvance
View 1 Replies View RelatedI need some help.
I have created a database that looks like the following:
FirstName Table link to Main Table.
I have created a Stored procedure that looks like this:
Create procedure dbo.StoredProcedure
(
@FirstName varchar(20)
)
Declare FirstNameID int
Insert Into Main Table
(
FirstName
)
Values
(
@FirstName
)
Select @FirstNameID = Scope_Identity()
How could I redesign this to check if a value exists and if it exists then simply use that value instead of creating a new duplicate value?
Thanks!!
I have a dilema..... I have a databas eof about 60,000 users and i need to get rid of those users where there is a duplicate email address. I have written an asp utilty that works but is far too taxing on our little server and i thinkk itwill kill it. what it does is for each email address it compares it against all the others.... so for each address it checks against 60,000 other records 60,000 times.... you know what i mean. its pretty phucked.... i tested it on just one record and took about 5mins.
anyway ive been trying to do it in SQL with no luck
here is a simplified version of my dilema
*****
NAMEEMAILAGE
tomtom@mail23
tombomb tom@mail23
petepete@email23
davecool@mail21
stevesteve@mail17
marycool@mail89
thomas tom@mail13
richrich@mail65
richdick@mail65
tomtom@mail23
tomtom@mail23
so what i want to do is cut it down so there are no duplicate email addresses. I want the table looking like this:
*****
NAMEEMAILAGE
tomtom@mail23
petepete@email23
davecool@mail21
stevesteve@mail17
richrich@mail65
richdick@mail65
Can you think of a way to do this?
i tried the following but it gets rid of duplicates of other fields which is not what i want to do
CREATE table distinct_records
SELECT name, Email, Age
FROM duplicate_records
GROUP BY name, Email, Age;
any ideas guys?
thanks
tom.harrow@netpoll.net
i've imported a number of spreadsheets into a database
the schema is
directoryid firstname, lastname, extension
703168 Andrew Lim 78094
703154 Joseph Egan 78888
704548 Andrew Lim 78094
I realized the spreadsheet had a large number of duplicates. How can i delete the duplicates ?
like deleting directoryid 703168 only.
i'm trying to get duplicates out of the my database
SELECT COUNT(*) AS Amount,
Firstname,
surname,
Internalextension
FROM iac.dbo.sf_profil
GROUP BY FirstName, surname, internalextension
HAVING COUNT(*) > 1 order by firstname, surname
How do i alter the query just retrieve records which have firstname and lastname which are similar but different extension numbers ?
Hi,
This is the query which shows me the duplicates
Some of the records have more than one records
I would like to know how to delete the extra records so that I will end up with one record per row.
select
Pricing_Source,
VaR_Identifier,
Price_Date,
PX_Last,
Count(*) as 'count'
from
tblPricesClean
group by
Pricing_Source,
VaR_Identifier,
Price_Date,
PX_Last
having count(*) > 1
order by
count desc
Hi,
Is there a way to find duplicates in one field? For example my query has person_nbr and for each person_nbr on one day they could have used multiple payer_names. I want to be able to count each person_nbr one time but also I want to group by description(which is the name of the provider) and by payer name to see how many person's that the provider seen with each payer. My problem is that if the person had more than one payer they are counted twice. Is there some type of aggregate function to use the first payer in the list??
With PersonMIA (person_id,person_nbr,first_name,last_name,date_of_birth) as
(
select distinct person_id,person_nbr,first_name,last_name,date_of_birth from
(select count(*) as countenc,a.person_id,a.person_nbr,
a.first_name,a.last_name,
a.date_of_birth
from person a
join patient_encounter b on a.person_id = b.person_id
group by a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth )tmp
where tmp.countenc <=1
)
select person_nbr,payer_name,first_name,last_name,description,year(create_timestamp),create_timestamp
from
(
select distinct c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,
tmp.last_name,tmp.date_of_birth,d.payer_name,b.create_timestamp
from PersonMIA tmp
join person a on a.person_id = tmp.person_id
join patient_encounter b on a.person_id = b.person_id
join provider_mstr c on b.rendering_provider_id = c.provider_id
join person_payer d on tmp.person_id = d.person_id
where c.description = 'Leon MD, Enrique'
group by c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,tmp.last_name,
tmp.date_of_birth,d.payer_name,b.create_timestamp
)tmp2
where year(create_timestamp) IN (2005,2006)
group by person_nbr,payer_name,first_name,last_name,description,create_timestamp
Thanks in Advance!
Sherri
Hi,
I'll see if I can explain this clearly. The query below
selects rows from the "hdr_ctl_nbr_status" table if the value in the field "tcn" from that table is found in the table "temp_tcn". I want all fields from the "hdr_ctl_nbr_status" table to be selected BUT only one row. In other words for a tcn with a value "12345678" there are 10 rows returned from the hdr_ctl_nbr_status table, I want only 1. Is there a way I can use SELECT DISTINCT to do this ? I know this usually functions on one or more fields but I want the DISTINCT to be on tcn only BUT return all fields in the query.
Select h.*,'' from hdr_ctl_nbr_status as h WITH (NOLOCK)
where h.tcn in (select tcn from temp_tcn)
Thanks, sorry if this is too confusing. Jeff
Hi, All,
I have two columns of int data in the a table, as my example data shows below.
I want my data returned to be something like those in #test3, but my question is this, how can I do it without using #test2 and #test3?
By the way, the business requirement doesn't care it's min/max or any ID when one side has duplicated values.
Thanks!
Use tempdb
Go
if object_ID ('#test') is not null drop table #test
create table #test (col1 int, col2 int)
insert into #test
Select 123, 222 union
Select 124, 222 union
Select 125, 222 union
Select 111, 223 union
Select 111, 224
if object_ID ('#test2') is not null drop table #test2
create table #test2 (col1 int, col2 int)
Insert into #test2
Select distinct col1, min(col2) from #test group by col1
if object_ID ('#test3') is not null drop table #test3
create table #test3 (col1 int, col2 int)
Insert into #test3
Select min(col1), col2 from #test2 group by col2
Select * from #test3
I am attempting to execute the Stored Procedure at the foot of thismessage. The Stored Procedure runs correctly about 1550 times, butreceive the following error three times:Server: Msg 512, Level 16, State 1, Procedure BackFillNetworkHours,Line 68Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I've done some digging, and the error message is moderatelyself-explanatory.The problem is that there is no Line 68 in the Stored Procedure. It'sthe comment line:-- Need to find out how many hours the employee is scheduled etc.Also, there are no duplicate records in the Employee table nor theWeeklyProfile table. At least I assume so - if the following SQL todetect duplicates is correct!SELECT E.*FROMEmployee Ejoin(select EmployeeIDfromEmployeeGroup by EmployeeIDhaving count(*) > 1) as E2On(E.EmployeeID = E2.EmployeeID)SELECTW.*FROMWeekProfile Wjoin(SelectWeekProfileIDFROMWeekProfileGROUP BYEmployeeID, MondayHours, WeekProfileIDHAVING COUNT(*) > 1) AS W2ONW.WeekProfileID = W2.WeekProfileIDNOTE: In the second statement, I have tried for MondayHours thruFridayHours.Anyone got any ideas? The TableDefs are set up in this thread:<http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/fff4ef21e9964ab8/f5ce136923ebffc3?q=teddysnips&rnum=1&hl=en#f5ce136923ebffc3>The Stored Procedure that causes the error is here:--************************************************** ***********CREATE PROCEDURE BackFillNetworkHoursASDECLARE @EmployeeID intDECLARE @TimesheetDate DateTimeDECLARE @NumMinutes intDECLARE @NetworkCode int-- Get the WorkID corresponding to Project Code 2002SELECT@NetworkCode = WorkIDFROM[Work]WHERE(WorkCode = '2002')-- Open a cursor on a SELECT for all Network Support Employees whereany single workday comprises fewer than 7.5 hoursDECLARE TooFewHours CURSOR FORSELECTEmployeeID,CONVERT(CHAR(8), Start, 112) AS TimesheetDate,SUM(NumMins) AS TotalMinsFROM(SELECTTI.EmployeeID,W.WorkCode,TI.Start AS Start,SUM(TI.DurationMins) AS NumMinsFROMTimesheetItem TI LEFT JOIN[Work] W ON TI.WorkID = W.WorkIDWHERE EXISTS(SELECT*FROMEmployee EWHERE((TI.EmployeeID = E.EmployeeID) AND(E.DepartmentID = 2)))GROUP BY TI.EmployeeID, TI.Start, W.WorkCode) AS xGROUP BYEmployeeID,CONVERT(char(8), Start, 112)HAVINGSUM(NumMins) < 450ORDER BYEmployeeID,CONVERT(CHAR(8), Start, 112)-- Get the EmployeeID, Date and Number of Minutes from the cursorOPEN TooFewHoursFETCH NEXT FROM TooFewHours INTO @EmployeeID, @TimesheetDate,@NumMinutesWHILE (@@FETCH_STATUS=0)BEGINDECLARE @NewWorkTime datetimeDECLARE @TimesheetString varchar(50)DECLARE @Duration intDECLARE @RequiredDuration int-- Set the correct date to 08:30 - by default the cast from thecursor's select statement is middaySET @TimesheetString = @TimesheetDate + ' 08:30'SET @NewWorkTime = CAST(@TimesheetString AS Datetime)-- Need to find out how many hours the employee is scheduled to workthat day.SET @RequiredDuration = CASE (DATEPART(dw, @NewWorkTime))WHEN 1 THEN(SELECT CAST((60 * SundayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 2 THEN(SELECT CAST((60 * MondayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 3 THEN(SELECT CAST((60 * TuesdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 4 THEN(SELECT CAST((60 * WednesdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 5 THEN(SELECT CAST((60 * ThursdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 6 THEN(SELECT CAST((60 * FridayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 7 THEN(SELECT CAST((60 * SaturdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))ENDIF @NumMinutes < @RequiredDurationBEGIN-- Set the Start for the dummy work block to 08:30 + the number ofminutes the employee has already worked that daySET @NewWorkTime = DateAdd(minute, @NumMinutes, @NewWorkTime)-- Set the duration for the dummy work block to be required durationless the amount they've already workedSET @Duration = @RequiredDuration - @NumMinutes-- Now we have the correct data - insert into table.INSERT INTO TimesheetItem(EmployeeID,Start,DurationMins,WorkID)VALUES(@EmployeeID,@NewWorkTime,@Duration,@NetworkCode)ENDFETCH NEXT FROM TooFewHours INTO @EmployeeID, @TimesheetDate,@NumMinutesENDCLOSE TooFewHoursDEALLOCATE TooFewHoursGO--************************************************** ***********ThanksEdward
View 3 Replies View RelatedI have a table, TEST_TABLE, with 6 columns (COL1, COL2, COL3, COL4,COL5, COL6).... I need to be able to select all columns/rows whereCOL3, COL4, and COL5 are unique....I have tried using DISTINCT and GROUP BY, but both will only allow meto access columns COL3, COL4, and COL5..... i need access to allcolumns...I just want to get rid of duplicate rows (duplicates ofCOL3, COL4, and COL5)...Thanks in advance.Joe
View 1 Replies View RelatedAnyone know how to sum duplicated values so it only sums the distinct values?
View 3 Replies View RelatedI have the following data.
CustID RoleID RelatID StartDate EndDate
20 RIX AGQ 5/05/2007 31/12/9999
20 RIX AGR 10/06/2007 31/12/9999
18 LRS AGQ 3/09/2004 31/12/9999
22 SRT AGP 5/03/2007 31/12/9999
22 SRT AGP 10/03/2007 31/12/9999
I wish to SELECT only the records that have duplicates. So, in the above data example, RoleID of LRS should be excluded.
How to do?