Mastering SQL Queries for SAP Business One

Date function—where the most problems emerge

The date function is one of the most problematic areas for SQL query. It is often used to return date-related results from a database. Some date functions were explained in the previous chapter. Get familiar with them first, if you are still not clear.

Case 4-D1: Balance of production for a month

A question which I received dealt with a query that needs to display the balance of production for the current month or for the past month.

SELECT SUM(T0.SYSDeb - T0.SYSCred) AS 'Production'
FROM JDT1 T0 
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239','_SYS00000000244')
UNION
SELECT SUM(T0.SYSDeb - T0.SYSCred) AS 'Production'
FROM JDT1 T0 
WHERE T0.Account = '_SYS00000000053'

As this query will work in ...