Query - Purchase Reguest added in last five minutes

[Former Member]

Hi,

I need a guery that will show to me all purchase requests added in last five minutes or 300 seconds...

Thank you in advance!!!

[Abdul · Accepted solution]

Hi,

use this query it check PO created in last 120 seconds just change table name and seconds

SELECT T0."DocNum", T0."DocDate", T0."CardCode", T0."CardName", T0."NumAtCard",T0."Project", T0."DocTotal" FROM OPOR T0 INNER JOIN OUSR T1 ON T0."UserSign" = T1."USERID" INNER JOIN POR1 on POR1."DocEntry" = T0."DocEntry" WHERE DAYS_BETWEEN(TO_DATE(CAST (T0."CreateDate" AS DATE), 'YYYY-MM-DD'), TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'))=0 AND SECONDS_BETWEEN (CAST (CONCAT(CONCAT((LEFT(RIGHT(CONCAT('0000' ,CAST(T0."DocTime" AS VARCHAR)),4 ),2)),':') , (RIGHT(RIGHT(CONCAT('0000' ,CAST(T0."DocTime" AS VARCHAR)),4 ),2)) ) AS TIME),CURRENT_TIME)<120

[Former Member]

Dear Abdul,

Thank You very much. It works perfectly.

Best Regards,

Vladimir

[Johan_H]

Hi Vladimir,

The header table contains the DocTime field, which is data type SMALLINT, but the number is the human readable time. In your example a document may have been added at 16:55, which will then be 1655 in the database. You will need to convert this smallint to minutes or seconds, perhaps by first converting to to nvarchar.

After that you can then simply query purchase requests by current date and with > DATEADD(MINUTES, -5, GETDATE()).

Regards,

Johan

[Former Member]

Dear Nagarajan,

Thank You very much for replying. I am not sure that I understand your question. By last five minutes I am thinking on present time minus 300 seconds or present time minus 5 minutes. So if now is 17:00 than I need documents added from 16:55 till 17:00

Thank You in advance!!!

[kothandaraman_nagarajan]

Hi,

What is your definition for last five minutes?

Regards,

Nagarajan