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 before the end) SET @EndTime = @GivenDate + ' 17:30:00'; ; WITH Times([Time]) AS ( --Select First hour in range SELECT CONVERT(DATETIME,@StartTime) AS [Time] UNION ALL --Add a record for every half-hour in the range (change based on slot times) SELECT...

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

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 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 spaces to a table: CREATE FUNCTION dbo.[ufn_StringToTable] ( @StringInput VARCHAR(MAX) )...

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

Convert foreign characters to English alphabethttps://www.joellipman.com/articles/database/mysql/convert-foreign-characters-to-english-alphabet.html

13 June 2016 What? A quick article showing my MySQL statement when I want to remove all the accents from foreign characters and return the English equivalent. Why? A content management system (CMS) that I'm working on has just gone international and started...

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

Zoho Deluge: Convert Map to HTML Table without a FOR loophttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-convert-map-to-html-table-without-a-for-loop.html

11 January 2021 A very quick article on converting a Map string into a HTML table without using a for each loop. Why? I have quite a big response from our CRM that hits a statement execution limit if I use a for loop. I have a map with 3 columns: first_name, last_name,...

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

Oracle SQL - Convert given list (CSV) into Rowshttps://www.joellipman.com/articles/database/pl-sql/oracle-sql-convert-given-list-csv-into-rows.html

30 August 2011 Just a quick note as to something that worked for me. Taken from the Oracle forums @ https://forums.oracle.com/forums/thread.jspa?messageID=6861756 Situation I have one column of values that I've suffixed a comma to but I want to display this as a rows...

• Type: Article
• Author: Joel Lipman
• Category: Oracle PL/SQL
• Language: en-GB

Functions to convert Hex 2 RGB and vice-versahttps://www.joellipman.com/articles/automation/autohotkey/functions-to-convert-hex-2-rgb-and-vice-versa.html

27 September 2011 These are for autohotkey and in AHK code. Taken from Autohotkey forums topic 1878: From Hexadecimal to RGB From RGB to Hexadecimal Check for a valid hexadecimal value From Hexadecimal to RGB: HEX2RGB(HEXString,Delimiter="") { If Delimiter= Delimiter=,...

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

Php convert filesizes to bytes kb mb gbhttps://www.joellipman.com/articles/web-development/php/php-convert-filesizes-to-bytes-kb-mb-gb.html

03 January 2013 What? Just a quick note on how to format a given filesize and to reduce the display output to a small string, eg: 196 bytes : displays as => "196 bytes" 12945 bytes : displays as => "12 Kb" 1478515 bytes : displays as => "1 Mb" 8798745455 bytes :...

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

Zoho Deluge - Get Refresh/Access Token API v2https://www.joellipman.com/articles/crm/zoho/zoho-deluge-get-refresh-access-token.html

17 July 2019 // optional m_Data= Map(); m_Data.put("data",m_RecordData.toJSONList()); // NOTE: convert this to a JSON Array r_RecordUpdate = invokeUrl [ url :v_EndPoint type :PUT headers: m_Header parameters:m_Data.toString() ]; // NOTE: convert parameters variable...

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

Joe's Unduplicater (JUD)https://www.joellipman.com/component/content/article/joes-unduplicater.html?catid=40

12 July 2010 If you need to remove files which are simply the converted versions of music or video files, then this tool asks which folder has the files it will keep and which ones it will send to the Recycle Bin. TESTED ON: - Windows XP, Windows 7 SCREENSHOTS:...

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

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

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

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

AutoHotkey: Get Media Information and Displayhttps://www.joellipman.com/articles/automation/autohotkey/autohotkey-get-media-information-and-display.html

07 August 2020 a function I use called "Filexpro()", then list some examples of usage. And in the second-to-last section, I'll quickly convert a 100 nanoseconds unit to display Hours : Minutes : Seconds. We could have tried out some cool new DLL files for MS Windows...

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

Zoho Creator: Receive eBay Order Notifications via Webhookhttps://www.joellipman.com/articles/crm/zoho/zoho-creator-receive-ebay-order-notifications-via-webhook.html

20 September 2021 m_Params.put("PreferenceLevel","User"); m_Params.put("WarningLevel","High"); m_Params.put("ErrorLanguage","en_GB"); // // convert to xml and replace root nodes x_Params = m_Params.toXML(); x_Params = x_Params.toString().replaceFirst("",""); x_Params =...

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

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

28 June 2011 for FREE. I don't get it. So I wrote a program that does exactly that: visits each website in a list, takes a picture, converts it to a thumbnail. I then upload the images to my server and this Joomla! CMS uses them. Maybe I'll run this program again in...

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

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

20 September 2011 you can tell these are my messed up functions that convert dates into seconds and vice-versa. They're a little disorganised but they're the ones I copy and paste to my scripts then modify. In it's straightforward form FormatTime( TimeString, Format ) {...

• Type: Article
• Author: Joel Lipman
• Category: AutoHotkey
• 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 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: *

Zoho Deluge - Generate 5 Letter Booking Retrieval Codehttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-generate-5-letter-booking-retrieval-code.html

09 May 2019 to generate a 5 letter code from several functions: one which returns 5 randomly selected characters and another two which convert a number to 5 letters. Why? Zoho doesn't have a function to generate random numbers or strings. There are workarounds...

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

Zoho Deluge: Calculate Days, Hours, Minutes, Seconds between two Timestampshttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-calculate-days,-hours,-minutes,-seconds-between-two-timestamps.html

21 September 2021 day so we add 1 business day) v_NextTime = zoho.currentdate.addBusinessDay(1).toString("yyyy-MM-dd ") + "12:00:00"; // // convert to seconds v_NowEpoch = v_NowTime.toLong(); v_NextEpoch = v_NextTime.toTime().toLong(); // // calculate seconds in between...

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

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

25 February 2011 MediaWiki CMS: -- to get articles from MediaWiki CMS: Let's call it STATEMENT1 SELECT wikimedia_page.page_id AS PageID, CONVERT(wikimedia_page.page_title USING latin1) AS PageTitle, CONVERT(wikimedia_text.old_text USING latin1) AS PageContent FROM...

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

T-SQL Record Separatorhttps://www.joellipman.com/articles/database/t-sql/t-sql-record-separator.html

11 October 2011 '01202 ', '01202'), ' ', CHAR(10)) ELSE c.[Phone] END AS 'Phone', tt.[SiteId] AS 'Site', wm.[WeekNumber] AS FacilityWeek, CONVERT(CHAR(10), GETDATE(), 103) AS 'WeekStart', CONVERT(CHAR(10), DATEADD(day, 6, GETDATE()), 103) AS 'WeekEnd', SD.[SetId] AS...

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

SSRS Dropdown parameter cannot be blank!https://www.joellipman.com/articles/microsoft/ssrs/ssrs-dropdown-to-allow-blank-values.html

14 December 2011 SELECT d1.Name, d1.ItemID FROM ( SELECT Name, ItemID FROM Catalog WHERE Type = 2 UNION ALL SELECT TOP 1 '' AS Name, CONVERT(uniqueidentifier,'00000000-0000-0000-0000-000000000000') AS ItemID FROM ExecutionLogStorage ) AS d1 ORDER BY d1.Name ASC...

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