Before I start driving myself nuts, I'd like to make sure my approach is correct.I want to create a simple job posting board.I have a text boxes for company name, email, job title, and job description, and a submit button.I created a table in my database called "JobPostings", with columns called "CoName", "CoEmail", "JobTitle", and "JobDesc"When someone fills out the fields and clicks submit, it will insert the new records.So,1. Is this the correct approach so far?2. What is the best way to display the job listings? A grid view?3. At submit time, how can I include that day's date?4. How can I get the records in the database to delete after 90 days?Thanks.
I have this query below that I created to do a count, but I don't think this is what I needed.
I need to find the duplicates. Example, if
CLI_ID1 12345 has 4 CLIP records, each CLIP record should have a different CLIP rank. I need to find scenarios where 2 (or more) of the CLIP records have the same CLIP RANK. If there are duplicate CLIP_RANKs within the same CLI_ID,
Select Distinct cli_id1, count(clip_rank) countrank FROM impact.dbo.CLI LEFT JOIN impact.dbo.CLIO ON CLI.CLI_ID1 = CLIO.clio_id1
left join impact.dbo.clip ON cli_id1 = clip_id1 Where (clio_trm = '' or clio_trm = NULL or clio_trm is null) group by cli_id1 order by cli_id1
Hi there, I've a complex stored procedure, which I'm trying to get a list of one or more users based the list of records that are being display. If I mentioned the overall store procedure here it'll turn you off, so I'll put the questions in bitesize chunks. Example would be if Bob and Luke work for Acrm Corp and John for Maxwellarms Ltd, and there was an intimediate table which linked both. With my stored procedure I want to display both names in the same row as Acrm Corp. Is there a way to do this? User tableUserid Username 1 Bob2 Luke3 John Store procedure resultCompanyname Username Acrm Corp NullMaxwellarm Ltd Null Regards,
I have this 40,000,000 rows table... I am trying to clean this 'Contacts' table since I know there are a lot of duplicates.
At first, I wanted to get a count of how many there are.
I need to compare records where these fields are matched:
MATCHED: (email, firstname) but not MATCH: (lastname, phone, mobile). MATCHED: (email, firstname, mobile) But not MATCH: (lastname, phone) MATCHED: (email, firstname, lastname) But not MATCH: (phone, mobile)
We have an helpdesk sytem on SQL 2000. I am trying to show how much calls have been assigned to each parent category plus it's child categories in a single row. The thing is when I run my query it display parent and child categories and each on it's own rows. I do get the call totals for each row, but I would like to add the totals of the rows together and display it on row. The new table must have then 6 rows (because there is 6 parent categories) with the total of all calls for that parent category, as well as it's childs. Example:
parameter # Calls ------------ -------- desktoppp 5 desktopqq 6 {This is what I am getting at this stage} desktop t 4 network r 9 networkgg 10 softwarevv 3
I am creating a report on an asp.net webpage to show how many times a coupon has been used from a set list of items.There are two tables I am using for this query.Table 1 has the list of items or coupons. Table 2 has a column for the item id from Table 1 and a value.
Example: Table 1 id Name 1 Coupon1 2 Coupon2 3 Coupon3
[code]...
My current query will only show coupon1 and coupon2 and the number and value. It will not show coupon2 since it hasn't been used. I would still like to show coupon2 with a number of zero and a value of zero.
Current query is: SELECT Table1.Name, COUNT(Table2.ID) AS CNum, SUM(Table2.Value) AS CValue FROM Table2 JOIN Table1 ON Table2.ID = Table1.ID GROUP BY Table1.Name
We all were new at one point.... any help is appreciated.
Objective:
Combining two 49,000 row tables and remove records where there is only 1 column difference. (keeping the specified column value removing the one with a blank.)
Reason:
I have 2 people going through a list, coding a specific column with a single letter value. They both have different progress on each sheet. Hence I am trying to UNION them and have a result of their combined efforts without duplicates.
My progress/where I'm stuck:
Here is my first query/union:
SELECT * FROM [Eds table] UNION SELECT * FROM [Vickis table];
As shown above, I have unioned these 2 tables and my results removed th obvious whole record duplicates, but since 1 column is different on these, a union without criteria considers them unique.....
an example of duplicates that I must remove are as follows:
I cannot figure out how to write a sql statement to display the multiple records of multiple records. I know that doesn't make much sense so let me explain:
This problem involves two tables. They both reference (FK) data in a third table (tblDepartment).
Here are the fields and some test data in each of the two important tables:
What I am trying to do is display all requests associated with the multiple departments of "user1". In the above example, I would want to see (after a select statement):
We don't see the requests of user3 or user4 when the given request's departmentID is not a department associated with user1.
Hopefully someone out there understands this. I've never had to write a select statement like this before, so I'm having trouble wrapping my mind around it. Thanks for any help!
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,
I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.
I 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?
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
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
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
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)
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
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
I 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
Hello! Just looking for advise on dealing with duplicates in database. I have a contact table that have a bunch of duplicated customer records. My goal is to combine all duplicated records into one record. This involves couple tables:contact,contact history ,calendar. All tables related by common column "accountno". What would be the best approach for this?
