Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 2 records at a time? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, November 15, 2013 1:13 PM
 Grasshopper Group: General Forum Members Last Login: Friday, August 21, 2015 2:05 PM Points: 12, Visits: 86
 Hi everyone,I'm not even sure how to word this succinctly enough to search for this topic, so I apologize in advance if this is covered elsewhere! It almost feels like a gaps and islands issue, but not quite....Background Info:I am developing a form for a mortgage company. There can be any number of borrowers on a given loan, and the business has asked that this form return only 2 borrowers at a time for a loan. For example, if there are 3 borrowers for a loan, they want the first copy of the form to print the first 2 borrowers and then another copy of the form to print the 3rd. No matter how many copies are printed, they want the borrower information to be labeled as 'Borrower1' xyz and 'Borrower2' xyz. Also, there will be a LOT more fields returned on the real form, so the sample information below is very simplified test data. Sample Data:CREATE TABLE #t (LoanID VARCHAR(5), BorrowerName VARCHAR(20), BorrowerOrder INT);GOINSERT INTO #t VALUES ('::E', 'John Smith', 0) , ('::E', 'Jane Smith', 1) , ('::E', 'Rob Jackson', 2) , ('AF_CF', 'Sloan Burton', 1) , ('AF_CF', 'Virginia Burton', 2) , ('AF_CF', 'John Hardeman', 0) , ('::E', 'Mary Lamb', 4) , ('::E', 'Patrick Stewart', 3)So for loan 'AF_CF', the form should print twice:1st copy: Borrower1 = Sloan Burton, Borrower2 = Virginia Burton2nd copy: Borrower1 = John Hardeman, Borrower2 = NULLWe are on 2012, so I tried to do this with LEAD():SELECT Borrower1 = BorrowerName , Borrower2 = LEAD(BorrowerName) OVER(ORDER BY BorrowerOrder)FROM #t AS tWHERE FileID = 'AF_CF'But this returns a record for every borrower, so the form is printing 3 times:1st: Borrower1 = Sloan Burton, Borrower2 = Virginia Burton2nd: Borrower1 = Virginia Burton, Borrower2 = John Hardeman3rd: Borrower1 = John Hardeman, Borrower2 = NULLI don't want that 2nd record to return. This result is what makes me think of gaps and islands, but I don't know if the 2nd record is really an island since it's (1) not stored this way...it's returning this way because of the query and (2) it's not sequential data....Finally, I tried restricting this by putting this into a CTE and then returning only the odd numbered records like I have below. This runs pretty quickly when dealing with one loan. But...I am concerned that the CTE will be slow when we run batches of loans. Attempt with CTE:--With CTE;WITH cte AS (SELECT Borrower1 = BorrowerName , Borrower2 = LEAD(BorrowerName) OVER(ORDER BY BorrowerOrder) , RowNumber = ROW_NUMBER() OVER(ORDER BY BorrowerOrder)FROM #t AS tWHERE FileID = '::E')SELECT *FROM cteWHERE RowNumber % 2 = 1Is there a better, cleaner way to do this? Or is the CTE the best way to go?
Post #1514842
 Posted Sunday, November 17, 2013 5:00 AM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, February 10, 2016 11:50 AM Points: 6,897, Visits: 13,559
 Since you already have the BorrowerOrder column I'm not sure why it would need to be so complicated...Wouldn't a simple BorrowerOrder < 2 be enough?SELECT *FROM #t AS tWHERE BorrowerOrder < 2 LutzA pessimist is an optimist with experience. How to get fast answers to your question How to post performance related questionsLinks for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1514973
 Posted Sunday, November 17, 2013 8:26 AM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 7:26 AM Points: 9,829, Visits: 11,900
 LutzM (11/17/2013)Since you already have the BorrowerOrder column I'm not sure why it would need to be so complicated...Wouldn't a simple BorrowerOrder < 2 be enough?SELECT *FROM #t AS tWHERE BorrowerOrder < 2The trouble with that is that it's only going to return 1 row, so what happens when the file has more than 2 borrowers? Also, the sequence of the values in the BORROWERORDER field may contain gaps, so that may only get one borrower when it should get two. Or even worse, return no borrowers at all.I think the OP's solution with a CTE is probably OK; roughly half of the CTE's rows are excluded from the output, but if the BorrowerOrder values are not guaranteed to be set of integers without gaps (and I imagine they can't be, since if they were there would be no need to use LEAD instead of just adding 1) there is no way to avoid that, and the performance penalty for having the extra data in the CTE is not enormous as all the rows with the right FILEID have to be read whether they are first of a pair or second of a pair. If the table is clustered on a key of which the FILEID is the first column, or there is a suitable covering index for this query where FILEID is the first column, the solution using a CTE will be pretty fast.Of course a slightly different query consisting of a CTE which adds a row number and doesn't combine records and a main select which joins with a tally table might be faster, but it might actually be slower. Probably it would be easy enough to measure and compare the two approaches if there's adequate test data. Tom
Post #1514990
 Posted Sunday, November 17, 2013 10:11 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 10:03 PM Points: 42,077, Visits: 39,461
Post #1515063
 Posted Monday, November 18, 2013 7:48 AM
 Grasshopper Group: General Forum Members Last Login: Friday, August 21, 2015 2:05 PM Points: 12, Visits: 86
 Thank you all for your replies! Tom, you have it correct. There are, at times, gaps in the BorrowerOrder numbers, and there are times when there might be up to 7 or 8 borrowers. Jeff, I apologize! This was my error in creating the test data. For the example resultset I gave, it should have been Sloan - 0, Virginia - 1, John - 2. What do you recommend?
Post #1515232
 Posted Tuesday, November 19, 2013 6:33 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 10:03 PM Points: 42,077, Visits: 39,461
Post #1515859
 Posted Wednesday, November 20, 2013 6:31 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 I can second what Jeff said about performance of LAG/LEAD as I've done some testing myself. Frankly I was not impressed in the admittedly limited testing that I have done, even though they do tend to make the code look simpler (in some cases). In this case, I think what Jeff offered looks pretty simple. At least to me. But perhaps I am not objective as I've used that same technique before. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1516287
 Posted Thursday, November 21, 2013 8:43 AM
 Grasshopper Group: General Forum Members Last Login: Friday, August 21, 2015 2:05 PM Points: 12, Visits: 86
 Ahhh! Thank you so much, Jeff!! I've used crosstab before, but didn't realize that's what that was called. Scary, right? I feel like a kid who drops a perfectly good toy in favor of a shiny new one. Hahaha. This is great and works really well - I appreciate you helping me out. You explained everything so well and thank you for sharing the link to your crosstab/pivot article! I'm sharing this post and the article link with my group at work. And I also appreciate you pointing out the use of the backwards compatible test code to broaden my audience. I'll be sure to do this going forward. Have a great day, everyone!
Post #1516459
 Posted Thursday, November 21, 2013 8:44 AM
 Grasshopper Group: General Forum Members Last Login: Friday, August 21, 2015 2:05 PM Points: 12, Visits: 86
 Thank you, Dwain - good to know!
Post #1516460

 Permissions