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

27 November 2012 12-Jul-2015 28 This query in T-SQL (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) + '-' +...

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

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

07 December 2012 datatype issue) SET @OrigVal = LTRIM(RTRIM(@OrigVal)); -- Set variable default values SET @NewVal = ''; SET @OrigLen = LEN(CAST(@OrigVal AS VARCHAR(max))); SET @CurrLen = @OrigLen; SET @LoopCt = 1; SET @DateVal = ''; -- 1900-01-01 SET @TimeVal = ''; --...

• 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 query. I want the SQL to return just numbers and let the MDX query add the thousand separator. My answer for everything: CAST((TimeEnd - TimeStart) AS TIME) CAST(CONVERT(CHAR, DATEADD(millisecond, [TimeDataRetrieval]+[TimeProcessing]+[TimeRendering],...

• 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 date IF ISDATE(@OrigVal)=1 SET @ColumnTypeIsDate = 1; -- Determine if Given Value is date (ISDATE does not recognize CAST AS DATE) IF @OrigLen=10 AND (LEN(@OrigVal) - LEN(REPLACE(@OrigVal, '-', '')))=2 AND ISNUMERIC(SUBSTRING(@OrigVal, 1, 4))=1 AND...

• 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 In the above 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...

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

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 the rows to multiply 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...

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

T-SQL: Parse an XML valuehttps://www.joellipman.com/articles/database/t-sql/t-sql-parse-an-xml-value.html

27 March 2014 the Gender: We're going to extract the value in between the opening and closing XML tags: -- Using .query() SELECT CAST(CAST(Event_XML AS XML).query('data(/STAFF/GENDER)') AS VARCHAR(10)) AS Gender FROM [dbo].[XML_EVENTS] -- Returns column GENDER for...

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

Before and After, Highs and Lowshttps://www.joellipman.com/articles/microsoft/ssrs/before-and-after-highs-and-lows.html

03 August 2011 Catalog.Name AS ReportName , MIN(ExecutionLogStorage.TimeStart) AS [First] , MAX(ExecutionLogStorage.TimeStart) AS [Last] , CAST(CONVERT(CHAR, DATEADD(millisecond, MAX(DATEDIFF(MILLISECOND, ExecutionLogStorage.TimeStart, ExecutionLogStorage.TimeEnd)),...

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

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

24 September 2013 END AS [ColumnDataType], CASE WHEN 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...

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

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 FETCH NEXT FROM Cursor1 INTO @TableToProcess WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlToExecute = ' SELECT DISTINCT ' + CAST(@p_Value AS VARCHAR) + ', ''' + @TableToProcess + ''', COUNT(*) AS Count FROM ' + @TableToProcess + ' WHERE ' + @p_Column + ' =...

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

SSIS Convert a string into a datehttps://www.joellipman.com/articles/microsoft/ssis/ssis-convert-a-string-into-a-date.html

14 March 2013 + "/" + SUBSTRING((DT_STR,8,1252)StartDate,5,4)) Error: Description: "Invalid character value for cast specification". Exceptions for text file where I needed two derived column tasks, the first checks if the Date was blank and puts a NULL string. The...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Integration Services
• 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 details to display e.Note --CTEs defined above are used as the queries for the results FROM Dates d LEFT JOIN Events e ON CAST(CONVERT(VARCHAR(10),e.EventDate,101) AS DATETIME) = d.[Date] --Set the maximum times the Dates cte can recurse OPTION...

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

Migrate Joomla! 1.5.x to 2.5.x+https://www.joellipman.com/articles/cms/joomla/migrating-from-joomla-15-to-16.html

30 January 2011 AS lft, @new_jos_categories_id_last_lft+1 AS rgt, 1 AS `level`, CAST(@new_jos_categories_extension_alias AS CHAR) AS path, 'com_banners' AS extension, c.`title`, c.`alias`, CONCAT('Import from J15. PreviousID=catid', c.`id`) AS note, c.`description`,...

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

Room with a view E.M. Forsterhttps://www.joellipman.com/articles/_other-misc/room-with-a-view-em-forster.html

04 February 2011 Taking a break from website migrations. Thought I'd share some thoughts I was reminiscing of: "We cast a shadow on something wherever we stand, and it is no good moving from place to place to save things; because the shadow always follows. Choose a...

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

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 CHARACTER_MAXIMUM_LENGTH IS NULL THEN ',' + COLUMN_NAME + ' ' + DATA_TYPE ELSE ',' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')' END FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName ORDER BY...

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

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

11 October 2012 '.' + TABLE_NAME) + ' WHERE SOUNDEX(' + COLUMN_NAME + ')=SOUNDEX('''+ @p_SearchString +''') AND LEN(' + COLUMN_NAME + ')=' + CAST(@minStrLen AS varchar(11)) FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar',...

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