How to create more effective Alerts

How to setup Alerts using a field DocTime

Was creating some alerts and came across some situations. First the query that will trigger the alert should contain some filters, which DocType, DocStatus, DocDate so far so good. The problem arose when I needed to be more specific and put a limit on time. In recent versions of SAP Business One is a field marketing documents called DocTime then made ​​the following combination.

SELECT T0.DocEntry, T0.DocStatus, T0.DocDate, T0.CardCode, T0.CardName, T0.ObjType

FROM OINV T0 WHERE T0.DocStatus = ‘O’ AND T0.ObjType = ’13’

AND  CONVERT(VARCHAR,A.CREATEDATE,103) = CONVERT(VARCHAR,GETDATE(), 103)

AND (CONVERT(varchar, datepart(hour, getdate())) + ” + CONVERT(varchar, datepart(minute, getdate())) – T0.DocTime) <= 15

Before that it was need create a user field to record the time and then use in a comparison. Today it is possible to do this directly with field Native systems. And this comparison can be done directly by two ways, it will depend on whether you’re with SQL2008 or SQL2012.

SELECT T0.DocEntry, T0.DocStatus, T0.DocDate, T0.CardCode, T0.CardName, T0.ObjType

FROM OINV T0 WHERE T0.DocStatus = ‘O’ AND T0.ObjType = ’13’

AND  CONVERT(VARCHAR,A.CREATEDATE,103) = CONVERT(VARCHAR,GETDATE(), 103)

AND  CONVERT(CHAR,(T0.U_HOUR – T0.U_REMINDER),108)>= CONVERT (CHAR,GETDATE(),108)

In SQL2012 to use SELECT FORMAT(SYSDATETIME(), ‘HHmm’)

In SQL2008 to use SELECT CONVERT(varchar, datepart(hour, getdate())) + ” + CONVERT(varchar, datepart(minute, getdate())

..compared with the DocTime

Comments and suggestions are welcome!

References:

http://www.sql-server-helper.com/sql-server-2012/format-function-vs-convert-function.aspx

http://blog.sqlauthority.com/2012/07/16/sql-server-2012-functions-format-and-concat-an-interesting-usage/

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/a0c16e74-974b-2d10-ec98-a2c00675b392?QuickLink=index&overridelayout=true&48056389120629

Regards,

Marcelo Silva Santos