Detailed data and summary data with different windows in a single report - Suggest the better query

Database
Enthusiast

Detailed data and summary data with different windows in a single report - Suggest the better query

Requirement

I need the detail data for a date range window of 1 months but I need to add the AMT field for a date range window of 3 months and need to plot

that SUM(AMT) against every combination of A,B,C

Below are the two queries

1st Query uses a LEFT OUTER JOIN of the detail data to the Summary data to ensure that I do not loose data which did not exist for all the 3 months. This would require both the TOP and BOTTOM derived tables to be materialized there by excessive spool usage occurs. The original production query involves 6-7 huge tabe in the query.

2nd Query uses a SUM() OVER OLAP function and I have tried to accomplish the task in the same query and thereby not spooling the whole data twice.

I believe the second query will perform better.

Please suggest which query would perform optimally and correct me if i think otherwise.

Would appreciate your reply on that.

QUERY 1

-------

Select

a,b,c,d,e,

SUM(CASE (WHEN T1.f = 'XXX' and T3.LOAD_DTE BETWEEN '2011-01-01' And '2011-03-31') THEN T1.AMT ELSE 0)

OVER (PARTITION BY T1.a, t1.b, t1.c )AS QTRLY_AMT

From

Table T1

INNER JOIN T2

ON T1.a = T2.a

INNER JOIN T3

ON T2.b = T3.b

AND T3.LOAD_DTE BETWEEN '2011-01-01' And '2011-01-31'

QUERY 2

-------

Select top.a, top.b, top.c, top.d, top.e,

bottom.QTRLY_AMT

FROM

(

Select

a,b,c,d,e,

From

Table T1

INNER JOIN T2

ON T1.a = T2.a

INNER JOIN T3

ON T2.b = T3.b

AND T3.LOAD_DTE BETWEEN '2011-01-01' And '2011-01-31'

) AS TOP

LEFT OUTER JOIN

(

Select

T1.a, T1.b, T1.c, Sum(LOAD_DTE) AS QTRLY_AMT

From

Table T1

INNER JOIN T2

ON T1.a = T2.a

INNER JOIN T3

ON T2.b = T3.b

AND T3.LOAD_DTE BETWEEN '2011-01-01' And '2011-03-31'

GROUP BY

T1.a, T1.b, T1.c

) AS BOTTOM

ON TOP.a = BOTTOM.a

AND TOP.b = BOTTOM.b

AND TOP.c = BOTTOM.c