Hey guys, I have a table full of data that has duplicate records except for two date columns (date1 and date2). What I would like to do is remove the duplicates while retaining the most recent record, how can I do this?
So record 1 looks like this:
Code:
John | Smith | 08/08/2000 | 10/10/2000
Record 2 looks like this:
Code:
John | Smith | 08/10/2005 | 10/10/2005
I'd like to remove the first instance and keep the second (most recent one).
Hi All, I am writing a SP where I need to pass an value to maintain records of last n days. In this SP I am deleting a couple of tables based on the value passed to this SP. For e.g. If the SP is passed the value 10, then only TOP 10 records is maintained, the rest are deleted. I have formed the following logic, which I feel can be improved vastly. I create a temp table and
CREATE TABLE #TempAuditTbl (Rownum int PRIMARY KEY, Orderid uniqueidentifier)
INSERT INTO #TempAuditTbl
SELECT ROW_NUMBER() OVER (ORDER BY orderdate desc) AS rownum, Orderid FROM Orders
DELETE Orders FROM Orders INNER JOIN #TempAuditTbl adt ON adt.Orderid = Orders.Orderid AND rownum > @TopnRows
DROP TABLE #TempAuditTbl
OR
DELETE FROM Orders WHERE orderid NOT IN ( SELECT TOP @TopnRows OrderID FROM Orders ORDER BY OrderDate desc)
This way I am able to keep the top n records. Which of these two solutions is more efficient? Is there a more efficient way to achieve the same. Please help.
I have tested the code below to remove duplicate table entries based on the field IntOOS. This code works; however, I want to ensure I am removing the oldest entries and keeping the most recent entry. The field to key on this would be something like Max(ID). What would be the best way to ensure I keep the most recent table entry?
/*** Removes duplicate rows from ampfm.rpt_AdtVisitDiag table
by Chuck King 20070928
***/
;WITH CTE
as
(
SELECT
ROW_NUMBER() OVER (Partition By IntOOS Order BY IntOOS) AS ROWID
i have a datetime field in the post tables. I would like to get the records within the latest 7 days. Are there any functions for doing something like this? my current query is something like select * from post where creation_time .... Thank you
Ok, I was able to find the most recent Effective date and it works well. The only problem I am having is with the CarrierName. See there are different Carriers with so then it makes us have Mult effective dates. I need to pull the most recent effective date with carrier. So, I though to max the pcsiNumber2, but that didn’t work. Do you have any other ideas? Let me know.
Select Distinct --PCSNumber, PCSINumber1, max(PCSINumber2) as mostrecentPCS, LastName, FirstName, State, --InsuranceCarrier, CarrierName, MAX(CoverageEffectiveDate)as MostRecentEff, MAX(TerminationDate) as MostRecentTerm, AmtPerClaim, Aggreg
FROM VW_Medical_Malpractice_Project
Where State = 'PA'
Group By PCSINumber1, LastName,FirstName,State,CarrierName,AmtPerClaim,Aggreg
Order By PCSINumber1
Example
000043010003MUSSER WILLIAM American Professional Agency2002-06-01
000043010002MUSSERWILLIAM Columbia Casualty2000-02-20
So, what I need is the most recent effective date which would be 2007-01-01, which I have but what is not letting me do this is the names, the 5ith column. I was trying to use the 2nd column to use the max , but it is not working. What am I doing wrong. I need the whole last row, but that number 0005 can change because there may only be 2 dup records.
If I have tables like Customer (Id, First, Last, Address, etc.), CustomerEvent (EventCode, EventDate, CustomerId), EventTypes(Code, Description) how would I get all of the CustomerIds where the latest CustomerEvent record is a cancel and it happened within the last 60 days?
If I just do a select max(EventDate) and put EventTypes.Code='cancel' in the where clause it picks up records where the customer has done other things since the cancel.
create table dbo.customer ( customer_id int identity primary key clustered, customer_name nvarchar(256) not null ) create table dbo.purchase_order ( purchase_order_id int identity primary key clustered customer_id int not null, amount money not null, order_date date not null )
Implement a query for the report that will provide the following information: for each customer output at most 5 different dates which contain abnormally high or low amounts (bigger or less than 3 times SDTDEV from AVG), for each of these dates output minimum and maximum amounts as well.
Need to remove the duplicated rows from a table which has text/ntext/image type columns. The table does not have any PK/Unique column. (I accept its a bad data model). But currently changing the data model is not possible. Hence doing changes in application.
I couldn't do 'SELECT DISTINCT * from table', since the table has text columns. Though there is no PK constraint, If I know that col1 and col2 are join PKs in the table, Is that possible to select the distinct rows from such a table.
I want the output like this,it should take only Min Datecreated FormCodeRefCodeSerialnoDateTime R1-196H1-68A12232138/6/2007 19:38:14 R1-205H1-67XS23124148/6/2007 19:36:08 R1-220H1-66F433365348/6/2007 19:30:27 R1-400H1-64ER53436648/6/2007 19:24:23 R1-408H1-65TE4626268/6/2007 19:24:23
1 7530568 87143 OESCHD 1/5/2006 6:31:58 AM 1 7530568 87143 OESCHD 1/5/2006 7:02:36 AM
for each 7530568 ordernumber there should only be one OESCHD status.
This is the query I'm using to insert the data sent to me.
INSERT INTO ORDER_EVENTS SELECT d.division as division, dt.orderNum as orderNum, dt.poNum as poNum, dt.statusCode as statusCode, dt.statusChangeDate as statusChangeDate FROM dt_Order_Events dt INNER JOIN division d ON dt.division = d.divisionShort INNER JOIN status s ON s.division = d.division AND s.statCode = dt.statusCode WHERE directive <> 'C' AND dt.orderNum IN (SELECT orderNum FROM ORDER_HEADER)
This works fine when used with in the hourly transactional update. But When I ran it for the Bulk UpDate (so we'd have historical data) it allowed orders to have statuses to many times.
I am not a SQL guru, I have no idea how to write a sql statement or stored proc that will remove the duplicate records. or how to change what I have to prevent further ones.
I have a two tables containing customers invoices, one for the invoices header (ie: customer #, invoice date,... KEY: invoice # + invoice date) and another for the details of the invoices (ie: each invoice line details KEY: invoice # + line #). I need to periodically remove invoices older than a certain timeframe (ex: all invoices older than 48 months).
I have regular work that requires me to extract a bunch of customer records from our database, and then remove duplicate address destinations (so we dont mail the same address more than once).
I can currently achieve this using a combination of my poor SQL skills and Excel, but it's really not working out for me, so looking for SQL wizardry necessary to do it just in SQL.
Relevant fields: Member.AddressBarcode (This is a unique barcode (Text representation of a base-3 number) based on the customer address. So if there's more than one record in the pulled records with the same barcode, we then look at Member.MemberTypeID to determine whether to include this record in the results or discard it as a duplicate. Note that AddressBarcode may be blank if the mailing address couldn't be validated, if it is blank we don't discard it since there is no easy way to detect duplicate addresses without the barcode)
Member.MemberTypeID (This is the type of member account. We have 3 types - Single, Joint Primary, Joint Secondary, represented in this field by the numbers 1/2/3. This is also the order of preference of who to mail. So if there is a Joint Primary and Joint Secondary with the same mailing barcode, we want to discard the Joint Secondary from the results, so that the Joint Primary is the record we include in the results of who to mail.)
Member.ID (Unique numeric ID for each customer. Kind of irrelevant here, but it's a key)
So some pseudo code for what I'm trying to achieve is:
(Member.MemberTypeID = 1) OR (Member.MemberTypeID = 2 AND Member.AddressBarcode not in results of Member.MemberTypeID = 1) OR (Member.MemberTypeID = 3 AND Member.AddressBarcode not in results of Member.MemberTypeID = 2 AND Member.AddressBarcode not in results of Member.MemberTypeID = 1)
hi All, I have to remove non-useful and duplicate records containing NULL , Blanks and extra spaces (either on left or right side of the column values) etc from all the tables in my server's DB XYZ weekly thru a a scheduled job with the help of a Stored Proc, that s i guess called Purging og DB. Plz help how i can do it with T-SQL.
Also i have to find out and remove all the duplicate DB objects(tables) from the DB .e.g. a table existing with name TABLE_TEST or TABLE_DEBUG etc for an original table TABLE , making sure no any of the base table is dropped.
I wanted to remove duplicate records from SSRS report. I set the "Hide Duplicates" to True. It is now working, But i am getting the space between the two records, which i want to get rid of. How to get rid of extra spaces between two records ( Please find the details below).
I need to get all customer records with the most recent tDate. A customer should never have duplicate tDate records, as only one record per day is allowed per customer, yet there somehow there are duplicates. Given this, I need to get the record with the Max date and Max ID. the ID column is an auto-incrementing Identity column.Below is the code without the Max ID column logic
SELECT tCustID, MAX(tDate) AS tDate--get MAX tDate records FROM table1 GROUP BY tCustID
Have a table that list item#, date the standard cost went into effect and the standard cost. How do I find the difference in StdCost on the last EffectiveDate and second to last EffectiveDate. 5.59 (01/05/2015) minus 5.81 (09/29/.014) = -.22.
Item#     EffectiveDate   StdCost
1152Â Â Â Â Â Â Â 01/01/2009Â Â Â Â Â 5.50 1152Â Â Â Â Â Â Â 09/29/2014Â Â Â Â Â 5.81 1152Â Â Â Â Â Â Â 04/04/2011Â Â Â Â Â 5.56
For my db connection managers the password is not being saved. I have 'remember password' checked for each connection manager, but when I close down the BIDS and open it again, I have to re-enter the password again. Even when I import the dtsx file into SQL IS, its not in the connection manager information. I have to re-enter the password again every time prior to running it,.
What is causing this? I don't want to have to enter in the password everytime I run it because the jobs are going to be scheduled and no user interaction unless one fails.
Hello, I need some help to solve an issue we have with SSIS, perhaps someone could help us.
We need to extract data from a remote database. We would like to use SSIS to extract data from that DB. But our actions are very restricted in that DB; we can query the DB views and create (local, global) temp tables only.
So, we created an SSIS package with two SQL execution tasks. In the first task, we create two local temp tables and insert some results into them. In our second task, we need to access the values stored into the temp tables, so we have the two tasks connected and we set the RetainSameConnection to true.
But when we execute, the second task always fails reporting €œinvalid object name€? (referring to the temp table names). It looks like each task is using a different DB, making impossible for us to use the temp tables we created in the first task. We tried both ADO.net and OLEDB without much luck so far.
Any ideas how to solve this problem? Any hints and recommendations are much appreciated.
As a side note, we are trying to avoid global tables because our test team will need to test our code and if the code is run by both SSIS and our test team at the same time, then we€™ll have problems. Unless we synchronize access to the tables, or pass some sort of caller identifier to prefix or suffix each temp table name with that caller identifier. No option looks very elegant so far, but anyway.
So, do you know if just one database connection could be used for the whole flow (or not)? And if yes, how?
Is is possible to upgrade from 6.5 to 7.0 and have all the logins that have been granted the ability to make a trusted connection to 6.5 be created the same capability in 7.0?
When I did it the logins were created as standard logins in 7.0
I have a table employee_test having the sample data. The rows with EmployeeID=6 are duplicate rows. I want to delete the duplicates retaining one row for the employeeid=6. Note :- I don't want to use a temporary table. I want to do this using a single query or at the most in a SP query batch. Please advise.
I'm designing a database to store information about jobs that are in progress at a property. More than one job can be in progress at a property at one time and each different kind of job can contain different data, although they all share some common fields such as StartDate.
So I have a table that stores the property details PropertyDetails:
*ID PropertyAddress PropertyPostCode
Then I have a table that stores all of the jobs' shared details:
*PropertyID *JobID - These three make up a compound primary key *JobType StartDate EndDate
Then I have individual tables for each of the Jobs, for example BuildingWork:
*JobID BuildingContractor InsuranceCompany
Which works great, and enables me to query all basic job details from one table (JobDetails) rather than multiple tables for every job type.
BUT: I don't know how to enforce the referential integrity of the database. Obviously I can use a constraint to cascade deletes from the PropertyDetails table to the JobDetails table through the PropertyID, but there doesn't appear to then cascade the deletes from the JobDetails table to the individual Job tables as JobDetails has no idea what tables are there.
If I store the relevant individual table name as the JobType in the JobDetails table, could I use a trigger to somehow delete the related record from that table?
I want to retain the formulae defined in the Reporting Services to be retained when I export the report to Excel. I want to know the best possible way to achieve the same.
Scenario: I have a report which has 4 Columns.Column 1 and Column 2 are fetched from Database. Column 3 is empty and Column 4 (Formulae defined using expressions) is computed with the formulae using the previous 3 Columns.
My requirement is that upon rendering of the report, I'll download the report to excel and the end user enters some values in the column 3 and based on the value entered, Column 4 Formula has to be computed. But when I download the Report to Excel, my Formulae expression is not retained.
Kindly let us know if there is any other means of doing this in reporting services itself. Else please suggest an appropriate alternative, either through third party or in any other way.
Hi all, I've developed few reports I'm passing values to few parameters in a report from menu report. when I click on "View Report" button values are changed to default for parameters eventhough I've not changed specifically any values for parameters. thus report is missing few parameters and not able to execute properly.. this error occurs only in web environment.. after publishing reports.. they are working fine in developer suite(Visual Studio)
HiI have been working since 2 days to device a method to export sql tableinto csv format. I have tried using bcp with format option to keep thecolumn names but I'm unable to transfer the file with column names. andalso I'm having problems on columns having decimal data.Can any one suggest me how to automate data transfer(by using SP) andretaining column names.ThanksNoor
I have an SSIS package that uses an FTP connection manager. When running the package in BIDS, it runs fine and maintains the password for the remote ftp site's user account. Once I deploy the package and attempt to run it, it fails with the following error:
Started: 4:06:15 PM Error: 2015-08-27 16:06:20.09   Code: 0xC001602A   Source: Export and FTP New Jobs Connection manager "FTP Connection Manager"   Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed End Error Error: 2015-08-27 16:06:20.09   Code: 0xC002918F   Source: FTP Jobs Listing to Concur FTP Task   Description: Unable to connect to FTP server using "FTP Connection Manager". End Error DTExec: The package execution returned DTSER_FAILURE (1).
I've tried Don't Save Sensitive With Password and that still fails.
Does the FTP connection manager just not retain passwords outside of BIDS?
Hi, I am looking to implement a custom event handler that will also retain the original event arguments (in addition to several custom arguments). Specifically, I am looking to pass custom arguments into a SqlDataSourceStatusEventHandler, but also want to be able to access the Command.Parameters. I have implemented a new Event Arguments class (derived from System.EventArgs), new Event class and delegate, but do not know how to retain the SqlDataSourceEventArgs. I would really appreciate your suggestions! Thanks!
I am using BI Dev Studio for SS2005 in a research (as opposed to a production) environment. Often I want to compare the results of multiple models using the same attributes. If I switch to a different model, the Design view completely resets. Is there any way to retain the same field names with different models in the Design view?
My current workaround is to give my models similar names with AR, DT, CL, LOG, NN suffixes and make global changes in the DMX.
I have consulted the following without finding an answer: http://msdn2.microsoft.com/en-us/library/ms178445.aspx http://msdn2.microsoft.com/en-us/library/ms175642.aspx http://msdn2.microsoft.com/en-us/library/ms175678.aspx http://msdn2.microsoft.com/en-us/library/ms175637.aspx
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.
Log: Reports_Log LogID | ReportID | AccountID | ReportRunDate ---------------------------------------- 9876 | 5 | 1234 | 9/4/2007 10:35:43 AM 9875 | 5 | 987 | 9/4/2007 10:35:43 AM 9874 | 4 | 1234 | 9/4/2007 9:05:43 AM 9873 | 3 | 1234 | 9/4/2007 9:30:17 AM 9872 | 5 | 1234 | 9/3/2007 10:35:43 AM 9871 | 5 | 987 | 9/3/2007 10:35:43 AM 9870 | 4 | 1234 | 9/3/2007 9:05:43 AM 9869 | 3 | 1234 | 9/3/2007 9:30:17 AM 9868 | 5 | 1234 | 9/2/2007 10:35:43 AM 9867 | 5 | 987 | 9/2/2007 10:35:43 AM 9866 | 5 | 5677 | 9/2/2007 10:35:43 AM 9865 | 4 | 1234 | 9/2/2007 9:05:43 AM 9864 | 3 | 1234 | 9/2/2007 9:30:17 AM
so if i wanted to know what report for reportID 5 hasn't run in the past 2 days, it would be accountID of 5677 (last ran on 9/2/2007 10:35:43 AM)
I am not sure where to even start. I am thinking I can grab the latest report ran for all Accounts in the Reports_AccountID table (create temp table and insert most recent log record into that table), and do a date difference between the date and current datetime and select based on datedifference > 2 ?
or would the best way is to do an inner join between the 2 tables for all reports ran in the past 2 days, and then whatever account is not listed in that query, is a report that has not run (do a subquery?)
just trying to think of the best way to do this. any tips/advice would be appreciated
