Hi,
I am trying to generate a report rolled up to Month to date (MTD), Year to date (YTD) and Life to date (LTD) off of OLTP (not OLAP cubes). I am using the query below to do this.
But the table has 5mil + rows and the query executed for more than a day, inorder to improve performance I was planning to create a table which mimics date dimension in a star schema with a date column (on which jopin will be done) and other columns like yyyy, yyyymm. This way I can join payment table with the date dimension table and roll up based on yyyy and yyyymm columns which will be much faster as the optimizer will choose index seek instead of table scan.
The problem with this approach is the payment_table has datetime format (with up to milli sec accuracy) the date timension will have date in the format '2008-08-14 00:00:00.000' (one row per day) and my join will not work unless I populate the date dimension with all possible time with in a day which will run into billion + rows easily.
Is there any way I can achive better performance with what I am trying to do. This is on SQLserver 2005 (In 2008 MS introduced date datatype which is not there isn 2K5)
select * from
(
select customer_id, 'MTD' as record_type, count(*), sum(...), avg(...)
from payment_table
where
where year(payment_dt) = year(getDate())
and month(payment_dt) = month(getDate())
group by customer_id) MTD_payments
UNION ALL
(
select customer_id, 'YTD' as record_type, count(*), sum(...), avg(...)
from payment_table
where
where year(payment_dt) = year(getDate())
group by customer_id) YTD_payments
UNION ALL
(
select customer_id, 'LTD' as record_type, count(*), sum(...), avg(...)
from payment_table) LTD_payments
) payments_report
order by customer_id, record_type
