SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Joining Multiple Sub Queries to Master
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bmsra79
Starting Member

24 Posts

Posted - 12/31/2012 :  09:54:53  Show Profile  Reply with Quote
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
7174 Posts

Posted - 12/31/2012 :  11:00:12  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000