469,915 Members | 2,457 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,915 developers. It's quick & easy.

Need help with query that does both timediff & sum

Dan
Hello,

Am trying to construct a query that'll both compute elapsed time based
on certain criteria and sum this up for a subset of records.

Sample Data that I'm working with:

Row Ev_Time Ev_id Ev_Desc
-------------------------------------------------------------------
1 2004-12-03 14:28:45 400 Batch set Run
2 2004-12-03 14:28:45 201 Batch received Start
3 2004-12-03 14:28:45 500 Unit Received Run
4 2004-12-03 14:28:45 502 Operation Received Run
5 2004-12-03 14:28:45 210 Allocated
6 2004-12-03 14:28:45 227 Set Start
7 2004-12-03 14:28:45 226 Received Ready
8 2004-12-03 14:28:47 228 Received Run
9 2004-12-03 14:28:49 230 Received Held
10 2004-12-03 14:30:49 231 Set Restart
........
28 2004-12-03 14:40:02 230 Received Held
29 2004-12-03 14:40:32 231 Set Restart
30 2004-12-03 14:40:35 234 Received Done
31 2004-12-03 14:40:35 235 Set Reset
32 2004-12-03 14:40:35 226 Received Ready
33 2004-12-03 14:40:35 227 Set Start
34 2004-12-03 14:40:37 228 Received Run
35 2004-12-03 14:45:28 234 Received Done
36 2004-12-03 14:45:28 235 Set Reset
37 2004-12-03 14:45:28 226 Received Ready
38 2004-12-03 14:45:28 227 Set Start
39 2004-12-03 14:45:29 228 Received Run
40 2004-12-03 15:26:20 229 Set Hold
41 2004-12-03 15:27:20 209 Batch set Aborted
42 2004-12-07 15:23:36 206 Batch set Closed
Need query that'll compute timediffs between ev_ids (230 & 231) & (229,
209) across all records and then sum it up:

ie: SUM (timediffs between row pairs (9,10), (28,29) & (40,41) )
120 + 30 + 60 = 210 Seconds
Any tips would be welcome

Thanks
-D

Jul 23 '05 #1
3 2868
[posted and mailed, please reply in news]

Dan (da************@gmail.com) writes:
Am trying to construct a query that'll both compute elapsed time based
on certain criteria and sum this up for a subset of records.
...
Need query that'll compute timediffs between ev_ids (230 & 231) & (229,
209) across all records and then sum it up:

ie: SUM (timediffs between row pairs (9,10), (28,29) & (40,41) )
120 + 30 + 60 = 210 Seconds


For this type of questions it is always a good idea to post:

o CREATE TABLE statement for your table.
o INSERT statement with sample data.
o The desired result given the sample data.

This makes it possible for anyone who answers the question to copy the SQL
to Query Analyzer, and develop a tested solution.

Since you did not provide SQL, this is an untested solution:

SELECT SUM(CASE WHEN t.Ev_id IN (231, 209) THEN 1
WHEN t.Ev_id IN (230, 229) THEN -1
END * datediff(ss, a.min_time, t.Ev_Time))
FROM tbl t
CROSS JOIN (SELECT min_time = MIN(Ev_Time) FROM tbl) a
WHERE t.Ev_id IN (209. 229, 230, 231)

This query assumes that all events have completed. If the last event
has not terminated, you will get a gross error.

The point with the derived table is to give a time zero. This
could be any time, but picking from the data, minimizes the risk
that the datediff will overflow. But you could pick 2000-01-01 if
you like.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
On 21 Mar 2005 13:49:33 -0800, Dan wrote:
Hello,

Am trying to construct a query that'll both compute elapsed time based
on certain criteria and sum this up for a subset of records.

Sample Data that I'm working with: (snip)
Need query that'll compute timediffs between ev_ids (230 & 231) & (229,
209) across all records and then sum it up:

(snip)

Hi Dan,

-- Create temp table to hold the pairs we need
DECLARE @Required TABLE (FirstId int NOT NULL,
SecondId int NOT NULL,
PRIMARY KEY (FirstId, SecondId)
)
-- Insert data in the temp table
INSERT INTO @Required (FirstId, SecondId)
VALUES (230, 231)
INSERT INTO @Required (FirstId, SecondId)
VALUES (239, 209)
-- Find and sum the results
SELECT SUM(DATEDIFF(second, a.Ev_Time, b.Ev_Time)
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.Row = a.Row + 1
INNER JOIN @Required AS c
ON c.FirstId = a.Ev_id
AND c.SecondId = a.Ev_id

(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #3
Dan
Thanks Folks for the responses. Used the suggestion by Erland and it
works.
Aplogies for not posting the actual data...but the table structure and
size made it difficult to post it on a usenet message...hence
the abbreviated version.

-Dan

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by |-|erc | last post: by
3 posts views Thread by JA | last post: by
25 posts views Thread by Bjørn T Johansen | last post: by
reply views Thread by ward | last post: by
Colloid Snake
3 posts views Thread by Colloid Snake | last post: by
3 posts views Thread by pbd22 | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.