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

14 January 2011 CONVERT(VARCHAR(2),DATEADD(MONTH, 1,GETDATE()),101) + '/01/2009 01:30:00 PM', Note = 'Event 6' As you can see, these events were hard-coded into the script. I was asked to replace these with events from an actual database. So I just need a table...

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

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

01 August 2012 1, 0, REPLICATE('0', 3 - LEN(@RandMSeconds))); SET @TimeVal = CONVERT(time, @TimeVal, 14); END -- DETERMINE WHICH DATE PARTS WERE SUBMITTED IF @TimeMatch=1 AND @DateMatch=0 SET @NewVal = @TimeVal; ELSE IF @TimeMatch=0 AND @DateMatch=1 SET @NewVal =...

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

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

07 December 2012 send this data to your developers and the data types will be correct and maybe they'll resolve issues faster than if they were given scrambled data (see my articles on DataJumble and DataScramble). How? CREATE PROCEDURE [Common].[usp_ScrambleMultivalue]...

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

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

07 December 2012 1, 0, REPLICATE('0', 3 - LEN(@RandMSeconds))); SET @TimeVal = CONVERT(time, @TimeVal, 14); END -- DETERMINE WHICH DATE PARTS WERE SUBMITTED IF @TimeMatch=1 AND @DateMatch=0 SET @NewVal = @TimeVal; ELSE IF @TimeMatch=0 AND @DateMatch=1 SET @NewVal =...

• 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/else/database/t-sql/split-a-row-into-multiple-rows-based-on-a-column-value.html

23 August 2013 has all the days of sickness of employees. This table contains, which employee, on what date, and for how many days they were off sick. When migrating to a new system, the destination wanted 1 row per day. This meant that if in the old system, there was...

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

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

24 September 2013 with SQL Server and without having to download any third-party products. Why? I googled and binged and all I could find were people selling third party products... they don't get it. If you are reading this, it's likely you've paid for a commercial...

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

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

20 August 2014 to: Microsoft SQL Server 2008 R2 Microsoft SQL Server 2012 What? These were in a solution and I thought I'd note them on my site so I can refer to them more easily. How? Let's start with a function that just converts a string of words delimited by...

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

MySQL: Find records in one table that are not in another.https://www.joellipman.com/articles/else/database/mysql/mysql-find-records-in-one-table-that-are-not-in-another.html

25 February 2011 --------------------- 17 1144 2010-06-18 12:25:22 The How And here is the dilemma. The 3 below SELECT statements were derived from a post to the Stack Overflow website about the best way to check for rows in one table which don't exist in another. --...

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

Error 1065: Query was Emptyhttps://www.joellipman.com/articles/else/database/mysql/error-1065-query-was-empty.html

13 December 2012 this error pops up in so many systems I take over, I have often gone down the wrong path following the red herring as it were when in fact the answer to this is very common. Why? Bespoke systems are the usual suspects. Developers will have told the...

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

Delete related records from multiple tableshttps://www.joellipman.com/articles/else/database/mysql/delete-related-records-from-multiple-tables.html

11 January 2013 we use some JOIN statements. Why? It is never recommended to delete from multiple tables and instead to use the system you were given. Given a Relational Database Management System (RDBMS) where deleting some erroneous rows in one table will cause...

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

Country Lookup by IP address CSVhttps://www.joellipman.com/articles/else/database/mysql/country-lookup-by-ip-address.html

17 March 2013 was to create a refreshable country by IP address list that we can use when parsing our website logs to check where visitors were from. Note: we also use and love Advanced Web Statistics (awstats) but this was so that you could have your own country...

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

CSS Background Without Causing Scrollbarshttps://www.joellipman.com/articles/cms/website-development/css/css-background-without-causing-scrollbars.html

23 April 2011 case I spend as long as I did again. A Client had some clouds on the background of his photoshop file for his website. They were to be positioned in the top right corner with the leftmost tip aligned to the right of a central content section. I...

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

CSS keeping the footer at the bottomhttps://www.joellipman.com/articles/cms/website-development/css/css-keeping-the-footer-at-the-bottom.html

25 April 2011 in its own separate layer. In a joomla CMS template though, I wanted the "left" div to take over the whole page if there were no modules positioned in the "right" div. I specified width of left using PHP. This is the CSS though: #container { position:...

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

Parse a HTML Table into a ListViewhttps://www.joellipman.com/articles/cms/website-development/html/parse-a-html-table-into-a-listview.html

30 March 2013 A quick article on if you were given a webpage coded in HTML, what methods in AutoHotkey could you use to separate out the HTML Tables into a ListView. Why? I want a snippet of code that replicates any HTML table. How? I've been trying various ways so...

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

Sort an associative array by values in Javascripthttps://www.joellipman.com/articles/cms/website-development/js/sort-an-associative-array-by-values-in-javascript.html

29 September 2015 quite unhelpful. The original code is not my own either but that's not an excuse. I also found that examples across the web were only partial and thought I'd write a full example here. No jQuery and using the Google Chrome browser. I wanted to: Sort the...

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

Convert XML UTF-16 to JSON UTF-8 with PHP cURLhttps://www.joellipman.com/articles/cms/website-development/php/convert-xml-utf-16-to-json-utf-8-with-php-curl.html

28 March 2015 This is an article with notes for me on how to convert some received XML encoded in UTF-16 to some JSON in UTF-8. If it were entirely in UTF-8, I would simply load the received XML with SimpleXML and use the built-in PHP JSON_encode function. I ran into...

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

Foreign Characters create symbols in PHP and MySQLhttps://www.joellipman.com/articles/cms/website-development/php/foreign-characters-create-symbols-in-php-and-mysql.html

25 January 2016 extra parameters on the htmlentities or htmlspecialchars PHP functions. My database stored the foreign characters as they were so the fault was somewhere between PHP reading from MySQL to displaying the characters on the webpage.

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

Submit form as a server and not the client with cURLhttps://www.joellipman.com/component/content/article/submit-form-as-a-server-and-not-the-client-with-curl.html?catid=109&Itemid=165

20 February 2015 App to a third-party API. The third-party only accepts requests from a static and permitted IP address. If the end-user were to make the request, then their own IP address would be the one checked against, and it just wouldn't be manageable to add every...

• Type: Article
• Author: Joel Lipman
• Category: API Miscellaneous
• Language: *

GoDaddy Apache cPanel: Install SSL Certificatehttps://www.joellipman.com/component/content/article/godaddy-apache-cpanel-install-ssl-certificate.html?catid=111&Itemid=165

07 April 2022 seems to require re-installing each year despite it's expiry in a couple years time. The first time I did it, there were more steps; the second time I just did as per the below. If I ever have to do the long process again, I'll add it to this article....

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

Checklist for the local computer repairshttps://www.joellipman.com/component/content/article/checklist-for-the-local-computer-repairs.html?catid=116&Itemid=165

28 April 2011 from the backup I’d made silently. I guess it’s part of the “underpromise/overdeliver” thing… but several customers were intensely relieved when I was able to “work my magic” and save their business/thesis/whatever.″ Rates: Charge nominal fee £20 /hour...

• Type: Article
• Author: Joel Lipman
• Category: IT Support
• Language: *