On occasion, I need to produce a report at the end of a production run or for a morning emailed summary. If I had access to SQL Server Enterprise Edition, then the later task of the morning summary may have been accomplished using a subscription. My solution leverages Microsoft.ReportViewer.WinForms.dll. To find out if you have the version referenced by the script, you can run the following PowerShell command. 1 [System.

CTEs can … aid readability of a query be used as an alternative to derived tables (aka nested subqueries) be referenced multiple times be used for recursive queries Here are some use cases. Filtering or Grouping on Windowing Function Here are the top two cities by population for each state in the table. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 DECLARE @CityList TABLE ( City VARCHAR(30) NOT NULL, StateCode CHAR(2) NOT NULL, [Population] INT NOT NULL ); INSERT INTO @CityList (City, StateCode, [Population]) VALUES ('Portland', 'ME', 66363), ('Lewiston', 'ME', 36491), ('Bangor', 'ME', 33011), ('South Portland', 'ME', 25068), ('Manchester', 'NH', 109830), ('Nashua', 'NH', 86704), ('Concord', 'NH', 42733), ('Derry', 'NH', 33109), ('Dover', 'NH', 30168), ('Rochester', 'NH', 29935), ('Merrimack', 'NH', 25494), ('Boston', 'MA', 625087), ('Worcester', 'MA', 181631), ('Springfield', 'MA', 153155), ('Lowell', 'MA', 107584), ('Cambridge', 'MA', 106038), ('New Bedford', 'MA', 95183), ('Brockton', 'MA', 94316), ('Quincy', 'MA', 92909); WITH CityRank AS ( SELECT ROW_NUMBER() OVER(PARTITION BY StateCode ORDER BY [Population] DESC) AS PopulationOrder, City, StateCode, [Population] FROM @CityList ) SELECT City, StateCode, [Population] FROM CityRank WHERE PopulationOrder <= 2 ORDER BY StateCode, PopulationOrder; Nested Aggregate Functions Here it’s using the same numbers as Population above but let’s call it TotalSales for this example, and we will calculate the average sales for a state.

SSRS - Violin Plot A couple years ago, I received a request to build an SSRS report which would display a violin style plot. In this case, close enough would suffice. It didn’t need to implement a violin plot exactly. The purpose was to visualize a set of pressure measurements across a set of stations. Violin plots are similar to box plots, except that they also show the probability density of the data at different values, usually smoothed by a kernel density estimator.

Sometimes I have a SQL Server backup file and I need to find out what version it is so that I know where can I restore the file without having to use trial and error. Let’s assume the backup file in located at c:\mybackup.bak 1 RESTORE HEADERONLY FROM DISK = 'c:\mybackup.bak' https://www.sqlservercentral.com/blogs/determine-database-version-from-a-bak-file

SSRS - 4 US Postcards per page A user asked a question on SQL Server Central about how to build a report to print four double-sided US postcards per page. Question Posed I have to create an SSRS Report that will print as a USPS postcard, on both sides of a sheet of paper The rectangular postcard must follow the dimensions below: 4-1/4 inches high x 5-1/2 inches long The dimensions of the paper they will print on are: