Grouping by calculated column without a temp table

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)

Grouping by calculated column without a temp table

Author

Topic

Dev Null
Starting Member

39 Posts

Posted - 2005-04-27 : 03:02:07

Hi All,

I've managed to solve my problem, but its an ugly, inefficient solution; I'm hoping someone can suggest something better. Here's the situation:

I have a table with entries every 15 minutes with a timestamp, an id, and a field that tells me wether the machine with that id was up for those 15 minutes, like so:

ir_timestamp     ir_id     ir_schdout
9:00             1         Y
9:15             1         Y
9:30             1         N
9:45             1         N
10:00            1         Y
10:15            1         N

I'd like to summarise that into single entries with a start and a stop time for each block that has the same ir_schdout value, like:

start     stop     ir_id     ir_schdout
9:00      9:30     1         Y
9:30      10:00    1         N
10:00     10:15    1         Y
10:15     10:30    1         N

...but I can't just group on ir_schdout, because the values may repeat (like in the example.) My solution was to get "stop" as the minimum later ir_timestamp with a different ir_schdout value, and group on that stop value, like so (with a little added data conversion):

select avm1.ir_id,
  DATEDIFF( second, @CurrentDate, avm1.ir_timestamp ) as start,
  (select ISNULL( DATEDIFF( second, @CurrentDate, MIN(avm2.ir_timestamp) ), 87600 ) 
   from ir_avmon avm2
   where DATEPART( dayofyear, avm1.ir_timestamp ) 
       = DATEPART( dayofyear, avm2.ir_timestamp )
     and avm1.ir_id = avm2.ir_id
     and avm1.ir_timestamp < avm2.ir_timestamp
     and avm1.ir_schdout <> avm2.ir_schdout
  ) as stop,
  'up' = CASE avm1.IR_SCHDOUT
           WHEN 'N' THEN CAST(1 AS BIT)
           WHEN 'Y' THEN CAST(0 AS BIT)
           ELSE NULL
         END
into #avmon_temp
from ir_avmon avm1
where DATEPART( dayofyear, avm1.ir_timestamp )
    = DATEPART( dayofyear, @CurrentDate )

select ir_id, min(start), stop, up
from #avmon_temp
group by ir_id, stop, up

... but as you can see, the restriction against grouping on a computed column means I have to compute into a temporary table and then group from there. This could be a big table, and that temporary table will be large too; I'd rather avoid it if I can. Can anyone suggest another way around the restriction? Any help appreciated,

- rob.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2005-04-27 : 05:07:59

Without understanding your code, you can almost always get rid of a single select temp table by using a "derived" table, like this:


select ir_id, min(start), stop, up 
from
(
	select avm1.ir_id,
	  DATEDIFF( second, @CurrentDate, avm1.ir_timestamp ) as start,
	  (select ISNULL( DATEDIFF( second, @CurrentDate, MIN(avm2.ir_timestamp) ), 87600 ) 
	   from ir_avmon avm2
	   where DATEPART( dayofyear, avm1.ir_timestamp ) 
	       = DATEPART( dayofyear, avm2.ir_timestamp )
	     and avm1.ir_id = avm2.ir_id
	     and avm1.ir_timestamp < avm2.ir_timestamp
	     and avm1.ir_schdout <> avm2.ir_schdout
	  ) as stop,
	  'up' = CASE avm1.IR_SCHDOUT
	           WHEN 'N' THEN CAST(1 AS BIT)
	           WHEN 'Y' THEN CAST(0 AS BIT)
	           ELSE NULL
	         END
	into #avmon_temp
	from ir_avmon avm1
	where DATEPART(dayofyear, avm1.ir_timestamp) = DATEPART(dayofyear, @CurrentDate)
) dt
group by ir_id, stop, up

Dev Null
Starting Member

39 Posts

Posted - 2005-04-27 : 21:52:29

Of course you can; I'm ashamed to admit I didn't think of it. Thanks, thats exactly what I was looking for.

- rob.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-27 : 22:24:12

by the way -- this might help you out:

http://www.sqlteam.com/item.asp?ItemID=12654

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources