Thursday, December 27, 2012

Report Building Part 4: The SQL Query soo far

So now that we've gone through the absolute basics on building a SQL Query (I left a lot out on purpose, I don't have the patience to write THAT much about creating SQL Queries), lets circle around and get the Query in order.

Original Query from the Interwebs:

SELECT Manufacturer0, Model0, Count(Model0) AS 'Count'
FROM dbo.v_GS_COMPUTER_SYSTEM
GROUP BY Manufacturer0,Model0
ORDER BY Model0


So first thing we will need to do is figure out how to limit this by collection.  As mentioned at the beginning of this series, you will need to familiarize yourself with the SCCM 2007 Database Schema/Diagram, linked in the first article.  In this case, we need to figure out how to get from v_GS_COMPUTER_SYSTEM too v_Collection.  In the diagram the two do not share any common tables, however v_FullCollectionMembership DOES have tables in common with both: ResourceID and CollectionID.  Knowing this, we can create some INNER JOINs to link them all together.  The Query now looks like this:

SELECT Manufacturer0, Model0, COUNT(Model0) AS 'Count'
FROM v_GS_COMPUTER_SYSTEM
INNER JOIN v_FullCollectionMembership ON v_GS_COMPUTER_SYSTEM.ResourceID = v_FullCollectionMembership.ResourceID
INNER JOIN v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID
GROUP BY Manufacturer, Model0
ORDER BY Model0

This only gets us part of the way there, however, as we haven't told SQL what collections we need to query from.  Nor do we have a mechanism to select Collections at will.  This is going to take one more feature of creating SQL Queries: Parameters!

No comments: