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

03 November 2022 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" = po."Sales order ID" LEFT OUTER JOIN "Sales Order Items" soi ON soi."Sales...

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

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

11 January 2013 the DELETE clause (so instead of DELETE FROM myTable WHERE myValue=givenValue): DELETE s, ssf, sst FROM `Session` s LEFT OUTER JOIN `SessionStaff` ssf ON ssf.sessionId=s.sessionId LEFT OUTER JOIN `SessionStudent` sst ON sst.sessionId=s.sessionId WHERE...

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

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

24 September 2013 to 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

T-SQL Record Separatorhttps://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

Report Builder 2.0 - Hide Series1https://joellipman.com/articles/microsoft/ssrs/report-builder-20-hide-series1.html

06 May 2010 from appearing in your SQL Server Reporting Service (SSRS) report. I am guessing that Series1 is the data series from an outer join where the joining index value is null. The chart below has 3 series or data fields: Count the total number of incidents...

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

SQL Queries for Statisticshttps://joellipman.com/articles/database/mysql/sql-queries-for-statistics.html

13 December 2010 custombu_stats_visits a WHERE YEAR(a.DateTimeStamp)='2010' GROUP BY a.VisitorIP ORDER BY MAX(a.VisitorID) DESC ) t1 LEFT OUTER JOIN wikimedia_user b ON t1.ID=b.user_id GROUP BY t1.IP ORDER BY b.user_name ) t2 GROUP BY MONTH(t2.Date); Notes: I use this...

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

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

25 February 2011 MyTable2 WHERE MyTable2.ArticleID = MyTable1.PageID) -- STATEMENT4 SELECT PageID, PageTitle, PageContent FROM MyTable1 LEFT OUTER JOIN MyTable2 ON (MyTable1.PageID= MyTable2.ArticleID) WHERE MyTable2.ArticleID IS NULL Results STATEMENT1 880 rows 0.187s...

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

Room Availability Calendar in Business Intelligence Development Studiohttps://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: *

MySQL: Display Users and Duration in Matrix Timesheethttps://joellipman.com/articles/database/mysql/mysql-display-users-and-duration-in-matrix-timesheet.html

27 April 2012 the previous version of this query was pretty abysmal and worse if you want to list users who have no associated activities (outer join but all-in-one query). The DBA responsible for the system asked me to look at ways of reducing the load on the server...

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