-- today
select sum(traAmount), sum(traCredit) from transactions where traOrdID in (
select OrdID from orders A
left outer join
( select * from HL7Messages
union all
select * from MC_20091201..HL7Messages
union all
select * from MC_20091101..HL7Messages
union all
select * from MC_20091001..HL7Messages
union all
select * from MC_20090901..HL7Messages ) h on A.ordID = h.HL7OrdID
where h.hl7seq is null
 and A.createdby='HL7' and A.ordID>2000000 and created>='20091203'
)

-- today list
select HL7Seq, A.ordOrderNo, A.created, A.ordName, * from orders A
left outer join
( select * from HL7Messages
union all
select * from MC_20091201..HL7Messages
union all
select * from MC_20091101..HL7Messages
union all
select * from MC_20091001..HL7Messages
union all
select * from MC_20090901..HL7Messages ) h on A.ordID = h.HL7OrdID
where h.hl7seq is null
 and A.createdby='HL7' and A.ordID>2000000 and created>'20091203' order by  A.created desc