Replies To: Counting and removing duplicates

Re: Counting and removing duplicates

Posted 18 May 2012 - 10:47 AM

It's a little hard for me to understand what you're saying, because you don't really explain what a "gap" is. However , try changing your inner join to a right join. this will return records where employees have no item name, with NULL in the item name field. You can then count all the fields where item name is null.

I have a Insert statment that exstracts data from Qualification and education tables and run checks that insert the data into gap table.

The Gap table is the table that identify the gaps the employee have in his/her job profile. If she is a Developer she need sertain qualifications to be one, as the company evolve she need to update the qualifications and the gap table shows the company witch courses she need or qualifications she need to upgrade her profile.

Now when i run an individual gap analysis the gap table truncate and i get only one Person's info wich is correct.

Re: Counting and removing duplicates

Posted 21 May 2012 - 08:17 AM

Ok, I begin to see what you are up to, but still not quite clear. Is a "gap" a particular education or qualification unit that an employee doesn't have and is required to have for a job that he's interested in?

Also, how do you decide what are "duplicates", and why do you have two different tables, one for education and one for qualification?

Re: Counting and removing duplicates

I was trying to avoid this one. Was hoping someone would let you down easy...

SQL returns result sets. Period. You ask a question, you get an answer. That result set has NO formatting.

You want a report writer. Report writers take result sets and make them pretty. You can apply all kinds for display rules and layout to your heart's content. Don't ask me to recommend one, because they all suck. It looks like you're using MS SQL; just use that one.

That said...

You can customize the result set. MS SQL has temp tables; we'll use those:

Re: Counting and removing duplicates

Posted 22 May 2012 - 12:14 AM

Yes it is and he requires it for job he is interested in.

Education runs with other tables that quilification doesn't so i must have two tables.

Duplicates are determned by their item name, if i run a gap analysis for the whole company (all employees) my table is going to have 200 employees with 500 education and quilification Item names and the company wants to see just all the gaps needed

If duplicates isn't remove the company is going to see they need
Grade 12
Grade 12
Grade10
It diploma
Grade 10

I was trying to avoid this one. Was hoping someone would let you down easy...

SQL returns result sets. Period. You ask a question, you get an answer. That result set has NO formatting.

You want a report writer. Report writers take result sets and make them pretty. You can apply all kinds for display rules and layout to your heart's content. Don't ask me to recommend one, because they all suck. It looks like you're using MS SQL; just use that one.

That said...

You can customize the result set. MS SQL has temp tables; we'll use those:

Re: Counting and removing duplicates

Hmmm... aside from the use of MS SQL style temp tables and "identity" it's basically just straight up SQL. I'd start there. Any SQL book, tutorial, intro should cover the basics.

You should understand the "CRUD" of SQL: INSERT, SELECT, UPDATE, DELETE. Within select, you must understand JOIN and also GROUP BY. Once you have that, a lot of tricks are open to you.

Your single biggest tool for complex stuff is a "subquery." That is, basically, treating the result of one query as if it were a table in another.

There are places where pure SQL just can't do the job. The trick of throwing the result in a temp table and then manipulating it solves this most of the time. For the really ugly, there is are procedural languages, like T-SQL.