|
Hi,
I would like to ask how can I delete duplicate records in MySQL from my fingerprint table.
I need to delete any record if found the same created_date AND created_time AND employee_number
Thanks,
Jassim
Technology News @ www.JassimRahma.com
|
|
|
|
|
Don't use MySQL but in SQl server you would do this:
WITH CTE_fingerprint
AS (
SELECT
employee_number
,created_date
,created_time
,ROW_NUMBER() OVER (
PARTITION BY
employee_number
,created_date
,created_time
ORDER BY
employee_number
,created_date
,created_time
) AS RowNumber
FROM [fingerprint]
)
DELETE
FROM CTE_fingerprint
WHERE RowNumber > 1;
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Not sure with self-joins in MySQL.
This query should show you all the duplicates (assuming ID is the identifier in the table):
SELECT *
FROM fingerprint t1 inner join fingerprint t2 on
t1.created_date=t2.created_date and t1.created_time = t2.created_time and t1.employee_number=t2.employee_number
Where t1.ID<t2.ID
Next, change SELECT * to SELECT t1.ID to get the ID values of the duplicates only, then do a delete query with a subquery:
DELETE
FROM fingerprint
WHERE fingerprint.ID IN
(
SELECT t1.ID
FROM ... (see query above)
)
|
|
|
|
|
Hi,
I'm facing a real challenge in a report I've built with MDX in SSRS.
I have "Keywords" (Dim) column, and "Cost" (Measure) column.
There are a lot of the same keywords (each from different Campaign, which name I preset in the KW tool tip).
I need to make sure that all KWs from the same kind will display one after the other.
The problem is, I need to make sure it's stays in that order even when sorting (interactively) the "Cost" column. basically, the sorting will start again after each group of keywords. And I must display each one of the KWs, not grouping them to 1.
Is it possible? what is the best solution?
Thanks!
|
|
|
|
|
Hi,
There is column for mobile number in my table whose data type is string.
I have to replace six character from middle with star(*) from the string of 10 characters.
For example
The Contact number is 9334459875
I have to display it as follows
93******75 first two characters and last two characters only.
|
|
|
|
|
SELECT LEFT(PhoneNumber, 2) + '******' + RIGHT(PhoneNumber, 2)
FROM AdventureWorks.Person.PersonPhone
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
If SQL Server:
PRINT STUFF( '9334459875' , 3 , 6 , '******' )
|
|
|
|
|
SELECT STUFF('9334459875',3,6,'******')
|
|
|
|
|
|
You change the "order by" clause in the SQL statement.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
I am developing a project ASSET MANAGEMENT SYSTEM in which i am giving the asset Id to every asset . I have my office at different places in INDIA. Every now and then new asset is purchased so I want a perfect record in a database.
My question is that i want a asset number consists of company name then 3 words of city and then number .
for eg: let company name is XYZ it is purchased in city MUMBAI so i want asset id like----> XYZ/MUM/001. on every new entry of a record.
AMRITESH ASTHANA
|
|
|
|
|
You are talking about a real-life identifier. That's not the same as a primary key in a database. A primary key does NEVER, EVER have a constant text in there.
You create a field for you numeric; don't use the PK. Next, add in a FK to the place where it is bought. Then create a calculated field to get the name of the place, and format it as your real-life identifier.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
You could have three columns in your table called
company_name of type varchar(5) (or whatever length you desire)
purchase_city of type varchar(5) (or whatever length you desire)
asset_sn of type int
Make these columns unique so you can't add the same number twice. Index name could be ix_asset_number
When you add a new inventory item you first get the last registered row for the company and city.
SELECT LAST(asset_sn) AS AssetNumber FROM assets WHERE company_name = 'XYZ' AND purchase_city = 'MUM';
Then increment the AssetNumber with 1 and insert the new record.
|
|
|
|
|
Thanks George it works 
|
|
|
|
|
|
In VS I called master.sys.xp_create_subdir @dir to create a folder on a file server, it is successful, but I called my own sp that includes this built-in sp, it is error out and the message is ';Error executing extended stored procedure: Invalid Parameter';, can anyone tell me what the problem is, how should I fix it? Here is the code
create procedure usp_MyProcedure
as
declare @dir varchar(50)
set @dir = '\\servername\folder\subfolder\'
exec master.sys.xp_create_subdir @dir
go
Thank you for your help in advance
|
|
|
|
|
This will be a permissions issue. Your executing sp doors not have permission to use master system procedures
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have employees table with date_of_join field
and I have employee_leaves table with the following fields:
employee_id
leave_from
leave_to
total_days
the employee joined on 15 Feb 2011
I want to have a query showing the cound of leaves for every employee years based on his date_of_join
for example, if the employee joined on 15 Feb 2011 then the result will be like this:
Feb 2011 to feb 2012 ---- totals days: 21
Feb 2012 to feb 2013 ---- totals days: 26
Feb 2013 to feb 2014 ---- totals days: 8
where Feb to feb is the employee year so it's from 15 Feb to 14 Feb every year
can anyone help please?
Technology News @ www.JassimRahma.com
|
|
|
|
|
What have you tried? 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I am not able to get it...
I tried:
SELECT employee_leaves.leave_from, employee_leaves.leave_to FROM employee_leaves
JOIN leave_category ON leave_category.leave_category_id = employee_leaves.leave_category
GROUP BY YEAR(employee_leaves.leave_from);
but this will just group by cal;ander year not employee year
Technology News @ www.JassimRahma.com
|
|
|
|
|
You can only group on fields that are present within the query (or calculations based on one of those fields). You can get get the start of the contract by looking for a MIN-entry. Add 365 days to that fact, that's your range.
That does not account for leap-years of course.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Better add 1 year and not 365 day...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Does it account for me joining the company on the 29th februari?
Edge case from Hell
If you can't read my code, try converting it here[^]
modified 10-Sep-14 16:14pm.
|
|
|
|
|
That does not matter to SQL - it has the requested knowledge to handle the 29th of February...
In fact if you are working with 365 days you have to know when to add 365 or 366 to land on the right date - otherwise you will always get 28 of February.
If you work with year you will move between 28 and 29 as it proper to the year...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
