>I think that was close but not quite it.
>
>

>MEETINGID BEGINTIME           ENDTIME           ISBLOCK  BLOCKID BLOCKMEMBER
>INT          DT                 DT               BIT      INT      BIT
>------------------------------------------------------------------------
>1         12/14/2001 07:00   12/14/2001 11:00     1        1        0
>2         12/14/2001 07:00   12/14/2001 07:45     0        1        1
>3         12/14/2001 07:45   12/14/2001 07:55     0        1        1
>4         12/14/2001 07:55   12/14/2001 08:40     0        1        1
>

>
>In this example (real life):
>BlockTime=240 minutes (7am to 11am)
>UsedTime=100
>
>Using query returns the correct number of UsedTime, but the BlockTime is return 720. It just so happens that is the blocktime x 3 (number of blockmembers)
>
Hi Kirk,

Yes, the block time was multiplied by the number of member records in this block. There is a kludgy way to fix it. The CAST() function converts integers into numbers otherwise SQL Server would use integer arithmetic.

Select mb.blockid,
      SUM(DATEDIFF( mi, mb.BEGINTIME, mb.ENDTIME))/COUNT(*) AS blocktime,
      SUM( DATEDIFF( mi, ap.BEGINTIME, ap.ENDTIME)) AS usedtime,
      CAST( SUM( DATEDIFF( mi, ap.BEGINTIME, ap.ENDTIME)) AS numeric(5,2)) /
        SUM( DATEDIFF( mi, mb.BEGINTIME, mb.ENDTIME)) * COUNT(*) AS used
  FROM  meetings mb 
  JOIN  meetings ap ON mb.BLOCKID = ap.BLOCKID 
    AND mb.ISBLOCK = 1 AND ap.BLOCKMEMBER = 1
  GROUP BY mb.blockid

There's more elegant SQL Servet version

Select ap.blockid,
      CAST( CAST( SUM( DATEDIFF( mi, ap.BEGINTIME, ap.ENDTIME)) AS numeric(7,2)) /
	( 
	Select CAST(DATEDIFF( mi, mb.BEGINTIME, mb.ENDTIME) AS numeric(7,2)) 
	  FROM  meetings mb 
	  WHERE mb.ISBLOCK = 1 and mb.BLOCKID = ap.BLOCKID ) AS numeric(7,2)) as used
  FROM meetings ap 
  WHERE ap.BLOCKMEMBER = 1
  GROUP BY ap.blockid