sql - Use datediff() result in dsum() function -


i have following table start , end dates:

dataid     ts                 endts           1744    7/27/17 1:57:34 pm    7/27/2017 1:57:38 pm     1743    7/27/17 1:57:31 pm    7/27/2017 1:57:34 pm    1742    7/27/17 1:57:23 pm    7/27/2017 1:57:31 pm     1741    7/27/17 1:57:16 pm    7/27/2017 1:57:23 pm    1740    7/27/17 1:57:04 pm    7/27/2017 1:57:16 pm     1739    7/27/17 1:56:57 pm    7/27/2017 1:57:04 pm     1738    7/27/17 1:56:38 pm    7/27/2017 1:56:57 pm   

i date/time interval (in seconds) , calculate running total.

here have far:

select      [dataid] [dataid]     datediff("s", [ts],[endts]) [durationsec]     dsum("[durationsec]","[hx32]","[dataid] <=" & [dataid]) [add]  [hx32]; 

i think datediff() funtion possibly causing formatting problems. "[durationsec]" nulls result, [durationsec] following results:

 durationsec add        4    6896        3    5169        8    13776        7    12047        12   20640        7    12033        19   32642 

i tried cint(datediff("s", [ts],[endts])) no change. tried passing durationsec table , running seperate query. no change. (also prefer in 1 query)

here results achieve:

dataid     ts                 endts          durationsec    add 1744    7/27/17 1:57:34 pm    7/27/2017 1:57:38 pm    4      60 1743    7/27/17 1:57:31 pm    7/27/2017 1:57:34 pm    3      56 1742    7/27/17 1:57:23 pm    7/27/2017 1:57:31 pm    8      53 1741    7/27/17 1:57:16 pm    7/27/2017 1:57:23 pm    7      45 1740    7/27/17 1:57:04 pm    7/27/2017 1:57:16 pm    12     38 1739    7/27/17 1:56:57 pm    7/27/2017 1:57:04 pm    7      26 1738    7/27/17 1:56:38 pm    7/27/2017 1:56:57 pm    19     19 

thanks, i'm beginner.

time not seconds integer days, try:

select      [dataid],     datediff("s", [ts], [endts]) [durationsec],     dsum("[endts]-[ts]", "[hx32]", "[dataid] <= " & [dataid] & "") * 86400 [add]      [hx32]; 

that said, june's method should work well. if both fail, else going on.


Comments

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

vue.js - Create hooks for automated testing -

Add new key value to json node in java -