SQL queries and sharepoint designer data view web parts

As an exercise, I wanted to create a data view web part that showed the number of certifications earned per employee in the year 2008. I used a data connection to our sql database and found that the best place to start such a thing, was to get a SQL query put together that performed what I needed.

To achieve what I wanted, I had to create two custom views in my existing SQL database. Below is the example of my code and what it does:

CREATE view [Techcert_view] AS
Select Tech.Name,Techskil.Type_code, Techskil.Auth_date from Tech, Techskil
where
Tech.Technician = Techskil.Technician
and Auth_date >= '2008-01-01'


The block above creates a new custom view called Techcert_view.It selects 3 columns from two separate tables (Tech, Techskil) in the main database.The Where clause was necessary because one table showed the technician's name, and the technician's ID, whereas the other table only contained the technician ID. I wanted my output to contain the full technician's name, so I had to correlate the two values. The final statement indicates I want values only since 2008.The output looked like this:

Name Type_code Auth_date
-------------------------------- --------- ---------------------------
SMITH, JOHN VCP 2008-02-18 00:00:00.000
SMITH, JOHN FIRETIDE 2008-05-06 00:00:00.000
FRANK, JANICE 272 2008-05-23 00:00:00.000
FRANK, JANICE DELL-OD 2008-07-20 00:00:00.000

...

This is great, but what I really wanted was a count of certs per technician during this period. Right or wrong (my sql skills are lacking), I created another custom view:

CREATE VIEW [TechcertCount_view] AS
select Name, count(Distinct Type_code) AS Certification_count from [Techcert_view]
group by Name

That now gives an output of:
Name Certification_count
-------------------------------- -------------------
SMITH, JOHN 2
FRANK, JANICE 8
HUBER, MARK 5
BRANDT, SEAN 15

I then made my data view web connection in Sharepoint Designer, and I selected the TechcertCount_View (you can add tables and/or views via SPDesigner), and sorted by the Certification_count, so that it shows who received the most certifications.