SQL – Combining Multiple Tables to Create a Roster | Quisitive

​In this example, I am combining the data from three tables.  Two of the tables have different columns and no relation or join. 

I am managing a soccer team.  I have a list of weeks in the soccer season.  And I also have a list of players. In addition, I also have a list of weeks in which each player have confirmed. 















Using Transact SQL, I want to create a roster that combines all of the players and all of the weeks and specifies whether a player has confirmed participation for that week.

-- declare result table









To combine all Players and all Weeks, you simply perform a select without a join.

-- combine all weeks and all players to create roster
















Once you have your roster, you can perform a quick update to add whether the player has confirmed for each of the weeks.

-- perform multiple update for confirmations'







    [email protected]=c.PlayerIdANDr.WeekId=c.WeekId

And here is your roster!  Let’s play some soccer.  : )

A customer of mine was looking for a way to verify that servers with multiple IP Addresses (mostly web severs) were configured to use a specific IP for all default communications. 

SkipAsSource is a Boolean flag which allows adding new IP Addresses that should not be used for outgoing packages unless explicitly set for use by outgoing packets.

This property is not available out-of-the-box in ConfigMgr 2012 R2.  It is also not populated in the root\CIMv2 namespace; however, with a bit of help from MSDN, James Kehr, and WUtils.com I found the WMI Namespace, Class, and Property (root\StandardCIMv\MSFT_NETIPAddress\SkipAsSource)

Below is a step-by-step configuring guide to capture and report SkipAsSource in ConfigMgr 2012.

Enable Inventory

To enable the custom inventory modify the Default Client Settings.

In the ConfigMgr console, navigate to Administration \ Overview \ Client Settings

Right-Click Default Settings and select properties

Select Hardware Inventory, Set Classes, Add, enter root\StandardCIMv2 for the WMI namespace, enable Recursive, and select Connect

Select the MSFT_NetIPAddress class by browsing for it or entering MSFT_NETIPAddress in the inventory class.  Select OK.

Enable / select the following properties: CreationClassName, Name, SystemCreationClassName, SystemName, Caption, Description, EnabledState, InterfaceAlias, IPAddress, IPv4Address, IPv6Adress, SkipAsSource (those in bold are required)

On a SCCM client, run the Machine Policy Retrieval & Evaluation Cycle, wait about 2 minutes, then run a Hardware Inventory Cycle.

View Inventory

Back on the SCCM Console, navigate to Assets and Compliance \ Overview \ Devices

Right-Click the SCCM client used in testing and select Start \ Resource Explorer

If the inventory has completed processing, there will be a new node named MSFT_NetIPAddress which contains the new WMI properties.

Notice the values for Skip As Source, the IP Address, and the Interface Alias.  Great info!

Create a Report

In Microsoft SQL Server Management Studio, connect to the CM_<SiteCode> database and run the query:

select ResourceID, InterfaceAlias0, IPAddress0, Name0 , SkipAsSource0 from CM_LAB.dbo.v_GS_MSFT_NETIPADDRESS

This will show all of the related data in the database and the same query can be used in SQL Server Reporting Services.

Create a new SQL Reporting Service report with the T-SQL above.  I did this the easy way by using another report as a starting point, opening the report in Report Builder, save it as a new name, replace the existing Dataset T-SQL and drag the new columns into the Tablix.

The report file (Computers with IP SkipAsSource Info.rdl) can be found on my public OneDrive.






Thanks, Curtis Petree for the challenge!

Using the DATEDIFF function allows you to easily calculate week days in SQL, because it both removes the time from a date and converts the date into a number for easy mathematical calculations.

Calculating Most Recent Monday

DECLARE @MostRecentMonday DATETIME = DATEDIFF(day, 0, GETDATE() – DATEDIFF(day, 0, GETDATE()) %7)

PRINT @MostRecentMonday

Calculating Previous Sunday


DECLARE @LastSunday DATETIME = DATEADD(day, -1 *(( @CurrentWeekday % 7) – 1), GETDATE())

PRINT @LastSunday

Calculating Previous Monday


DECLARE @LastMonday DATETIME = DATEADD(day, -7 *(( @CurrentWeekday % 7) – 1), GETDATE())

PRINT @LastMonday

More helpful SQL content:

  1. Float vs decimal in SQL 
  2. Cannot resolve the collation conflict
  3. SQL changes not permitted