Formatting a date in an MDX queryhttp://mail.joellipman.com/articles/database/t-sql/formatting-a-date-in-an-mdx-query.html

11 January 2011 WHEN 1 THEN 'st' WHEN 21 THEN 'st' WHEN 31 THEN 'st' WHEN 2 THEN 'nd' WHEN 22 THEN 'nd' WHEN 3 THEN 'rd' WHEN 23 THEN 'rd' ELSE 'th' END AS Ordinal, All very posh. Even Microsoft don't have helpful documentation on using SWITCH in an MDX query. Chances...

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

PHP & MySQL Search Enginehttp://mail.joellipman.com/articles/web-development/php/php-a-mysql-search-engine.html

09 March 2011 $parts="(PageTitle RLIKE '[[::]]')".$parts."(PageContent RLIKE '[[::]]')"; } else { $scorepartstring = implode(' + ', $scoreparts); } // Generate the SQL query for your search engine $filter_sql = " SELECT t.PageID, t.PageTitle, t.PageContent, t.Source,...

• Type: Article
• Author: Joel Lipman
• Category: Personal Home Page
• Language: en-GB

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

23 March 2011 hour of the day SET @EndTime = @GivenDate + ' 17:30:00'; --First time of the day (required to display empty rooms) END ELSE BEGIN --First hour of the day SET @StartTime = @GivenDate + ' 00:00:00'; --Last hour of the day SET @EndTime = @GivenDate + '...

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

List all cron jobs for all usershttp://mail.joellipman.com/articles/linux/list-all-cron-jobs-for-all-users.html

04 May 2011 read line ; do match=$(echo "${line}" | egrep -o 'run-parts (-{1,2}\S+ )*\S+') if [[ -z "${match}" ]] ; then echo "${line}" else cron_fields=$(echo "${line}" | cut -f1-6 -d' ') cron_job_dir=$(echo "${match}" | awk '{print $NF}') if [[ -d...

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

Joes FREE Website Thumbnailer (JWT)http://mail.joellipman.com/component/content/article/joes-free-website-thumbnailer.html?catid=40

28 June 2011 it and it works perfectly for me. And I guess I'll just keep testing but there is the issue that you can't do anything else while it's sitting there taking pictures; unless you have more than one screen. It's automation. The situation is that the...

• Type: Article
• Author: Joel Lipman
• Category: Product Documentation
• Language: *

Importing Joomla articles to WordPress postshttp://mail.joellipman.com/articles/cms/wordpress/importing-joomla-articles-to-wordpress-posts.html

16 August 2011 CONCAT(introtext, ' ', `fulltext`) 'post_content', title 'post_title', '' post_excerpt, CASE state WHEN '1' THEN 'publish' ELSE 'draft' END 'post_status', 'open' comment_status, 'open' ping_status, '' post_password, alias 'post_name', '' to_ping, ''...

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

AutoHotkey Format Date and Format Secondshttp://mail.joellipman.com/articles/automation/autohotkey/autohotkey-format-date-and-format-seconds.html

20 September 2011 ThisDays:=ThisDays - (ThisYears*365) Grammar:=(ThisDays=1) ? "" : "s" strVal=%strVal% %ThisDays% day%Grammar%, } else { Grammar:=(ThisDays=1) ? "" : "s" strVal=%ThisDays% day%Grammar%, } } If ThisHours>0 { Grammar:=(ThisHours=1) ? "" : "s"...

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

Migrate Joomla Users to WordPresshttp://mail.joellipman.com/articles/cms/wordpress/migrate-joomla-users-to-wordpress.html

03 October 2011 ' ' AS 'user_url', a.registerDate AS 'user_registered', ' ' AS 'user_activation_key', CASE a.block WHEN 0 THEN 2 ELSE 0 END AS 'user_status', -- NOTE THAT THIS IS DEPRECATED IN WORDPRESS 3.X a.name AS 'display_name' FROM my_joomla_db.jos_users a ORDER...

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

How to Display Report Execution Time in SSRS (milliseconds)http://mail.joellipman.com/articles/microsoft/ssrs/how-to-display-report-execution-time-in-ssrs.html

17 November 2011 them to load) to get the timestamp of when the queries started. IIF your result row count, if it's zero, just display blank, else, do the rest of the query. Blank out the textbox basically (execution time is kinda irrelevant when you get 0 matches) We...

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

Returning Oracle Stored Procedure Resultset in SSRShttp://mail.joellipman.com/articles/microsoft/ssrs/returning-oracle-stored-procedure-resultset-in-ssrs.html

19 January 2012 Fixing this resolved all 3 issues below!!! Encountered Issues ORA-01403 no data found & ORA-06512 something else - Fixed by specifying the query parameter properly (ex: Parameter name = p_STUDENT_ADNAME, Parameter Value = [@StudentReference] - NOTE THE...

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

Migrate JComments from Joomla 1.5.x to Joomla 2.5.xhttp://mail.joellipman.com/articles/cms/joomla/migrate-jcomments-from-joomla-15x-to-joomla-25x.html

07 June 2012 checked_out_time, editor ) SELECT id, parent, CASE WHEN SUBSTRING(path, LOCATE(',', path)+1, LOCATE(',', path, 2))='' THEN 0 ELSE SUBSTRING(path, LOCATE(',', path)+1, LOCATE(',', path, 2)) END AS thread_id, path, level, object_id, object_group,...

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

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

03 August 2012 and data types into one string SELECT CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ',' + COLUMN_NAME + ' ' + DATA_TYPE ELSE ',' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')' END FROM...

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

Search a database with SOUNDEXhttp://mail.joellipman.com/articles/database/search-a-database-with-soundex.html

11 October 2012 OPEN Cursor1 FETCH NEXT FROM Cursor1 INTO @SqlToExecute WHILE @@FETCH_STATUS = 0 BEGIN IF @myCounter=0 PRINT @SqlToExecute; ELSE PRINT ' union all ' + @SqlToExecute; SET @myCounter = @myCounter + 1; FETCH NEXT FROM Cursor1 INTO @SqlToExecute END CLOSE...

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

Parse a HTML Table into a ListViewhttp://mail.joellipman.com/articles/web-development/html/parse-a-html-table-into-a-listview.html

30 March 2013 If RowIndex=1 HeadingsArrayString:=HeadingsArrayString "|" ThisValue If ColIndex=1 ThisLabel := ThisValue Else If RowIndex1 If ColIndex%ReturnedHTMLTableCols0% LV_Add("", ThisLabel " (" TypeLabel ")" , ThisValue ) ColIndex++ } } Snippets Assume that...

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

SSIS Script: convert UPPERCASE to Mixed-Case using TitleCasehttp://mail.joellipman.com/articles/microsoft/ssis/ssis-script-convert-uppercase-to-mixed-case-using-titlecase.html

04 October 2013 is Microsoft.SqlServer.Dts.Pipeline.ColumnIsNullException || e.GetBaseException() is System.NullReferenceException) { } else throw new Exception(e.ToString()); } } } } Allowing me to simply tick columns to apply this to (note that some of the images...

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

MS-DOS: Copy folders without overwriting fileshttp://mail.joellipman.com/articles/automation/ms-dos/ms-dos-copy-folders-without-overwriting-files.html

23 February 2014 files unused since n. /MINLAD:n :: MINimum Last Access Date - exclude files used since n. (If n...

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

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

04 June 2014 character of data irrespective of previous rules SET @Ret = UPPER(SUBSTRING(@Ret,1,1)) + SUBSTRING(@Ret,2,LEN(@Ret)); END ELSE BEGIN -- return the string unaffected if it is not in uppercase SET @Ret=@Text END RETURN @Ret END Examples of what this...

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

mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connecthttp://mail.joellipman.com/articles/database/mysql/mysqldump-got-error-2049-connection-using-old-pre-4-1-1-authentication-protocol-refused-client-option-secure-auth-enabled-when-trying-to-connect.html

28 July 2014 protocol). The error ONLY appears when I try to "data export" the database. Why? The quick solution for everyone else is to change/reset the password of the connecting database user, but herein lies the problem. When you read my workaround, you'll say...

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

Add attribute xsl:nil=true on empty elements using XSLThttp://mail.joellipman.com/articles/web-development/xml/xslt/add-attribute-xsl-nil-true-on-empty-elements-using-xslt.html

18 August 2014 of greater than 0 (not blank) and change the attribute of the element so that it's tag displays "xsi:nil=true". If...then...else... will be emulated by the XSLT choose...when...test...otherwise: true true -- yields: assuming value of DATE_OF_BIRTH is...

• Type: Article
• Author: Joel Lipman
• Category: XML Stylesheet Language Transformations
• Language: *

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

20 August 2014 IF @EndPos>0 BEGIN SET @Value = SUBSTRING(@CommaSeparatedValues, @StartPos, @EndPos-@StartPos) SET @StartPos=@EndPos+1 END ELSE BEGIN SET @ReachedEnd='Y' SET @Value = SUBSTRING(@CommaSeparatedValues, @StartPos, @LengthOfString-(@StartPos-1)) END SET...

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