calculate the sum of differences 1

[Kobojunkie]

I have a situation where I need to get the last 20 INSERTS made by particular user into transaction table, then calculate the total amount of time it took to insert those 20 items into the table and then make sure it is less than 10 minutes or add a flag to another table. How do I go about accomplishing this please???

My current path has me retrieving the TOP 20 DESC order entry's in the Table by the particular user and then stepping through each row to do a sum of differences between each row and then comparing the sumtotal time with 10 minutes but I sort of feel there is a better way to do this and I would like help understanding this please.Thanks in advance.

 

[Golom]

Since this involves calculations on DateTime fields you should probably ask in the forum for the DBMS you are using because the functions for doing that vary from one DBMS to another. The code below would be used in Access.

UPDATE SomeOtherTable SET TheFlag = TheValue

Where UserID = 'SomeUserCode'
  And 10 <= 
(
       Select DateDiff ("n", MIN(EntryDate), 
                        MAX(EntryDate)) As [Time Taken]

       From
       (
         SELECT TOP 20 EntryDate
         FROM myTable
         Where UserID = 'SomeUserCode'
         Order By 1 DESC
         ) As X
)

 

[JarlH]

And TOP is also non-ANSI.
ORDER BY 1 is no longer ANSI. (Was removed some versions ago.)

Actually I believe datetime, TOP and stored procedures are the worst cases when comparing different dbms products' standard compliance - causes lots of trouble when writing portable SQL.