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 SELECT q.Employee AS EmployeeNo, RIGHT('000' + CAST(q.Employee AS VARCHAR), 3) + RIGHT( '00' + CAST( ( ROW_NUMBER() OVER (ORDER BY q.Employee) ) AS VARCHAR ), 2 ) AS QualificationRef FROM Qualifications q INNER JOIN Employee e ON RIGHT('000' +...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• 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 FULL OUTER JOIN [myDB2].[INFORMATION_SCHEMA].[COLUMNS] t2 ON t1.COLUMN_NAME=t2.COLUMN_NAME WHERE t2.COLUMN_NAME IS NULL ORDER BY t1.COLUMN_NAME -- columns in new database but not in old SELECT t2.* FROM [myDB1].[INFORMATION_SCHEMA].[COLUMNS] t1 FULL...

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

ZohoCRM: Process all records of a modulehttps://www.joellipman.com/articles/crm/zoho/zohocrm-process-all-records-of-a-module.html

24 August 2021 a datatype Boolean. Our function will then loop through each record and do what it has to do. The workaround here is that we order this by modified time. When the checkbox gets updated, this modifies the record and puts it at the bottom of the list. In...

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

Zoho Creator / Shopify: Get all Active Productshttps://www.joellipman.com/articles/crm/zoho/zoho-creator-shopify-get-all-active-products.html

12 October 2021 and list their IDs. How? So here's the function I came up with. It loops through a maximum of 2500 products sorted in order of ID ascending (starting with ID=0) retrieving 250 per call and using since_id to not list the same one twice. It then outputs a...

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

Zoho CRM: searchRecords with sorted resultshttps://www.joellipman.com/articles/crm/zoho/zoho-crm-searchrecords-with-sorted-results.html

10 February 2022 // // set results parameters m_SearchParam = Map(); m_SearchParam.put("sort_by", "Modified_Time"); m_SearchParam.put("sort_order", "asc"); // // loop through each page for each v_Page in l_Pages { l_SearchResults =...

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

ZohoDeluge: Get All eBay Orders Given a From and Till Datehttps://www.joellipman.com/articles/crm/zoho/zohodeluge-get-all-ebay-orders-given-a-from-and-till-date.html

28 February 2023 An article on how I can query all the orders for yesterday without using the eBay GUI Selling website. I've included the code to parse out the information as well. Why? Because I need this function too often either for debugging/monitoring purposes but...

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

ZohoCRM: Get Organization Business Hours using Deluge/APIhttps://www.joellipman.com/articles/crm/zoho/zohocrm-get-organization-business-hours-via-deluge-api.html

28 March 2023 "type": "custom" } } Tidied Up Want that in a practical format for use in ZohoCreator? Preferably not in the Zoho order, but that of the normal week (in other words, not alphabetical, numerical, or completely random order): void...

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

Zoho Books: Error 7008: There are no contact persons associated with this Invoicehttps://www.joellipman.com/articles/crm/zoho/zohobooks-error-7008-there-are-no-contact-persons-associated-with-this-invoice.html

07 March 2024 as a scope but I'm sure you can lock it down to just the scopes you need. void ZohoBooks.fn_SendZohoBooksInvoice(int p_OrderID) { v_OrderID = ifnull(input.p_OrderID,0).toLong(); /*...

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

MySQL Commands to display all columnshttps://www.joellipman.com/articles/database/mysql/mysql-commands-to-display-all-columns.html

28 October 2010 displays the structure of all the columns in all the databases of the localhost: SELECT * FROM information_schema.COLUMNS ORDER BY TABLE_NAME, COLUMN_NAME The following is a MYSQL query that finds all columns (displayed as "tablename.columnname") that...

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

GDPR Privacy Policyhttps://www.joellipman.com/static-items/gdpr-privacy-policy.html

14 February 2019 JoelLipman.com and its associates reserve the right to refuse service, terminate accounts, remove or edit content, or cancel orders in their sole discretion. Reviews, Comments, Emails, and Other Content Visitors may post reviews, comments, and other...

• Type: Article
• Author: Joel Lipman
• Category: Static Items
• Language: *

Privacy Policyhttps://www.joellipman.com/static-items/privacy-policy.html

30 May 2009 customise the website according to your interests. Security We are committed to ensuring that your information is secure. In order to prevent unauthorised access or disclosure we have put in place suitable physical, electronic and managerial procedures...

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

Installing phpBB3 for Joomla with a RocketTheme templatehttps://www.joellipman.com/articles/cms/joomla/installing-phpbb3-for-joomla-with-a-rockettheme-template.html

28 September 2009 instructions for applying the template and integrating it into Joomla that straightforward. It doesn't seem to matter what order you do the below in because I went through rocketheme's instructions once and it didn't work. I then carried out all the...

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

Creating a Top 10 chart with less codehttps://www.joellipman.com/articles/web-development/php/creating-a-top-10-chart-with-less-code.html

18 February 2010 My aim is to do the following: retrieve data from a table, count the number of times each data exists, sort it in reverse order so that the most frequent is at the top of the list print out each row with the number of times that particular data appeared...

• Type: Article
• Author: Joel Lipman
• Category: Personal Home Page
• Language: *

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

07 January 2011 = @siteId AND tt.[Status] 3 -- Remove cancelled bookings AND upper(sd.[Descrip]) not like 'ADDITIONAL TIMETABLE%' ORDER BY Date ASC, Room ASC, tt.[StartTime] ASC, tt.[FinishTime] ASC I even separated off the @GivenDate and @GivenCampus and re-assigned...

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

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

28 June 2011 sites you've done or how many times you used it (no audit trail). Client-side: Server solutions take up processing power in order to convert/download these on-the-fly. Use this program to generate the thumbnails and upload the images to your server....

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

Win32 Constantshttps://www.joellipman.com/articles/automation/autohotkey/win32-constants.html

05 July 2011 = $04000000 Const WS_CLIPCHILDREN = $02000000 Const WS_MAXIMIZE = $01000000 Const WS_CAPTION = $00C00000 Const WS_BORDER = $00800000 Const WS_DLGFRAME = $00400000 Const WS_VSCROLL = $00200000 Const WS_HSCROLL = $00100000 Const WS_SYSMENU = $00080000...

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

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

03 October 2011 my_joomla_db to the name of your joomla database. Note that your database user should have access to both databases in order for this to run smoothly: INSERT INTO my_wordpress_db.wp_users (ID, user_login, user_nicename, user_email, user_url,...

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

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

17 November 2011 dataset (would have to be the first dataset to load so you would need delete your current datasets then add them in the order you want them to load) to get the timestamp of when the queries started. IIF your result row count, if it's zero, just display...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• 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 the end-user will be none the wiser. OLD: Dataset to populate the dropdown SELECT Name, ItemID FROM Catalog WHERE Type = 2 ORDER BY Name ASC NEW: Dataset to populate the dropdown SELECT d1.Name, d1.ItemID FROM ( SELECT Name, ItemID FROM Catalog WHERE...

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

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 WHERE DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext') AND TABLE_SCHEMA='dbo' ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME; OPEN MyCursor FETCH NEXT FROM MyCursor INTO @SqlToExecute WHILE @@FETCH_STATUS = 0 BEGIN PRINT ' union...

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