December 7, 2015 at 10:39 am
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
December 7, 2015 at 11:43 am
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!
December 7, 2015 at 12:03 pm
thanks! Let me play with that and see if it works as I'd expect
December 7, 2015 at 12:12 pm
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://qa.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!
December 7, 2015 at 1:49 pm
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
December 7, 2015 at 2:19 pm
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!
December 7, 2015 at 2:38 pm
COl1 Col2 Col3
RE44528 INfringed 2015-03-27 00:00:00.000
RE44528 Not infringed 2015-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
December 7, 2015 at 2:46 pm
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
December 7, 2015 at 3:00 pm
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://qa.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!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply