| Author |
 Topic  |
|
|
bmsra79
Starting Member
24 Posts |
 Posted - 12/31/2012 : 09:54:53
|
I have huge aggregated view called Customer_Master with > 50 columns with few selected columns as below
ID, EnrolDate, StartDate, StopDate
X1, 21-Sep-11, 23-Sep-11, 24-Sep-11
X2, 24-Sep-12, 24-Sep-12, 25-Sep-12
CallData (Huge table with > 2 million records)
ID, CallDate
X1, 22-Sep-11
X1, 20-Sep-11
X1, 22-Sep-11
X1, 23-Sep-11
X1, 25-Sep-11
X2, 26-Sep-12
X2, 27-Sep-12
X2, 28-Sep-12
X2, 29-Sep-12
I want to query the 2 tables above to get the output in the format below. What is the most efficient way to achieve the below resultset.
ID, EnrolDate, StartDate, StopDate, Calls_Before_Start_Date, Calls_After_Stop_Date, Most_Recent_Call_Date
X1, 21-Sep-11, 23-Sep-11, 22-Sep-11, 3, 1, 25-Sep-11
X2, 24-Sep-12, 24-Sep-12, 25-Sep-12, 0, 4, 29-Sep-12 |
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/31/2012 : 11:00:12
|
Make sure you have clustered index on ID
Select ID,EnrollDate,StartDate,StopDate,Calls_Before_Start_Date,Calls_After_Stop_Date,MAXCallDate
from Customer_Data CT
inner join
(
Select C.ID,
SUM(Case When CallData < StartDate then 1 Else 0 End)Calls_Before_Start_Date,
SUM(Case When CallData > StopDate then 1 Else 0 End)Calls_After_Stop_Date,
MAX(CallDate) MAXCallDate
from Customer_Master C
inner join CallData D on C.ID = D.ID
Group by C.ID
)T on T.ID = CT.ID |
Edited by - sodeep on 12/31/2012 11:00:50 |
 |
|
| |
Topic |
|
|
|
Joining Multiple Sub Queries to Master
