Zoho Analytics: Determine profits from invoices and purchase ordershttps://www.joellipman.com/articles/crm/zoho/zoho-analytics-determine-profits-from-invoices-and-purchase-orders.html

03 November 2022 AS "PO Custom Ref", po."Sales order ID" AS "SO ID", soi."Item ID" AS "SO Item ID" FROM "Purchase Order Items" poi LEFT JOIN "Purchase Orders" po ON po."Purchase Order ID" = poi."Purchase Order ID" LEFT OUTER JOIN "Sales Orders" so ON so."Sales order ID"...

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

T-SQL Conversion failed when converting the varchar to data type inthttps://www.joellipman.com/articles/database/t-sql/t-sql-conversion-failed-when-converting-the-varchar-to-data-type-int.html

14 May 2013 A very quick note in case I forget this one. If you are trying to join two tables and receiving the error "Conversion failed when converting the varchar value 'B110' to data type int" then read on. How? So where does the 'B110' string come from, well...

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

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

11 January 2013 So this is a quick article on how to delete from multiple tables in a mySQL database where 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...

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

Migrating from Joomla 2.5.x to 3.4.xhttps://www.joellipman.com/articles/cms/joomla/migrating-from-joomla-2-5-x-to-3-4-x.html

12 September 2015 ':|joe|:', c.`id`) AS new_name, a.`title`, a.`rules` FROM `v25_db`.`v25_assets` a INNER JOIN `v25_db`.`v25_content` c ON c.`asset_id`=a.`id` WHERE a.`name` LIKE 'com_content.article.%'; -- Articles INSERT INTO `v34_db`.`v34_content` ( `id`, `asset_id`,...

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

Zoho Analytics: Create a Monitoring System with Imageshttps://www.joellipman.com/articles/crm/zoho/zoho-analytics-create-a-monitoring-system-with-images.html

25 July 2024 5 days ago Use URL of the image representing Warning Else Use URL of image representing a FAILURE ***/ But if I want it to join to my images table and with a bit more foresight (I plan to do a mega query to rule them all which has all the data for my...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• 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 Notes', c.[Name] AS 'Contact', c.[Company] AS 'Dept', c.[Phone] AS 'Phone' FROM [CMISdata].[cmisuser].[SLOTDETAILS] sd INNER JOIN [CMISdata].[cmisuser].[TIMETABLE] tt ON tt.[SlotId] = sd.[SlotId] and tt.[SetId] = sd.[SetId] INNER JOIN...

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

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

11 October 2011 data SELECT t2.*, CASE WHEN t2.Day=t3.Day THEN NULL ELSE 'Separator Marker' END as 'Marker' FROM tblDifference t2 Left Outer Join tblDifference t3 On t2.RowNumber=t3.RowNumber-1 ORDER BY t2.RowNumber --...

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

SSIS Multiple Lookups in onehttps://www.joellipman.com/articles/microsoft/ssis/ssis-multiple-lookups-in-one.html

05 August 2014 want to do this. How? As a proof of concept, I created one lookup task that executes a SQL query which returns all the joined tables and displays the decode name. Let's pretend the info we want is rather simple: SELECT [sequenceID], [student_reference],...

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

List MediaWiki Articleshttps://www.joellipman.com/articles/cms/mediawiki/list-mediawiki-articles.html

22 September 2011 FROM wikimedia_categorylinks WHERE wikimedia_categorylinks.cl_from=p.page_id) AS PageCategory FROM wikimedia_page p LEFT JOIN wikimedia_revision r ON p.page_latest=r.rev_id LEFT JOIN wikimedia_text t ON r.rev_text_id=t.old_id WHERE p.page_namespace=0...

• Type: Article
• Author: Joel Lipman
• Category: MediaWiki
• 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 created_user_id, b.date AS created_time, '*' AS `language` FROM `my_old_database`.jos_categories c INNER JOIN `my_old_database`.jos_banner b ON b.catid=c.id WHERE c.section='com_banner'; --...

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

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 EventDate = '11:00:00', EventFinish='12:30:00', Status = '2', Tooltip='Some more details' ) SELECT * FROM Times t LEFT OUTER JOIN Events e ON CONVERT(VARCHAR(8) , t.Time, 108) BETWEEN e.EventStart AND DATEADD(minute, -1, e.EventFinish) --Set the maximum...

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

Performance Report - Background colors based on dataset valueshttps://www.joellipman.com/articles/microsoft/ssrs/green-red-background-colors-based-on-dataset-values.html

02 December 2011 [Total Time] ,RANK() OVER (ORDER BY e.[TimeStart] DESC) [Rank] FROM [ReportServer].[dbo].[ExecutionLogStorage] e INNER JOIN [ReportServer].[dbo].[Catalog] c ON e.ReportID = c.ItemID WHERE e.[ReportID] = @ReportParameter1 union all SELECT TOP 1 c.[Name]...

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

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

Upgrading Joomla CMS version 3.x to Joomla CMS 5.xhttps://www.joellipman.com/articles/cms/joomla/upgrading-joomla-cms-version-3-x-to-joomla-cms-5-x.html

04 February 2025 by group_id desc and changed 8 to 2. SELECT m.`user_id`, m.`group_id`, u.`username`, u.`email` FROM `#_user_usergroup_map` m JOIN `#_users` u ON u.id = m.user_id ORDER BY `group_id` DESC; UPDATE `#_user_usergroup_map` SET `group_id`=2 WHERE `user_id`111...

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

MySQL parameters in Excel 2007 PivotTableshttps://www.joellipman.com/articles/database/mysql/mysql-parameters-in-excel-2007-pivottables.html

24 August 2011 CASE COUNT(*) WHEN 1 THEN '(1 Event)' ELSE '(Multiple Events)' END AS DataAccuracy FROM StaffActivities s INNER JOIN Users u ON u.UserID=s.StaffUserID INNER JOIN Resources r ON r.ThisResourceID=s.CategoryID INNER JOIN Teams t ON t.TeamID=s.TeamID GROUP...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• 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 SCh.name + '.' + ST.NAME, ( SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10) FROM SYS.columns SC JOIN SYS.types STy ON STy.system_type_id = SC.system_type_id AND STy.user_type_id =SC.user_type_id WHERE STY.name in...

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

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 3) AS EmployeeNo, RIGHT('000' + CAST(q.Employee AS VARCHAR), 3) + '01' AS QualificationRef FROM Qualifications q INNER JOIN Employee e ON RIGHT('000' + CAST(e.ID AS VARCHAR), 3)=q.Employee -- yields EmployeeNo QualificationRef...

• 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 , 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 n.number...

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

ZohoAnalytics & ZohoBooks: Custom Related List from Analyticshttps://www.joellipman.com/articles/crm/zoho/zohoanalytics-zohobooks-custom-related-list-from-analytics.html

09 July 2024 including the custom fields per line items to ZohoAnalytics; and in ZohoAnalytics importing a table based on a query which joins the sales orders and purchase order items by the custom lookup field. What follows is the code required to build the custom...

• 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 USING latin1) AS PageTitle, CONVERT(wikimedia_text.old_text USING latin1) AS PageContent FROM wikimedia_page LEFT JOIN ( wikimedia_revision, wikimedia_text) ON wikimedia_page.page_latest=wikimedia_revision.rev_id WHERE wikimedia_page.page_namespace=0...

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