Get min and max rows without a cursor
-
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 NULLDROP TABLE #AcctActivityCreate 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') -
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) BerzukovAuthor 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. -
For now, nothing
My report will produce counts in a given month for before and after the 1st ESN change in that month.Ken
-
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?
- Craig FarrellNever 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 -
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
-
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 -
How's this?
;WITH CTE AS(-- First, get the Row # for each customer, and the row # for each customer/esn_numSELECT *,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 restartsSELECT *,CustRowCount = ROW_NUMBER() OVER (PARTITION BY CUST_ID ORDER BY CustRowID)FROM CTEWHERE CustRowID > 1AND CustESNRowID = 1)-- get all the rows for when changedSELECT CUST_ID, CUST_LINE_SEQ_ID, ESN_NUM, ESN_CHANGE_DT, PROD_NM, PPLAN_CDFROM CTE2WHERE CustRowCount = 1UNION-- and combine them with all the previous rowsSELECT t1.CUST_ID, t1.CUST_LINE_SEQ_ID, t1.ESN_NUM, t1.ESN_CHANGE_DT, t1.PROD_NM, t1.PPLAN_CDFROM CTE t1JOIN CTE2 t2ON t1.CUST_ID = t2.CUST_IDAND t1.CustRowID = t2.CustRowID-1AND t2.CustRowCount = 1;Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
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