Creating Summary Table - from multiple tables

I have a report database server which is accessed by thousands of
customers. I am wanting to make a summary table of some of our most
accessed information. I was wanting to create a indexed view but it
has so many limitations that I am unable to use it (no unions,
subqueries, outer joins...)
I was able take a stored procedure with a bunch of temp tables and
rewrite this summary procedure into one big sql statement. However, as
I found out indexed views would not allow me to use all the sql
features which made this query possible.
I thought about using a trigger but I don't think I could use it
because the summary table is based off of 5 tables and all of the
information must exists before the summary can take place. This is
because it uses a weighted value of information from 3 of the 5 times
to get the final answers needed.
I have posted the query below. I changed this query so many times
trying to get the indexed view to work (before learning all the
limitations of it). This query can be shortened with the use of a
union statement. Also, the query is not finalized, but it has enough
information there that you can see the complex mess. The date range is
just in the query to make it run faster while trying to find something
that will work.
I am interested in any ideas that you may have.
Thanks
select customerid, ordid, cycle, orderid, eventid,
subtotal*allocatepercent as subtotal,
shiptotal*allocatepercent as shiptotal,
taxtotal*allocatepercent as taxtotal,
discount*allocatepercent as discount,
adjustment*allocatepercent as adjustment,
tendered*allocatepercent as tendered
from (
select customerid, ordid, cycle, a.orderid, a.eventid,
coalesce(subtotal, 0) as subtotal, coalesce(shiptotal, 0) as shiptotal,
coalesce(taxtotal, 0) as taxtotal, coalesce(discount, 0) as discount,
coalesce(adjustment, 0) as adjustment, coalesce(tendered, 0) as
tendered, b.retailvalue, c.retailvalue as totalretailvalue, case when
c.retailvalue=0 then b.retailvalue/events else
b.retailvalue/c.retailvalue end as allocatepercent
from
( select distinct customerid, ordid, cycle,
a.orderid, eventid, coalesce(subtotal, 0) as subtotal,
coalesce(shiptotal, 0) as shiptotal, coalesce(taxtotal, 0) as taxtotal,
coalesce(discount, 0) as discount, coalesce(adjustment, 0) as
adjustment, coalesce(tendered, 0) as tendered
from dbo.[order] a
inner join dbo.orderdetail b on
a.orderid=b.orderid
where recvdate>'1/20/06'
group by customerid, ordid, cycle, a.orderid,
eventid, coalesce(subtotal, 0), coalesce(shiptotal, 0),
coalesce(taxtotal, 0), coalesce(discount, 0), coalesce(adjustment, 0),
coalesce(tendered, 0)
) a
inner join (
select a.orderid, a.eventid,
sum(coalesce(b.retailvalue, 0) + coalesce(c.retailvalue, 0)) as
retailvalue
from (
select distinct
a.customerid, a.ordid, a.cycle, a.orderid, b.eventid,
coalesce(subtotal, 0) as subtotal, coalesce(shiptotal, 0) as shiptotal,
coalesce(taxtotal, 0) as taxtotal, coalesce(discount, 0) as discount,
coalesce(adjustment, 0) as adjustment, coalesce(tendered, 0) as
tendered
from dbo.[order] a
inner join
dbo.orderdetail b on a.orderid=b.orderid
where
recvdate>'1/20/06'
) a
left outer join (
select a.orderid, eventid, sum(coalesce(retailvalue, 0)) as retailvalue
from dbo.[order] a
inner join dbo.orderdetail b on a.orderid=b.orderid
where recvdate>'1/20/06' and orderpackagesid is null
group by a.orderid, eventid
) b on a.orderid=b.orderid and a.eventid=b.eventid
left outer join (
select orderid, eventid, sum(retailvalue) as retailvalue
from (
select orderid, eventid, coalesce(retailvalue, 0) as
retailvalue
from (
select distinct customerid, ordid, cycle,
a.orderid, eventid, orderpackagesid
from dbo.[order] a
inner join dbo.orderdetail b on
a.orderid=b.orderid
where recvdate>'1/20/06' and orderpackagesid is
not null
) a
inner join dbo.orderpackages b on
a.orderpackagesid=b.orderpackagesid
) a
group by orderid, eventid
) c on a.orderid=c.orderid and a.eventid=c.eventid
group by a.orderid, a.eventid
) b on a.orderid=b.orderid and
a.eventid=b.eventid
inner join (
select a.orderid, count(a.eventid)
as events, sum(coalesce(b.retailvalue, 0) + coalesce(c.retailvalue, 0))
as retailvalue
from (
select distinct
a.customerid, a.ordid, a.cycle, a.orderid, b.eventid,
coalesce(subtotal, 0) as subtotal, coalesce(shiptotal, 0) as shiptotal,
coalesce(taxtotal, 0) as taxtotal, coalesce(discount, 0) as discount,
coalesce(adjustment, 0) as adjustment, coalesce(tendered, 0) as
tendered
from dbo.[order] a
inner join
dbo.orderdetail b on a.orderid=b.orderid
where
recvdate>'1/20/06'
) a
left outer join (
select a.orderid, eventid, sum(coalesce(retailvalue, 0)) as retailvalue
from dbo.[order] a
inner join dbo.orderdetail b on a.orderid=b.orderid
where recvdate>'1/20/06' and orderpackagesid is null
group by a.orderid, eventid
) b on a.orderid=b.orderid and a.eventid=b.eventid
left outer join (
select orderid, eventid, sum(retailvalue) as retailvalue
from (
select orderid, eventid, coalesce(retailvalue, 0) as
retailvalue
from (
select distinct customerid, ordid, cycle,
a.orderid, eventid, orderpackagesid
from dbo.[order] a
inner join dbo.orderdetail b on
a.orderid=b.orderid
where recvdate>'1/20/06' and orderpackagesid is
not null
) a
inner join dbo.orderpackages b on
a.orderpackagesid=b.orderpackagesid
) a
group by orderid, eventid
) c on a.orderid=c.orderid and a.eventid=c.eventid
group by a.orderid
) c on a.orderid=c.orderid
) aLook up how DB2 implements MQTs with incremental refresh, and how
Oracle's materialized views are refreshed on commit. You can do it
yourself, like this:
create table customer(CustomerId int)
insert into customer select 1 union select 2 union select 3
go
create table orders(CustomerId int, amount float)
insert into orders select 1, 15.0 union select 2, 30.0
go
-- populate the summary initially
select CustomerID,
coalesce((select sum(amount) from orders o where o.CustomerID =
c.CustomerID), 0) SumOrder
into customer_summary
from customer c
go
-- start tracking relevant changes
select CustomerId, CustomerId InsOrDel into customer_log from customer
where (0=1)
select CustomerId, amount into orders_log from orders where (0=1)
go
create trigger customer_ins
on customer after insert
as
begin
insert into customer_log
select CustomerId, 1
from inserted
end
go
create trigger customer_del
on customer after delete
as
begin
insert into customer_log
select CustomerId, -1
from deleted
end
go
create trigger orders_ins
on orders after insert, update, delete
as
begin
insert into orders_log
select CustomerId, sum(amount)
from inserted
group by CustomerId
insert into orders_log
select CustomerId, -sum(amount)
from deleted
group by CustomerId
end
go
insert into orders select 1, 10.0 union select 3, 20.0
select * from orders_log
update orders set amount = amount + 1 where customerId = 1
select * from orders_log
delete from orders where amount between 10.0 and 12.0
select * from orders_log
insert into customer select 4 union select 5
insert into orders select 4, 1.0 union select 5, 1.0
delete from orders where customerId = 2
delete from customer where customerId = 2
go
-- refresh the summary
select * from customer_summary
go
insert into customer_summary
select CustomerId, 0 from Customer_log where InsOrDel=1
delete from customer_summary where CustomerId in(
select CustomerId from Customer_log where InsOrDel=-1)
update customer_summary set SumOrder = customer_summary.SumOrder +
ChgLog.SumOrder
from customer_summary,
(select customerId, sum(amount) SumOrder from orders_log
group by customerId) ChgLog
where customer_summary.customerId = ChgLog.customerId
go
select * from customer_summary
-- clear the logs
truncate table orders_log
truncate table customer_log
go
-- verify that the summary is up-to-date
select CustomerID,
(select sum(amount) from orders o where o.CustomerID = c.CustomerID)
SumOrder
from customer c
go
drop table customer
drop table orders
drop table orders_log
drop table customer_log
drop table customer_summary
go