September 24, 2010 at 2:49 pm
Hello. I'm trying to get the "before" and "after" records for a telephone customer when they change their ESN. I have a large set of rows from a data warehouse and they look like the following. I need to look thru these rows and establish that a customer has made an ESN change. That's the after row. The before row will be the row with the max Esn_Change_DT less than this after row with the same Cust_Id, Cust_Line_Seq_Id combination. I'm trying not to do this with a cursor if I can help it :-).
So out of this sample data I'd be looking to come out with:
--before (1, 1, 'ESN1001', '9/4/10', 'Nokia2', 'PP2000')
--after (1, 1, 'ESN1002', '9/7/10', 'LG1', 'PP2000')
--before (3, 1, 'ESN1020', '9/20/10', 'Samsung1', 'PP2000')
--after (3, 1, 'ESN1021', '9/21/10', 'Samsung1', 'PP3000')
Customer 2 isn't represented because their ESN didn't change. Hope I've explained this right. TIA.
IF OBJECT_ID('TempDB..#AcctActivity','U') IS NOT NULL
DROP TABLE #AcctActivity
Create Table #AcctActivity (
CUST_ID Varchar(40),
CUST_LINE_SEQ_ID Varchar(40),
ESN_NUM Varchar(256),
ESN_CHANGE_DT Datetime,
PROD_NM Varchar(32),
PPLAN_CD Varchar(20)
)
Insert #AcctActivity Values (1, 1, 'ESN1001', '9/1/10', 'Nokia1', 'PP2000')
Insert #AcctActivity Values (1, 1, 'ESN1001', '9/4/10', 'Nokia2', 'PP2000')
Insert #AcctActivity Values (1, 1, 'ESN1002', '9/7/10', 'LG1', 'PP2000')
Insert #AcctActivity Values (1, 1, 'ESN1002', '9/10/10', 'LG2', 'PP2000')
Insert #AcctActivity Values (2, 1, 'ESN1010', '9/1/10', 'Nokia', 'PP2000')
Insert #AcctActivity Values (2, 1, 'ESN1010', '9/4/10', 'Nokia', 'PP2000')
Insert #AcctActivity Values (3, 1, 'ESN1020', '9/20/10', 'Samsung1', 'PP2000')
Insert #AcctActivity Values (3, 1, 'ESN1021', '9/21/10', 'Samsung1', 'PP3000')
Insert #AcctActivity Values (3, 1, 'ESN1022', '9/22/10', 'Samsung1', 'PP3000')
Insert #AcctActivity Values (3, 1, 'ESN1022', '9/25/10', 'Samsung1', 'PP3000')
September 24, 2010 at 3:32 pm
What shall we do when we bump into this row?
Insert #AcctActivity Values (3, 1, 'ESN1022', '9/22/10', 'Samsung1', 'PP3000')
ESN# has changed once again for same customer, does a new before/after image has to be reported?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 24, 2010 at 3:37 pm
For now, nothing 😀 My report will produce counts in a given month for before and after the 1st ESN change in that month.
Ken
September 24, 2010 at 3:51 pm
ken.trock (9/24/2010)
For now, nothing 😀 My report will produce counts in a given month for before and after the 1st ESN change in that month.Ken
So this also needs to be date sorted, or will you be feeding it only a single month at a time?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 27, 2010 at 10:07 am
Just to get this effort started I'm going to be working a straight month at a time. For each situation that represents an ESN change for the customer/customer line combination I need the 2 rows and a way to note them as the before and the after. From there I'll have some customer call data to match against it.
Thanks!
Ken
September 28, 2010 at 9:06 am
I don't have time to build out some code, but I believe you can do this with a proper use of ROW_NUMBER, OVER, PARTITION BY, ORDER BY. See BOL for usage of the ROW_NUMBER and OVER clauses.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 28, 2010 at 10:03 am
How's this?
;
WITH CTE AS
(
-- First, get the Row # for each customer, and the row # for each customer/esn_num
SELECT *,
CustRowID = ROW_NUMBER() OVER (PARTITION BY CUST_ID ORDER BY ESN_CHANGE_DT),
CustESNRowID = ROW_NUMBER() OVER (PARTITION BY CUST_ID, ESN_NUM ORDER BY ESN_CHANGE_DT)
FROM #AcctActivity
),
CTE2 AS
(
-- Next, get the row # for when the row # for each customer/esn_num restarts
SELECT *,
CustRowCount = ROW_NUMBER() OVER (PARTITION BY CUST_ID ORDER BY CustRowID)
FROM CTE
WHERE CustRowID > 1
AND CustESNRowID = 1
)
-- get all the rows for when changed
SELECT CUST_ID, CUST_LINE_SEQ_ID, ESN_NUM, ESN_CHANGE_DT, PROD_NM, PPLAN_CD
FROM CTE2
WHERE CustRowCount = 1
UNION
-- and combine them with all the previous rows
SELECT t1.CUST_ID, t1.CUST_LINE_SEQ_ID, t1.ESN_NUM, t1.ESN_CHANGE_DT, t1.PROD_NM, t1.PPLAN_CD
FROM CTE t1
JOIN CTE2 t2
ON t1.CUST_ID = t2.CUST_ID
AND t1.CustRowID = t2.CustRowID-1
AND t2.CustRowCount = 1;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 28, 2010 at 1:44 pm
Wayne, this is sweet :exclamation: It looks like it can even be extended for use if I want to track all ESN changes per customer, not just the 1st. If you're at PASS this year I'll have to buy you a beer 🙂
Thanks a bunch,
Ken
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply