select items based on both before and after dates

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 2012 Forums

Transact-SQL (2012)

select items based on both before and after dates

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2013-05-16 : 10:26:03

Hi,

Imagine a table like this:


    create table #productPurchases
    (	intRowId int identity(1,1),
		intItemId int,
		datPurchaseDate datetime
	)

It holds a record of items purchased and the date they were purchased. How would I write a query to identify items that had been purchased both before *and* after a certain date?

Tried this. Which didn't work, and bought back rather more records than I was expecting, mostly duplicates (the real table is a lot bigger) - but it illustrates what I'm trying to do.


SELECT TOP 1000 *
  FROM #productPurchases e1
  join #productPurchases e2
  on e1.strItemId = e2.strItemId
  where e1.datPurchaseDate> '2013-05-09 00:00:01'
  and e2.datPurchaseDate  < '2013-05-09 00:00:01'

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-16 : 10:54:53

Try just this (without join and different dates in the date range):
[CODE]
SELECT TOP 1000 *
FROM #productPurchases e1
where e1.datPurchaseDate > '2013-05-10 00:00:01'
and e1.datPurchaseDate < '2013-05-09 00:00:01'
[/CODE]

mattt
Posting Yak Master

194 Posts

Posted - 2013-05-16 : 10:57:41

quote:
Originally posted by MuMu88

Try just this (without join and different dates in the date range):
[CODE]
SELECT TOP 1000 *
FROM #productPurchases e1
where e1.datPurchaseDate > '2013-05-10 00:00:01'
and e1.datPurchaseDate < '2013-05-09 00:00:01'
[/CODE]

Thanks for the suggestion. But this returns nothing at all. And I'm certain there's data in that table which matches the requirement.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-16 : 11:01:54

Change the AND with OR in your condition.

Another way would be to say
WHERE Date <> 'the date'

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources