Hard to Describe Select Needed (sorry)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Hard to Describe Select Needed (sorry)

Author

Topic

VentureFree
Starting Member

19 Posts

Posted - 2009-12-07 : 12:27:28

I have a table with 2 different date fields and an foreign key ID. One date marks when it was first entered, the other date marks when it was "processed". Here's a representation of the table:

CREATE TABLE MyTable (
	[MyID] [bigint] NOT NULL,
	[ForeignID] [bigint] NOT NULL,
	[EnteredOn] [datetime] NOT NULL,
	[ProcessedOn] [datetime] NULL
)

I'm not entirely sure how to describe what I need. I'll try, though. I need to list only the records with the ForeignID for which ProcessedOn IS NULL, and which has the earliest EnteredOn date of all the ForeignIDs that match that criterion, regardless of whether that earliest record itself has ProcessedOn IS NULL. I'm not sure how to be more precise in my description, so let me give you an example. Given the following data:

INSERT INTO MyTable(MyID, ForeignID, EnteredOn, ProcessedOn)
VALUES(1, 1, '2009-12-01 01:00:00.000','2009-12-01 01:30:00.000')
INSERT INTO MyTable(MyID, ForeignID, EnteredOn, ProcessedOn)
VALUES(2, 2, '2009-12-01 02:00:00.000','2009-12-01 02:30:00.000')
INSERT INTO MyTable(MyID, ForeignID, EnteredOn, ProcessedOn)
VALUES(3, 3, '2009-12-01 03:00:00.000',NULL)
INSERT INTO MyTable(MyID, ForeignID, EnteredOn, ProcessedOn)
VALUES(4, 1, '2009-12-01 04:00:00.000','2009-12-01 04:30:00.000')
INSERT INTO MyTable(MyID, ForeignID, EnteredOn, ProcessedOn)
VALUES(5, 2, '2009-12-01 05:00:00.000',NULL)
INSERT INTO MyTable(MyID, ForeignID, EnteredOn, ProcessedOn)
VALUES(6, 3, '2009-12-01 06:00:00.000',NULL)
INSERT INTO MyTable(MyID, ForeignID, EnteredOn, ProcessedOn)
VALUES(7, 1, '2009-12-01 07:00:00.000','2009-12-01 07:30:00.000')
INSERT INTO MyTable(MyID, ForeignID, EnteredOn, ProcessedOn)
VALUES(8, 2, '2009-12-01 08:00:00.000',NULL)
INSERT INTO MyTable(MyID, ForeignID, EnteredOn, ProcessedOn)
VALUES(9, 3, '2009-12-01 09:00:00.000',NULL)

I need a select statement that would return the following records:

MyID  ForeignID  EnteredOn                ProcessedOn
5     2          2009-12-01 05:00:00.000  NULL
8     2          2009-12-01 08:00:00.000  NULL

The reason it should get the ones with ForeignID = 2 instead of ForeignID = 3 is because even though 3 has the earliest EnteredOn where ProcessedOn IS NULL, 2 has the earliest EnteredOn where at least one record with ForeignID = 2 has ProcessedOn IS NULL, even though that specific record doesn't have ProcessedOn IS NULL itself.

I hope I was clear enough here. Normally I figure these things out as I type out a post because describing it helps me figure it out. This time I don't think I can describe what I need well enough to figure it out.

Things should be made as simple as possible, but not any simpler - Albert Einstein

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-07 : 13:14:56

Im sorry you may have to be more clear. I do not understand your logic in not picking ForeignID 3, but picking 2 lines with ForeignID = 2?

You seem to want uniqueness on ForeignID, but still want back multiple values for it? Did you accept '2' because there was a ProcessedOn date that was NOT NULL?

VentureFree
Starting Member

19 Posts

Posted - 2009-12-07 : 13:33:11

Actually I think I've figured it out. It seems to work with the data that I provided. Next I'm going to try it with my real data. Here's the code that I came up with

SELECT MyTable.*
FROM MyTable INNER JOIN
(
	SELECT TOP 1 EarliestEntries.ForeignID
	FROM 
	(
		SELECT MIN(EnteredOn) AS Earliest, ForeignID
		FROM MyTable
		GROUP BY ForeignID
	) EarliestEntries
	INNER JOIN 
	(
		SELECT EnteredOn, ForeignID
		FROM MyTable
		WHERE ProcessedOn IS NULL
	) UnprocessedEntries
	ON EarliestEntries.ForeignID = UnprocessedEntries.ForeignID
	ORDER BY EarliestEntries.Earliest
) EarliestWithUnprocessed
ON MyTable.ForeignID = EarliestWithUnprocessed.ForeignID
WHERE ProcessedOn IS NULL

I think it comes down to the fact that the two dates involved were interdependent. I had to know which ProcessedOn dates were NULL to know which EnteredOn dates to look at, and I needed to know which EnteredOn dates were the "earliest" to know which ProcessedOn dates to look at. I'm not sure that this is the most efficient way to do it, but it works.

Sorry that I couldn't describe what I needed more clearly. I'm still not sure that I could do any better, but if you analyze the code I think it demonstrates what I wanted.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-07 : 13:40:56

Won't you still get ForeignKey 3 back?

VentureFree
Starting Member

19 Posts

Posted - 2009-12-08 : 06:41:23

Nope. The "EarliestEntries" subquery finds the earliest entries for each of the foreign keys regardless of whether that entry is processed or not. The "UnprocessedEntries" subquery finds those foreign keys which have any unprocessed entries regardless of when it's earliest entry is (by the way, I just realized that selecting EnteredOn in the UnprocessedEntries subquery is unnecessary, so I struck it in the comment). By joining those two I can dictate that I want the earliest entry that has at least one unprocessed entry. This means that ForeignID = 2 would be selected because it's earliest entry is before the earliest ForeignID = 3 entry (remember, "EarliestEntries" ignores whether that specific entry was processed or not).

I hope this makes sense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources