Generate Academic Calendar using MySQLhttps://www.joellipman.com/articles/database/mysql/generate-academic-calendar-using-mysql.html

27 November 2012 (SQL Server 2008 R2) for CMIS Facility SELECT calendar.WeekNumber AS AcademicWeek, CAST(DATEPART(dd, calendar.StartDate) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, calendar.StartDate), 1, 3) + '-' + CAST(DATEPART(yyyy, calendar.StartDate) AS...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: en-GB

SQL Calendar in Business Intelligence Development Studiohttps://www.joellipman.com/articles/database/t-sql/sql-calendar-in-business-intelligence-development-studio.html

14 January 2011 Date...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

DataScramble - Randomizing data rowshttps://www.joellipman.com/articles/database/t-sql/datascramble-randomizing-data-rows.html

07 December 2012 IS NOT NULL DROP FUNCTION ufn_DataScramble ; GO -- Create user defined function CREATE FUNCTION ufn_DataScramble ( @OrigVal varchar(max) ) RETURNS varchar(max) WITH ENCRYPTION AS BEGIN -- Variables used DECLARE @NewVal varchar(max); DECLARE @OrigLen...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

T-SQL Conversion failed when converting the varchar to data type inthttps://www.joellipman.com/articles/database/t-sql/t-sql-conversion-failed-when-converting-the-varchar-to-data-type-int.html

14 May 2013 case I forget this one. If you are trying to join two tables and receiving the error "Conversion failed when converting the varchar value 'B110' to data type int" then read on. How? So where does the 'B110' string come from, well from one of our tables...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

DataTumble - Randomize Data Rowshttps://www.joellipman.com/articles/database/t-sql/datatumble-randomize-data-rows.html

07 December 2012 data (see my articles on DataJumble and DataScramble). How? CREATE PROCEDURE [Common].[usp_ScrambleMultivalue] ( @TableName VARCHAR(MAX), @ColumnName VARCHAR(MAX), @WhereClause VARCHAR(MAX) = NULL ) AS...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

T-SQL concatenate an incremental row numberhttps://www.joellipman.com/articles/database/t-sql/t-sql-concatenate-an-incremental-row-number.html

24 May 2013 example, Joel Lipman has two qualification records. So let's start with a simple query: SELECT RIGHT('000' + CAST(e.ID AS VARCHAR), 3) AS EmployeeNo, RIGHT('000' + CAST(q.Employee AS VARCHAR), 3) + '01' AS QualificationRef FROM Qualifications q INNER...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

T-SQL functions to convert Strings to Tableshttps://www.joellipman.com/articles/database/t-sql/t-sql-functions-to-convert-strings-to-tables.html

20 August 2014 that just converts a string of words delimited by spaces to a table: CREATE FUNCTION dbo.[ufn_StringToTable] ( @StringInput VARCHAR(MAX) ) RETURNS @OutputTable TABLE ( StringValue VARCHAR(10) ) AS...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

The ReportServer Databasehttps://www.joellipman.com/articles/database/the-reportserver-database.html

08 August 2011 a serious lack of documentation as to what this database is and how it populates its data. View: ExecutionLog InstanceName nvarchar(38) NOT NULL Name of the report server instance that handled the request. Usually YOURSERVERNAME\MSSQLSERVER ReportID...

• Type: Article
• Author: Joel Lipman
• Category: Databases
• Language: en-GB

DataJumble - Shuffling characters in a data valuehttps://www.joellipman.com/articles/database/t-sql/data-shuffling-function.html

01 August 2012 IS NOT NULL DROP FUNCTION ufn_DataJumble ; GO -- Create user defined function CREATE FUNCTION ufn_DataJumble ( @OrigVal varchar(max) ) RETURNS varchar(max) WITH ENCRYPTION AS BEGIN -- Variables used DECLARE @NewVal varchar(max); DECLARE @OrigLen int;...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

Search a database for a string (MySQL, T-SQL)https://www.joellipman.com/articles/database/search-a-database-for-a-string-mysql-t-sql.html

08 October 2012 is expanded in a further article called "Search a database with soundex": /* USING A CURSOR */ DECLARE @SqlToExecute nvarchar(max); DECLARE @mySearchString varchar(50); SET @mySearchString = 'dnya'; DECLARE MyCursor CURSOR FOR SELECT 'SELECT ' +...

• Type: Article
• Author: Joel Lipman
• Category: Databases
• Language: en-GB

Stored Procedure to List Distinct Values and Countshttps://www.joellipman.com/articles/database/t-sql/stored-procedure-to-list-distinct-values-and-counts.html

26 October 2012 DROP PROCEDURE [usp_ListDistinctValuesAndCounts]; GO CREATE PROCEDURE [usp_ListDistinctValuesAndCounts] ( @p_SearchTable VARCHAR(max), @p_SearchColumns VARCHAR(max), @p_UseCollation VARCHAR(max) = 'Latin1_General_CS_AS' ) AS...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

Search a database for a value and count matching rowshttps://www.joellipman.com/articles/database/search-a-database-for-a-value-and-count-matching-rows.html

05 November 2012 [usp_CountRecordsPerTablePerColumn]; GO CREATE PROCEDURE [usp_CountRecordsPerTablePerColumn] ( @p_Value int, @p_Column varchar(max) ) AS /*********************************************************************************** ** Author: Joel Lipman ** **...

• Type: Article
• Author: Joel Lipman
• Category: Databases
• Language: en-GB

Parameters not being used in report processinghttps://www.joellipman.com/articles/microsoft/ssrs/parameters-not-being-used-in-report-processing.html

07 January 2011 =============================================================================== DECLARE @GivenDate datetime, @GivenCampus varchar(100), @setId varchar(10), @weekNumber int, @siteId nvarchar(5), @baseDate datetime, @specifiedDate datetime,...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

Room Availability Calendar in Business Intelligence Development Studiohttps://www.joellipman.com/articles/microsoft/ssrs/room-availability-calendar-in-business-intelligence-development-studio.html

23 March 2011 Status = '2', Tooltip='Some more details' ) SELECT * FROM Times t LEFT OUTER JOIN Events e ON CONVERT(VARCHAR(8) , t.Time, 108) BETWEEN e.EventStart AND DATEADD(minute, -1, e.EventFinish) --Set the maximum times the Dates cte can recurse OPTION...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: *

Search a database with SOUNDEXhttps://www.joellipman.com/articles/database/search-a-database-with-soundex.html

11 October 2012 will generate the full query to your output panel: CREATE PROCEDURE [usp_GenerateSearchbySoundexQuery] ( @p_SearchString VARCHAR(max), @p_TableSchema VARCHAR(max) ) AS...

• Type: Article
• Author: Joel Lipman
• Category: Databases
• Language: en-GB

Compare two databases using T-SQLhttps://www.joellipman.com/articles/database/t-sql/compare-two-databases-using-t-sql.html

24 September 2013 t1.CHARACTER_MAXIMUM_LENGTH IS NULL AND t2.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CAST(t2.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ' (new)' WHEN t1.CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND t2.CHARACTER_MAXIMUM_LENGTH IS NULL THEN...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Convert to Proper Case in T-SQLhttps://www.joellipman.com/articles/database/t-sql/convert-to-proper-case-in-t-sql.html

04 June 2014 it is to deal with exceptions to the rule, words that you want in a specific case. CREATE FUNCTION ufn_ProperCase(@Text AS VARCHAR(8000)) RETURNS VARCHAR(8000) AS BEGIN -- declare some variables DECLARE @Reset BIT; DECLARE @Ret VARCHAR(8000); DECLARE @i...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Copy a table with structure and data into a temporary tablehttps://www.joellipman.com/articles/database/t-sql/copy-a-table-with-structure-and-data-into-a-temporary-table.html

03 August 2012 PROCEDURE dbo.usp_MakeTableTemp; GO -- Create Stored Procedure CREATE PROCEDURE dbo.usp_MakeTableTemp @SchemaTableName nvarchar(100) AS BEGIN -- Variables used DECLARE @ColName varchar(50); DECLARE @TableName varchar(50); DECLARE @TableDeclaration...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

Reorder Columns in a Tablehttps://www.joellipman.com/articles/database/reorder-columns-in-a-table.html

22 July 2013 DEFAULT b'0' AFTER other_column; -- OR A MORE COMPLETE EXAMPLE -- ALTER TABLE table_name MODIFY COLUMN misplaced_column VARCHAR(13) NOT NULL DEFAULT 'DefaultString' COMMENT 'A Comment' AFTER other_column; Alternatively Export the file as a SQL file...

• Type: Article
• Author: Joel Lipman
• Category: Databases
• Language: *

Split a row into multiple rows based on a column valuehttps://www.joellipman.com/articles/database/t-sql/split-a-row-into-multiple-rows-based-on-a-column-value.html

23 August 2013 based on the value of the column: SELECT EmployeeNo , DaysOffSick , DateOfSickness ,'1' + substring(CAST(DaysOffSick AS VARCHAR(10)), 2, 1000) AS Items FROM Employees_Attendance_Table JOIN master..spt_values n ON n.type = 'P' AND n.number...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *