Link to home
Create AccountLog in
Avatar of Mark Wilson
Mark Wilson

asked on

Eliminating Data

I am using SQL 2008

I have the following data in a table

Matter      ReceivedDate
125      2013-02-14
125      2012-05-01
125      NULL
125      NULL
126      NULL
126      NULL

I only want to matters where there is no date in the Received Date for that matter

In the above example it would be 126

Is there a way to do this?

Thanks
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

SELECT DISTINCT matter FROM YourTable WHERE ReceivedDate IS NULL
>I only want to matters
The above selects only the Matter value where there is a NULL in ReceivedDate.

Do you wish to only SELECT this, or do you want to delete the rows in the table where there is a Matter and NOT NULL in ReceivedDate?

'to matters' doesn't completely spell out what the question is.
Avatar of Mark Wilson
Mark Wilson

ASKER

Thanks for the answers

To clarify I only want to bring back matters where the Received Date is null and there are not received dates associated with that matter

Matter 125 has two received dates and two nulls associated with it so therefore does not meet the criteria
Matter 126 has two nulls and no received dates associated with it, therefore does meet the criteria

I do not want to delete any rows in the table
Got it.  In that case, copy-paste the below code into your SSMS, run it to verify it does what you want, then modify to fit your situation.

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE #tmp

CREATE TABLE #tmp (matter int, receiveddate datetime)

INSERT INTO #tmp (matter, receiveddate)
VALUES (125, '2013-02-14'), (125, '2012-05-01'),(125, NULL), (125, NULL), (126,  NULL),(126, NULL)

SELECT a.matter 
FROM (SELECT matter, Max(receiveddate) as receiveddate_max FROM #tmp GROUP BY matter) a
WHERE a.receiveddate_max IS NULL

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account