Hi Group,

This one has me a bit stumped at the moment.

The structure looks a bit like this:

TableA
(
ID,
StartTime,
EndTime
)

TableB
(
ID,
TableAID, <-- FK
StartTime,
EndTime
)


[TableA] 1..* [TableB]

EndTime in both tables can be null. I have a function TotalMins that
does a Coalesce with 'now', an undefined endtime means there is no
endtime, and it should be considered "active".

I will spare you the real select statement because you will be here
all day, but in essence.

select
a.StartTime,
a.EndTime,
TotalMins(a.StartTime, a.EndTime) as ATotalMins
Coalesce((select sum(TotalMins(b.StartTime,b.EndTime) from TableB b
where b.TableAID=a.ID),0) as BTotalMins
from TableA a
where ....

Now that works (well at least my version does, I may not have
translated it into tableA and tableB quite right, but you get the gist)

I would like to also include

ATotalMins-BTotalMins as NetMins

obviously it doesn't like that, but I don't want to do another round
trip to TableB, because I already have all the data I need from it. I
also calculate a variance based on NetMins and another queried figure,
so that would be another hit.

Any suggestions?

Adam