CODEWITHSUNDEEP
sqlms-accessms-access-2010
Jordan Smith
Jordan Smith

Reputation: 91

Use datediff() result in dsum() function

I have the following table with start and 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 would like to get the date/time interval (in seconds) and then calculate a running total.

Here is what I have so far:

SELECT 
    [dataid] AS [dataid]
    DateDiff("s", [TS],[EndTS]) AS [durationsec]
    DSum("[durationsec]","[HX32]","[dataid] <=" & [dataid]) AS [add]

FROM [HX32];

I think the datediff() funtion is possibly causing formatting problems. With "[durationsec]" I get all nulls as a result, with [durationsec] I get the following results:

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

I also tried cint(DateDiff("s", [TS],[EndTS])) No change. I also tried passing durationsec to a table and running a seperate query. No change. (Also I would prefer to do this all in one query)

Here are the results I would like to 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 a beginner.

Upvotes: 1

Views: 213

Answers (3)

June7
June7

Reputation: 21370

DSum is looking at the [HX32] table or query to find a field named [durationsec]. It doesn't exist there.

SELECT 
    [dataid],
    DateDiff("s",[TS],[EndTS]) AS [durationsec],
    DSum("DateDiff('s',[TS],[EndTS])","[HX32]","[dataid] <=" & [dataid]) AS [add]
FROM [HX32];`

Note the use of apostrophes to delimit the 's' parameter in the nested DateDiff.

An alternative approach is to do the running sum in Report because textbox in report has a RunningSum property. Domain aggregate functions in queries can perform slowly in large datasets.

Upvotes: 1

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

SELECT dataid,
       startts,
       endts,
       DATEDIFF("s", startts,endts) AS durationsec,
       SUM(DATEDIFF("s", startts,endts)) OVER (ORDER BY endts ROWS UNBOUNDED PRECEDING) AS runningtotal
  FROM durtab
 ORDER BY 5 DESC;

Result:

dataid  startts endts   durationsec runningtotal
1744    2017-07-27 13:57:34.000 2017-07-27 13:57:38.000 4   60
1743    2017-07-27 13:57:31.000 2017-07-27 13:57:34.000 3   56
1742    2017-07-27 13:57:23.000 2017-07-27 13:57:31.000 8   53
1741    2017-07-27 13:57:16.000 2017-07-27 13:57:23.000 7   45
1740    2017-07-27 13:57:04.000 2017-07-27 13:57:16.000 12  38
1739    2017-07-27 13:56:57.000 2017-07-27 13:57:04.000 7   26
1738    2017-07-27 13:56:38.000 2017-07-27 13:56:57.000 19  19

Upvotes: 1

Gustav
Gustav

Reputation: 55806

Time is not seconds but integer days, so you could try:

SELECT 
    [dataid],
    DateDiff("s", [TS], [EndTS]) AS [durationsec],
    DSum("[EndTS]-[TS]", "[HX32]", "[dataid] <= " & [dataid] & "") * 86400 AS [add]
FROM 
    [HX32];

That said, June's method should work as well. If both fail, something else is going on.

Upvotes: 1

Related Questions