Hi Guys,
I have a query that gives me multiple rows for every customer, where as my requirement is to have one row for each customer. Below are the details:
Customers Table
Cust_ID int (PK)
Cust_Name varchar(255)
Subscription_Status int
Cust_Subscription_Audit Table
ID Identity(PK)
Cust_ID int
Old_Subscription_Status int
New_Subscription_Status int
Audit_Date DateTime
Logins Table (one customer may have multiple logins )
ID Identity (PK)
Cust_ID int
login name varchar(256)
email_ID varchar(256)
F_Name varchar(50)
L_name varchar(50)
role int
Last_Modified DateTime
INDEXES
Logins Table
PK__Clustered Ascending
No Index on Audit Table
Customers Table
PK__Clustered_ Ascending
Below is the query that is being used to extract the data
Code Snippet
SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID,MAX(CSA.Audit_Date)
FROM Customers C
INNERJOIN Cust_Subscription_Audit CSA ON C.Cust_ID= CSA.Cust_ID
INNERJOIN Logins L ON L.Cust_ID= C.Cust_ID
WHERE C.Subscription_Status >=1 AND L.Role= 1
GROUPBY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID
This gives me multiple rows for a customer. What I am looking for is
1) One row for each customer.(Query 1)
2) If possible can pick the row of my choice Like log in that was added first or last based on login or Modified date in login table.(Query 2)
3) If possible can pick the row by row number i.e. if there are multiple logins for a customer then the 2nd login. (Query 3)
Your help will be appreciated.
Thanks
-Leo
Can you post some DDL, including constraints and indexes, sample data and expected result, please?
AMB
|||Hi ,
Thanks for your prompt reply. Here is the Sample Data
Customers Table
Cust_ID Cust_Name SubsStatus
1 abc corp. 7
2 Adventure Works Inc 1
3 MicroCorn Inc 2
4 Fabrikam 7
5 Goleeeee Inc 5
Logins Table
ID Cust ID Login Email f_Name l_Name Role Date_Modified
1 1 rob1 rob1@.hotmail.com Rob Haany 1 2006-02-28 13:24
2 2 scot1 scot1@.yahoo.com Scot claudia 1 2006-05-24 12:30
3 3 scot2 scot2@.hotmail.com Scot Roy 1 2006-05-24 12:30
4 4 rob NULL RON HARTON 1 2006-08-22 15:26
5 4 qsir qsir@.hotmail.com Q Sir 1 2006-09-17 12:23
6 5 nzaar nzaar@.hotmail.com nad zaar 3 2006-09-17 12:23
7 5 jluk jluk@.hotmail.com Ron Richard 1 2006-09-17 12:23
8 2 drase draze@.hotmail.com Dino Mosoli 1 2006-08-22 15:26
9 2 dlins David Lin 3 2006-12-28 13:24
10 4 dmay NULL David May 1 2006-05-24 12:30
Role 1 = admin
Role 3 = Primary Contact
ID Cust_ID Old_Subscription_Status New_Subscription_Status Audit_Date
1 1 3 7 2006-09-24 12:30
2 2 0 1 2006-03-24 02:30
3 3 1 2 2006-08-24 10:30
4 4 1 7 2006-11-24 12:30
5 5 3 5 2006-09-24 12:30
6 4 3 1 2006-10-24 12:30
7 3 0 1 2006-06-24 12:30
8 1 0 1 2006-02-24 12:30
9 1 1 3 2006-07-24 12:30
10 5 1 3 2006-07-24 12:30
The required result is
Cust_ID Cust_Name F_name L_Name Email Audit Date
1 abc Corp Rob Haany rob1@.hotmail.com 2006-09-24 12:30
2 Adventure Works Inc Scot claudia scot1@.yahoo.com 2006-03-24 02:18
3 MicroCorn Inc Scot Roy scot2@.hotmail.com 2006-08-24 10:28
4 Fabrikam Q Sir qsir@.hotmail.com 2006-11-24 12:30
5 Goleeee Inc Ron Richard jluk@.hotmail.com 2006-09-24 12:30
Right now what is happening is threre are more than one records for Cust_ID = 2. The basic question is (Query 1) is how can we restrict the result set to only one row per Cust_ID. So my other questions (Query 2, Query3) were how can we choose one of these records based on some criteria e.g. since more than one records are there for Cust_ID = 2, and I wana choose the one that is with earliest Date_Modified or 2nd earliest Modified date.
Thanks,
-Leo.
|||hi Leo,
If you are using SQL server 2005 Try this:
Code Snippet
SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID, MAX(CSA.Audit_Date)
FROM Customers C
INNER JOIN Cust_Subscription_Audit CSA ON C.Cust_ID = CSA.Cust_ID
CROSS APPLY (select top 1 * from Logins where Cust_ID=CSA.Cust_ID ) L
WHERE C.Subscription_Status >=1 AND L.Role= 1
GROUP BY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID
|||Thank you very much for this nice hint. I tweeked the query little bit and it woked for me here is the modified query.
Code Snippet
SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID, MAX(CSA.Audit_Date)
FROM Customers C
INNER JOIN Cust_Subscription_Audit CSA ON C.Cust_ID = CSA.Cust_ID
OUTER APPLY (select top 1 * from Logins where Cust_ID=CSA.Cust_ID And [Role] = 1 ) L
WHERE C.Subscription_Status >=1
GROUP BY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID
|||do you need OUTER APPLU here? OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function (pretty much like OUTER JOIN in case of normal table). So if there is no match in table 'Logins' the value of L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID will be NULL.