Skip to Content
0

Query - Purchase Reguest added in last five minutes

Apr 17 at 10:37 AM

50

avatar image

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!!!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Best Answer
Abdul Mannan Apr 25 at 08:09 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Apr 17 at 02:03 PM
0

Hi,

What is your definition for last five minutes?

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded
Vladimir Mladenović Apr 24 at 02:32 PM
0

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!!!

Share
10 |10000 characters needed characters left characters exceeded
Johan Hakkesteegt Apr 25 at 06:14 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Vladimir Mladenović May 04 at 10:43 AM
0

Dear Abdul,

Thank You very much. It works perfectly.

Best Regards,

Vladimir

Share
10 |10000 characters needed characters left characters exceeded