How to compare one value with a specific date, and find if there are any other same values with a greater date

Question

Post reply

How to compare one value with a specific date, and find if there are any other same values with a greater date

taseedorf

SSC Eights!

Points: 917
More actions
December 7, 2015 at 10:39 am

#313733

The issue is this.
I have a list of values in columns like..
AA infringed 1/1/2015
AA not infringed 1/2/2015
BB infringed 1/5/2015
BB not infringed 1/5/2015
CC infringed 1/10/2015
CC not infringed 1/01/2015
CC not infringed 01/10/2015
I need to find something like
Return the values in column 1 (aa, bb, etc) in which the column 2 value is “not infringed” AND column 1 dpes not have “infringed” on a later or the same date
Confused on how to go about this and I end up using temp tables and bouncing around to get my values - wondering if there is a query that can handle this without moving into temp tables, etc

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

Perhaps something like this?

CREATE TABLE #Temp (code CHAR(2), code_status CHAR(20), code_date DATE);

INSERT INTO #Temp

VALUES

('AA','Infringed', '20150101'),

('AA','Not Infringed', '20150102'),

('BB','Infringed', '20150105'),

('BB','Not Infringed', '20150105'),

('CC','Infringed', '20150110'),

('CC','Not Infringed', '20150101'),

('CC','Not Infringed', '20150110');

SELECT code FROM #Temp

GROUP BY code

HAVING MAX(CASE WHEN code_status='Not Infringed' THEN code_date ELSE '00010101' END)

>

MAX(CASE WHEN code_status='Infringed' THEN code_date ELSE '00010101' END);

DROP TABLE #Temp;

Cheers!

taseedorf SSC Eights! Points: 917 More actions · Answer 2

thanks! Let me play with that and see if it works as I'd expect

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

The SELECT I have in there is code to do it without a temp table.

I create a temporary table and populate it with your sample data so it is in a readily consumable format. That way, other contributors can easily create a table on their instances with your sample data and test solutions. It's just a good practice, because if we only have data in text format, it's hard to test solutions. See http://www.sqlservercentral.com/articles/Best+Practices/61537/ for some more information on that.

Modify the SELECT statement I provided to use your actual table and and actual column names, and see if it does what you need.

Cheers!

EDIT: I see while I was typing this, you came to the same conclusion and modified your post. I hope it helps!

taseedorf SSC Eights! Points: 917 More actions · Answer 4

Well, it doesn't seem to work, for two reasons

One, it will show a result even if there is another results on or after the date. for example,

if I have a not infringed status on 10/01/2015 and also an infringed status on 10/01/2015, i DONT want to see not infringed, but it is showing.

Also, it seems in some cases, when there are only two results, it is showing a result even though it should only show when the date is LARGER,... it will show if the dates are equal

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

Your adaptation of the query to your actual data must have changed it, then, because the query I provided does not behave that way with your sample data.

You'll have to provide the query you're using, an example of data on which it fails, and expected results for me to know any more.

Cheers!

taseedorf SSC Eights! Points: 917 More actions · Answer 6

COl1 Col2 Col3

RE44528INfringed2015-03-27 00:00:00.000

RE44528Not infringed2015-03-27 00:00:00.000

I would expect your query to show no result, since they have the same date...

Rather than the binary, I used a date...SQL would not accept your varchar data for date

taseedorf SSC Eights! Points: 917 More actions · Answer 7

AWh, I have another column in the select, and therefore the group by.... that seems to affect it, but how can I use more than one column - for example, say I want another column that has a name or something to be shown..and STILL get this query to work

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

Those dates in my earlier query weren't binary.

Since you didn't say what data types any of the columns were and your sample data only showed dates (no times), I created the temp table using the date data type. The date I used in my CASE statement is the minimum value for the date data type, but will be out of range for the datetime data type (which it now seems you're using, since your most recent sample data added a time component).

That's why following the information in the article I linked in my earlier response is so important (here it is again: http://www.sqlservercentral.com/articles/Best+Practices/61537/).

If we don't know all the columns and data types for your data, we'll have to guess, and our solutions might not work with your actual data if our guesses about data types are wrong, or if you want to return columns you didn't tell us you wanted (in this case, you initially said you just wanted to return values from column 1).

Follow the instructions in that article by giving us DDL for your table, sample data in the form of INSERTs, and the expected result; we'll be better able to help you out then.

Cheers!