Wednesday, June 12, 2013

[SQL Server] How to delete duplicates/triplicates etc. except for the latest record based on date?

[SQL Server] How to delete duplicates/triplicates etc. except for the latest record based on date?

How to delete duplicates/triplicates etc. except for the latest record based on date?

Posted: 12 Jun 2013 11:24 AM PDT

Hi,I have a table (t_account_code) with the following three columns - AccountCode, StartDate and EndDate. The table does not have an ID column (I know - it was inherited) and at this point, modifying the table is not an option, so please bear with me. With the combination of three columns, we get a PK.This table has multiple entries for the same AccountCode, and I want to delete all duplicate/triplicate/multiple entries EXCEPT for the latest one, based on StartDate.First, I've created a temp table with all account codes (unique) and their count, if greater than 1.I've tried the following query, first to do a select on the accounts that occur twice:select * from t_account_code tac1where AccountCode in (select AccountCode from #Tmp_accountcodes where count_num = 2)and StartDate = (select MIN(StartDate) from t_account_code tac2 where tac2.AccountCode = tac1.AccountCode)The distinct values of count_num are 2, 3 and 4.This works as intended and gives me the results I want (for duplicate values), but when I change it to a delete statement, I get a syntax error in the first row for the table alias - tac1. Of course, if I delete it, then my query won't work as I'm using tac1 in the sub-query.Any solution to this problem will be appreciated, even if it's a different query than the one above. I want to end up with unique AccountCodes in the table, with the latest StartDate, and delete any multiple ones with the older StartDate.E.g.AccountCode ---- StartDate ---- EndDate1234------------ 12/31/2012 ---- 3/30/20131234------------ 3/31/2013 ------4/29/20131234------------ 4/30/2013 -----12/31/20149876------------12/31/2012------3/30/20139876------------3/31/2013 -------5/30/2014I want to delete the first two rows for AccountCode '1234' and delete the first row for AccountCode '9876.' If an AccountCode occurs only once, then I want to leave it alone.Thanks.

Questions on resources and removing instances

Posted: 12 Jun 2013 04:21 AM PDT

Hi there,I've been working with SQL Databases for over 5 years now. Prior to this job, I has no working knowledge of SQL in general, so everything I've learned, I did by trial and error. I work for an accounting software company and last summer, we've introduced a SaaS solution to allow our customers (who subscribed to this service) to use the software on a remote virtual machine using an RDP connexion. To concept is as follow, we have 2 virtual machine for the software (a mid version and a high version of the software) as well as dedicated virtual machines for SQL servers. We're up to our 3rd SQL server now since we've respected the 50 instances limit rule for stand-alone SQL server. Each instance belongs to a customer with his security group in it to prevent the other customers to see his databases.So this is our basic setup but we've ran into a few issues that I will try to address with the best of my experiences:1- Removing Dead InstancesSaaS is not for everyone and a small percentage of our customers have realised that their Internet connexion is not stable enough to run this, so we've put them back to a local usage with the software. That being said, we're now stuck with SQL instances that are no longer active and that we can't reuses since they were named using the customer licence codes, so I've checked my options:a) Renaming the instances: I found multiple sites stating that trying to rename instances is not a great idea, can anyone here confirm this? If this is false, how would you go about renaming one?b) Removing the instances: Although this seems to work on servers with only a few instances, our first 2 SQL servers which are now full are not cooperating with the uninstallation. The uninstall process can be left running for over 2 hours and nothing happens, doesn't seem to be uninstalling anything (no error messages). Any ideas why it's doing that and is there another (safe) way to remove an SQL instance in Windows 2008 without affecting the working ones?c) Leaving them off: Disabling them and turning them off will free the resources but I am left with a question, this 50 instance rule, does it include inactive instances? If I got 45 active instances and 5 inactive ones, can I create 5 more without breaking anything?2- Managing ResourcesIf there's one thing I've learned quickly about SQL is that it loves RAM, there never seems to be enough of it. I've read that it consumes everything that it can to put in a pool for future usage. Now that's all great, but when you have 50 instances running, they all seem to be competing for resources. Now I had the idea of perhaps limiting the amount of RAM that an instance can use based on the number of users in an instance (we have customers with more than 1 user) and the the total size of the database(s). Not too much info I could find online regarding this, so does anyone here care to share their opinion on that strategy? If it's a good idea, what's the magic formula I should use to calculate the amount of RAM I should limit the instances based on the number of users and total size of their database(s)?We currently have 16GB of RAM per SQL server, will this be enough for this strategy?If this is plainly a bad idea, what would you recommend that would allow enough resources for all of the instances?Thank you for your time

SQL to split row by date (split into multiple rows)

Posted: 11 Jun 2013 08:42 PM PDT

I am looking for help with splitting a row into multiple rows based on dates overlapping.As an example, I have a table with the following data:[b]Row ID, Employee, Job, Start Date, End Date, Workload[/b]1, John Doe, HSBC, 01/01/2013, 31/12/2013, 1002, John Doe, Vacation, 17/06/2013, 21/06/2013, 1003, John Doe, Vacation, 19/08/2013, 23/08/2013, 1004, John Doe, Barclays, 01/01/2014, 31/01/2014, 505, John Doe, Santander, 06/01/2014, 25/01/2014, 506, John Doe, Vacation, 13/01/2014, 17/01/2014, 100I am looking to split the banking rows where they overlap with a vacation. So for example, the final result should be:[b]Row ID, Employee, Job, Start Date, End Date, Workload[/b]1, John Doe, HSBC, 01/01/2013, 16/06/2013, 1002, John Doe, Vacation, 17/06/2013, 21/06/2013, 100[b]3, John Doe, HSBC, 22/06/2013, 18/08/2013, 100[/b]4, John Doe, Vacation, 19/08/2013, 23/08/2013, 100[b]5, John Doe, HSBC, 24/08/2013, 31/12/2013, 100[/b]6, John Doe, Barclays, 01/01/2014, 12/01/2014, 50[b]7, John Doe, Barclays, 18/01/2014, 31/01/2014, 50[/b]8, John Doe, Santander, 06/01/2014, 12/01/2014, 50[b]9, John Doe, Santander, 18/01/2014, 25/01/2014, 50[/b]10, John Doe, Vacation, 13/01/2014, 17/01/2014, 100New rows after split are in bold. The Row ID should be unique although it doesn't need to be sequential. Any help or guidance would be appreciated.

displaying Columns through select

Posted: 12 Jun 2013 04:26 AM PDT

I have three tables A ID, A.name, A.LastNameB ID, B.Name,B.someothernameC ID, C.why,C.whyalsoall connected by ID'si have a query as below to get the dataselect A.Name,B.name,C.Why where A.ID = B.ID and C.ID = A.IDhowever i need the select in a way that if there are null values we show them as A.name is null or b.name is null i.e if A.name is null select A.Name as "A'S is NULL"how do i implement this logic in a select statement thanks

Policy Management

Posted: 12 Jun 2013 12:19 AM PDT

Hi,I have not created the job syspolicy_purge_history, it has been created by default in sql server 2008.Can anyone please tell me what is syspolicy_purge_history? and Is there any effect , if I disable the job, sorry I am new in sql server 2008.Thanks in advance

Need Help with the Error 'Subquery returned more than 1 value'.

Posted: 11 Apr 2012 09:57 AM PDT

[quote][/quote]
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog