Query help: durations - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query help: durations

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-06-27 : 14:15:11

...I know this is one of those I'm-missing-something-obvious questions and the answer will make me kick myself, but at this point it's worth it, as I've got little hair left.

Suppose I've got a table variable:

declare @t table (item_type tinyint, duration int)

...and I want to populate that table variable with the cumulative durations for each item type in another table:

CREATE TABLE schedule 
(item_id int NOT NULL, item_type tinyint, post_date datetime)

..So the duration for each item in schedule is:

select datediff(minute,post_date,(select min(post_date) from schedule s2 where s2.post_date>schedule.post_date)) from schedule

...Now, how can I aggregate that by each item type; that is, I want to know the total duraction for each item_type, regardless of item_id. I've tried wrapping the datediff in a sum(), but I get a "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

In case it helps, here's some sample data:


item_id   item_type   post_date
1         1           2003-06-27 02:19:00
2         2           2003-06-27 07:19:00
3         1           2003-06-27 08:25:00
4         2           2003-06-27 11:41:00
5         3           2003-06-27 15:12:00
5         1           2003-06-27 18:14:00

Is this possible to do in one step?

Thanks
-b

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-06-27 : 14:47:16

Can type out the desired output so that I can see if what I have is right?

For anyone interested in working on this one (its tough) try this:

 
declare @t table (item_type tinyint, duration int)


CREATE TABLE #schedule (item_id int NOT NULL, item_type tinyint, post_date datetime)

INSERT INTO #schedule(item_id, item_type, post_date) VALUES(1, 1, '2003-06-27 02:19:00')
INSERT INTO #schedule(item_id, item_type, post_date) VALUES(2, 2, '2003-06-27 07:19:00')
INSERT INTO #schedule(item_id, item_type, post_date) VALUES(3, 1, '2003-06-27 08:25:00')
INSERT INTO #schedule(item_id, item_type, post_date) VALUES(4, 2, '2003-06-27 11:41:00')
INSERT INTO #schedule(item_id, item_type, post_date) VALUES(5, 3, '2003-06-27 15:12:00')
INSERT INTO #schedule(item_id, item_type, post_date) VALUES(5, 1, '2003-06-27 18:14:00')

--Insert your magic here

DROP TABLE #schedule

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-06-27 : 15:02:22

Sure -- desired output would be:


Item_Type       Duration
1               496
2               257
3               182

...I'm not counting that last item_type of 1, since there's nothing after it. Ideally, in that case, the query would use getdate(), but realistically the sample size will be so large that dropping that item would be fine.

Thanks!
-b

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-27 : 15:38:36

select item_type, sum(dur)
from
(select item_id,
item_type, dur =
datediff(minute,post_date,(select min(post_date)
from schedule s2
where s2.post_date>schedule.post_date))
from schedule
where post_date <> (select max(post_date) from schedule
) as a
group by item_type

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-06-28 : 02:50:34

Thanks, Nigel -- you're fantastic.

For the record, I went with a slightly modified version that uses getdate() for any records where either 1) there's no later record, or 2) the later record is after getdate(). Thanks to Nigel, here's what I came up with for the subquery (the actual query uses sum and group by as nr suggested):

select item_type,datediff(minute,post_date,(
				 	select IsNull(min(post_date),getdate())
					 from schedule s2
					 where s2.post_date>schedule.post_date
					 and s2.post_date<=getdate()
					)
			       ) as duration
     from schedule
     where post_date<=getdate()
	and post_date>=dateadd(day,(-1*@iDays),getdate())

... of course, it's parameterized and slightly different, but very much based on the good help here.

Thanks!
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources