Easy puzzle, complicated query

Question

Easy puzzle, complicated query

rourrourlogan

SSC Enthusiast

Points: 199
More actions
January 27, 2016 at 11:53 am

#315328

Hello fellow SQL artists,
I want to know the lines that had a line expire and NO new lines took its place. What's a new line? When a lines DateIssued is equal to the expiration date AND the business is the same.
Here's an example of the table I'm looking at.
UniqID | Date Issued | Date Expires | Business
1...........1/1/2010..........1/1/2011.....Commerical
2...........1/1/2011..........1/1/2012.....Commercial
3...........1/1/2011..........1/1/2012.....Medical
Any advice?

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61152 More actions · Answer 1

Can you explain what you mean by "line" and perhaps show us what the correct output would be for the sample data that you posted.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

rourrourlogan SSC Enthusiast Points: 199 More actions · Answer 2

Yeah no problem

UniqID | Date Issued | Date Expires | Business

1...........1/1/2010..........1/1/2011.....Commerical

2...........1/1/2011..........1/1/2012.....Commercial

3...........1/1/2011..........1/1/2012.....Medical

The correct output would be the count of Ids that don't have any lines replacing them.. essentially a renewed business and I'm looking for ones that DIDNT renew. So in the example above (edited) lines 2 and 3 would meet this condition because... there are no other lines that have an issued date of 1/1/2012 AND the BUSINESS would HAVE to be medical for line 3 or COMMERCIAL for line 2 in order for it to be what i'm looking for. Kind of confusing, but I hope it helps Im stumped.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

rourrourlogan (1/27/2016)
Yeah no problem
UniqID | Date Issued | Date Expires | Business
1...........1/1/2010..........1/1/2011.....Commerical
2...........1/1/2011..........1/1/2012.....Commercial
3...........1/1/2011..........1/1/2012.....Medical
The correct output would be the count of Ids that don't have any lines replacing them.. essentially a renewed business. So in the example above the only line that would meet this condition is line 3 because... there are no other lines that have an issued date of 1/1/2012 AND the BUSINESS would HAVE to be medical in order for it to not count. Kind of confusing, but I hope it helps Im stumped.

Hmmm...why wouldn't row 2 be returned, in that case? There is no row with a Date Issued of 1/1/2012 and a business of Commercial.

Cheers!

rourrourlogan SSC Enthusiast Points: 199 More actions · Answer 4

You're correct, i'm sorry. I was thinking of an example where technically that line couldnt have expired yet because the date hadnt past yet, but yes line 2 would also technically fit this criteria.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

You should try to search for articles about the problems defined as "gaps and islands". There are plenty of them on the internet which might help you to understand the methods used and apply them to your particular issue.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

If I'm understanding the requirements correctly, this should be a bit simpler than all that. Maybe something like this?

CREATE TABLE #test (UniqID int,

DateIssued date,

DateExpired date,

Business varchar(20));

INSERT INTO #test VALUES

(1,'20100101','20110101','Commercial'),

(2,'20110101','20120101','Commercial'),

(3,'20110101','20120101','Medical'),

(4,'20110101','20170101','Medical'),

(5,'20140101','20150101','Real Estate'),

(6,'20150101','20160101','Real Estate'),

(7,'20150601','20160101','Real Estate');

SELECT *

FROM #test AS outer_ref

WHERE DateExpired<GETDATE() AND

NOT EXISTS (SELECT 1

FROM #test AS inner_ref

WHERE inner_ref.Business=outer_ref.Business AND

inner_ref.DateIssued=outer_ref.DateExpired

);

DROP TABLE #test;

For future reference, it helps us out a lot if you can provide sample data in the form above, with CREATE TABLE and INSERT statements, along with the desired results. Being able to just copy/paste the sample data code lets us test solutions much more easily.

Cheers!