Retrieve outgoing payment details for last 24 hours

[Former Member]

Hi Everyone,

I made below query to get a list of outgoing payments over 100k for management monitoring purpose.  At the moment this retrieves data for the current date or for any date range.

However, I would like to modify this query to retrieve data for last 24 hours.

ex - from previous day 12 noon to current date 12 noon.

below is the query;

SELECT T0.DocNum AS 'DOC N0', T0.DocDate AS 'POST DATE', T0.DocTime AS 'TIME', T0.TransId AS 'TRANSACTION ID', T0.CardName AS 'VENDOR/CUSTOMER NAME',

T0.TrsfrSum AS 'AMOUNT', T0.TrsfrAcct AS 'TRANSFER A/C', T0.TrsfrRef AS 'REFERENCE', T0.Comments AS 'REMARKS', T1.U_NAME AS 'USER NAME' FROM dbo.OVPM T0

INNER JOIN dbo.OUSR T1 ON T1.USERID = T0.UserSign

WHERE DATEDIFF(DD, T0.DocDate , SYSDATETIME ()) > 0

AND DATEDIFF (DD, T0.DocDate, SYSDATETIME ()) < 2

AND T0.TrsfrSum > '100000'

GROUP BY T0.DocNum, T0.DocDate, T0.TransId, T0.CardName, T0.TrsfrAcct, T0.TrsfrRef, T0.Comments, T0.DocTime, T1.U_NAME, T0.TrsfrSum

ORDER BY T0.TrsfrSum desc, T0.DocDate desc, T0.DocTime desc

DocDate (DateTime)

DocTime (SmallInt)

Appreciate if you could explain to me how to modify the query to retrieve data for last 24 hours.

Thanks

[gaurav_bali · Accepted solution]

Hi Malith

Try Below Query. It will retrieve data for last 24 hours as per current time.

SELECT T0.DocNum AS 'DOC N0', T0.DocDate AS 'POST DATE', T0.DocTime AS 'TIME', T0.TransId AS 'TRANSACTION ID', T0.CardName AS 'VENDOR/CUSTOMER NAME',

T0.TrsfrSum AS 'AMOUNT', T0.TrsfrAcct AS 'TRANSFER A/C', T0.TrsfrRef AS 'REFERENCE', T0.Comments AS 'REMARKS', T1.U_NAME AS 'USER NAME'

FROM dbo.OVPM T0

INNER JOIN dbo.OUSR T1 ON T1.USERID = T0.UserSign

WHERE DATEDIFF(DD, T0.DocDate , SYSDATETIME ()) > 0

AND DATEDIFF (DD, T0.DocDate, SYSDATETIME ()) < 2

AND T0.TrsfrSum > '100000' and (T0.DocTime-left (replace(convert(nvarchar(10),GETDATE(),108),':',''),4)) >0

GROUP BY T0.DocNum, T0.DocDate, T0.TransId, T0.CardName, T0.TrsfrAcct, T0.TrsfrRef, T0.Comments, T0.DocTime, T1.U_NAME, T0.TrsfrSum

ORDER BY T0.TrsfrSum desc, T0.DocDate desc, T0.DocTime desc

[Former Member · Accepted solution]

Hi Gaurav,

Thank you for the feedback. However, When I run with the above no data returned. What could be the reason?.

[gaurav_bali · Accepted solution]

Hi Malith

There was a little mistake in calculations. The reason is that sap B1 stores time as integer instead of time in database. hence we need to convert it into time before executing any date time operation.

Here is the query with new algorithm. Though it is a bit complex it should calculate the time in minutes as per requirement also You can increase or decrease the range by Changing minutes (value 1440). Currently it is set at 24 hours (24X60 = 1440).

SELECT T0.DocNum AS 'DOC N0', T0.DocDate AS 'POST DATE', T0.DocTime AS 'TIME', T0.TransId AS 'TRANSACTION ID', T0.CardName AS 'VENDOR/CUSTOMER NAME',

T0.TrsfrSum AS 'AMOUNT', T0.TrsfrAcct AS 'TRANSFER A/C', T0.TrsfrRef AS 'REFERENCE', T0.Comments AS 'REMARKS', T1.U_NAME AS 'USER NAME'

FROM dbo.OVPM T0

INNER JOIN dbo.OUSR T1 ON T1.USERID = T0.UserSign

WHERE DATEDIFF(DD, T0.DocDate , SYSDATETIME ()) > 0

AND DATEDIFF (DD, T0.DocDate, SYSDATETIME ()) < 2

AND T0.TrsfrSum > '100000' and

DATEDIFF(MINUTE,CONVERT(datetime,

    SUBSTRING((CONVERT(nvarchar(10),T0.DocDate,112)+case when T0.DocTime < 1000 then '0'+convert(nvarchar(10),T0.DocTime) else CONVERT(nvarchar(10),T0.DocTime) end), 0, 5) + '-' +

    SUBSTRING((CONVERT(nvarchar(10),T0.DocDate,112)+case when T0.DocTime < 1000 then '0'+convert(nvarchar(10),T0.DocTime) else CONVERT(nvarchar(10),T0.DocTime) end), 5, 2) + '-' +

    SUBSTRING((CONVERT(nvarchar(10),T0.DocDate,112)+case when T0.DocTime < 1000 then '0'+convert(nvarchar(10),T0.DocTime) else CONVERT(nvarchar(10),T0.DocTime) end), 7, 2) + ' ' +

    SUBSTRING((CONVERT(nvarchar(10),T0.DocDate,112)+case when T0.DocTime < 1000 then '0'+convert(nvarchar(10),T0.DocTime) else CONVERT(nvarchar(10),T0.DocTime) end), 9, 2) + ':' +

    SUBSTRING((CONVERT(nvarchar(10),T0.DocDate,112)+case when T0.DocTime < 1000 then '0'+convert(nvarchar(10),T0.DocTime) else CONVERT(nvarchar(10),T0.DocTime) end), 11, 2) + ':' +

    '00'+ ':' +'000'),GETDATE())< 1440

GROUP BY T0.DocNum, T0.DocDate, T0.TransId, T0.CardName, T0.TrsfrAcct, T0.TrsfrRef, T0.Comments, T0.DocTime, T1.U_NAME, T0.TrsfrSum

ORDER BY T0.TrsfrSum desc, T0.DocDate desc, T0.DocTime desc

[Former Member · Accepted solution]

Hi Gaurav,

Thank you very much for your time and effort.

Cheers!