Joining Multiple Sub Queries to Master

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Joining Multiple Sub Queries to Master

Author

Topic

bmsra79
Starting Member

24 Posts

Posted - 2012-12-31 : 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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-31 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources