SQL 2000 Average Column Across Two Tables 1

[Auguy]

I am trying to average the days for an invoice to be paid. I have two tables that contain the invoice information, one is the current information and the second is a history table. I cannot change these tables.
I can do this individually for each table, but am having a tough time getting the average from both tables. This code will give me the days to pay for each invoice, but I'm not surre how to get the average of these. I have removed most of the where clause to make it easier to read.

Select custno,datediff(d,invdte,dtepaid) as DaysToPay
  From armast
  Where custno='AAAA'
Union 
Select custno,datediff(d,invdte,dtepaid) as DaysToPay
  From arymst
  Where custno='AAAA'

This code gives me an average for each table, but I want the combined average.

Select custno,avg(cast(datediff(d,invdte,dtepaid) as decimal (10,1)) ) as DaysToPay
  From  armast
  where custno='AAAA'
  group By Custno
Union 
Select custno,avg(cast(datediff(d,invdte,dtepaid) as decimal (10,1)) ) as DaysToPay
  From  arymst
  where custno='AAAA'
group By Custno

Will I have to select into a temp table to get this done? I could do the calculation in the user interface, but thought this would be cleaner. This seems like an easy thing to do, but it is Friday.

Auguy
Sylvania/Toledo Ohio

 

[markros]

Try

select CustNo, avg(cast(DaysToPay as Decimal(10,2)) as AvgDays from (Select custno,datediff(d,invdte,dtepaid) as DaysToPay  From armast  Where custno='AAAA'
Union Select custno,datediff(d,invdte,dtepaid) as DaysToPay  From arymst  Where custno='AAAA') as Derived

PluralSight Learning Library

 

[Auguy]

Thanks Markros, that works. I will do some more testing to make sure I'm getting the proper results.

Auguy
Sylvania/Toledo Ohio