Skip to Content

Query - Purchase Reguest added in last five minutes

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Apr 25 at 08:09 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 17 at 02:03 PM

    Hi,

    What is your definition for last five minutes?

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 24 at 02:32 PM

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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 25 at 06:14 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • May 04 at 10:43 AM

    Dear Abdul,

    Thank You very much. It works perfectly.

    Best Regards,

    Vladimir

    Add comment
    10|10000 characters needed characters exceeded