Skip to Content
author's profile photo Former Member
Former Member

Retrieve outgoing payment details for last 24 hours

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


Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Aug 31, 2016 at 04:11 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.