Grand Totals and Subtotals in SQL Server

Just one of those things you may not know existed - the ROLLUP clause. It's very useful for totaling. Are you used to doing something like this? SELECT customer, sum(sales) from table UNION SELECT 'Total', sum(sales) from table The problem here is that on larger datasets, this can really be a pain in terms of performance. ROLLUP can be extremely quick, but the syntax may not be straight forward. Here's an example of summing sales in AdventureWorks2017 with a Grand Total. USE AdventureWorks2017 SELECT SalesOrderID, sum(LineTotal) FROM Sales.SalesOrderDetail where SalesOrderID in ( '43659', '43660', '43661', '43662', '43663' ) GROUP BY SalesOrderID WITH ROLLUP Notice I've added a WITH ROLLUP at the end of my GROUP BY. This creates the grand total. However, I'm left with a NULL for the SalesOrderID. To solve this I wouldn't want to use a coalesce, because a NULL might be valid elsewhere. Instead we should use the GROUPING function. USE AdventureWorks2017 SELECT CASE WHEN GROUPING(SalesOrderID) = 1 THEN 'Total' ELSE cast(SalesOrderID as nvarchar(10)) END as SalesOrderID, sum(LineTotal) FROM Sales.SalesOrderDetail where SalesOrderID in ( '43659', '43660', '43661', '43662', '43663' ) GROUP BY SalesOrderID WITH ROLLUP You can extend this even further...
Read More

Search all tables and views for column name in SQL Server

A nifty little bit of code I've used for years and years, very useful if you don't have a third party add-on installed... USE AdventureWorks2017 SELECT * FROM ( SELECT t.name AS name, 'table' as type, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE '%Employee%' UNION SELECT v.name AS name, 'view' as type, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.views AS v INNER JOIN sys.columns c ON v.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE '%Employee%' ) a ORDER BY schema_name, name ...
Read More

Error Catching – Stored Procedure inside of SSIS

One fun thing I found out about SSIS is that is doesn't always error out a package if you execute stored procedures as SQL tasks instead of utilizing the SQL itself inside of the package. In other words - it may produce an error if you were to execute it in SSMS, but if you execute within SSIS it'll fly by successfully without you knowing anything went wrong. The best little trick I've found is to just surround the entire procedure with a Try/Catch block. This seems to raise the error up to SSIS and fails the package for you. CREATE PROCEDURE usp_TestProc as BEGIN TRY --SQL goes here... END TRY BEGIN CATCH PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) + ' at line ' + CONVERT(varchar(50), ERROR_LINE()) + ': ' + ERROR_MESSAGE(); END CATCH; GO ...
Read More

Refresh all views in SQL Server database

One problem I've had recently is a SQL Server instance in which the views were constantly cached and needed to be refreshed. Normally to do this you would run the build in procedure: exec sp_refreshview MyView However - I had the need to run this on all my views daily, and we constantly had new views. You can use the stored proc below to simply loop all the views and run as needed. create procedure usp_refreshview as declare @sqlcmd nvarchar(max) = '' select @sqlcmd = @sqlcmd + 'exec sp_refreshview ' + '''' + name + '''; ' from sys.objects as so where so.type = 'V' print @sqlcmd if len(@sqlcmd) > 0 exec(@sqlcmd) To run, simply exec: exec usp_refreshview ...
Read More

Rows to Comma Separated Lists in SQL Server

A useful bit of syntax in the case that you end up with multiple levels of granularity in a dataset, and you want to collapse it back down. One solution would be to turn your rows into columns (pivoting), or put your row values into a single column separated by commas (or pipes, etc.). Below I'm using the AdventureWorks2017 database to show how to do the latter. SELECT [CountryRegionCode] ,StateProvinceCode FROM [AdventureWorks2017].[Person].[StateProvince] Here it produces a gigantic list of countries with associated state/provinces. So let's collapse that down... SELECT outerQ.[CountryRegionCode], STUFF(( SELECT DISTINCT ',' + subQ.StateProvinceCode FROM [AdventureWorks2017].[Person].[StateProvince] subQ WHERE outerQ.CountryRegionCode = subQ.CountryRegionCode FOR XML PATH('') ), 1, 1, '' ) as LastNameList FROM [AdventureWorks2017].[Person].[StateProvince] outerQ GROUP BY outerQ.[CountryRegionCode]; ...
Read More

Converting all blanks to NULL in a SQL table

The bane of my existence, blank values that should be NULL in a database. This happens frequently when importing Excel/Flat files particularly within SQL Server. Running hundreds of UPDATE statements isn't fun, so I use a Stored Procedure with a CURSOR to take care of it. CREATE PROCEDURE [dbo].[usp_getRidOfTableBlanks](@tableName nvarchar(50)) AS DECLARE @colName varchar(255) DECLARE @sql varchar(4000) DECLARE Table_Cursor CURSOR FOR select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tableName OPEN Table_Cursor; FETCH NEXT FROM Table_Cursor INTO @colName WHILE @@FETCH_STATUS = 0 BEGIN set @sql = 'UPDATE ' + @tableName + ' SET [' + @colName + '] = NULL WHERE [' + @colName + '] = '''';'; ...
Read More

Integrate Tableau and SSRS – A complete tutorial

One of the most difficult part of Business Intelligence is finding the right tool for the right situation. Too often folks will discover Tableau and use it for every single bit of reporting they need, including data that used to be in Excel. The problem is, they will utilize Tableau as if it was a glorified Excel replacement and try to mimic Excels features. Unfortunately if there is one thing Tableau does horrible, it is display large amounts of raw data. Tableau is great at visualizations, which is the wheelhouse it should stay in. However - there is often a legitimate need to not only get at the raw data, but display it in a "prettier" format. Viewing data within Tableau isn't a great experience. Often it's littered with calculations you don't want to see, and users want you to organize the raw data (maybe add some nice headers). None of this is really possible or practical within Tableau. But -...
Read More

SQL Server integration with Python

Something very important to me in particular as I utilize SQL Server quite a bit. Utilizing pyodbc and Pandas you can bring your data in. Required Libraries conda install pyodbc #Anaconda installation #pip install pyodbc #Alternative Here we can try altering to read from the AdventureWorks2017 database in SQL Server, as an example (replacing your server_name): import pyodbc import pandas as pd conn = pyodbc.connect('Driver={SQL Server};' 'Server=server_name;' 'Database=AdventureWorks2017;' 'Trusted_Connection=yes;') sql_query = pd.read_sql_query('SELECT * FROM Person.person',conn) sql_query.head() Notice this gets thrown out as a dataframe: Similarly - we could also write back to SQL Server if we wanted to utilizing slightly altered logic (abbreviated insert given how many...
Read More

Temp Tables within Tableau (Initial SQL)

Did you know you can create/use temp tables within Tableau? It's one of those features that hasn't always existed, is very useful, and most people do not realize exists. Utilizing SQL Server as an example - you can make use of Initial SQL. From here you can type out your SQL to generate temp tables. Then, in your custom SQL you can reference that temp table. (I realize This can then be published on the Tableau server and works as normal. The temp table will drop when the user session ends. This is very beneficial for smaller datasets that are performant in which you don't want to go through the pain of creating a stored procedure. However - the downside is that the Initial SQL code can be hidden from anyone else trying to develop on the dashboard. Any yes - you can in fact utilize variables as you would in stored procedures within Initial SQL, good article here on how...
Read More