• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

SQL Query to find row appearing one day, but not other

Hello Experts,

lets say I had the following table.  What query would tell me all Names that appear on 1/1/2008, but not on 1/2/2008 (in this case, Jane).  I am using SQL Server 2005

Date              Name
1/1/2008       John
1/1/2008       Jane
1/2/2009       John
...
0
sfun28
Asked:
sfun28
  • 3
  • 2
1 Solution
 
brad2575Commented:
Assumign you have an ID field somewhere in this table and you know the dates ahead of time it would be this

Select *
From TableName
where Date = '1/1/2008'
AND TableID NOT IN (

  Select TableID  
  From TableName
  where Date = '1/2/2008'

)
0
 
louislietaerCommented:
hello,

this one may help to, it will select all name that are unique

select flag,nname,ndate from (select count(name) as flag,name as nprod, date-app as ndate from test group by name) as myquery where flag = 1

you coud add easily at the end a selection on ndate if needed
0
 
sfun28Author Commented:
Unfortunately I don't have IDs...just the table as shown.

could you provide the full syntax for this query?

louislietaer - i couldn't quite understand how I would use what you suggested.  Could you provide  that in the full query?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
louislietaerCommented:
sorry they was an error (nprod was for my tests)

You table name is 'test'
fields are 'name' and 'date-app'

select flag,nname,ndate from (select count(name) as flag,name as nname, date-app as ndate from test group by name) as myquery where flag = 1

if you want a date selection

select flag,nname,ndate from (select count(name) as flag,name as nname, date-app as ndate from test group by name) as myquery where flag = 1  and ndate='01/01/2008'


0
 
sfun28Author Commented:
this variation on brad's solution works

Select [Name]
From TableName
where Date = '1/1/2008'
AND [Name] NOT IN (

  Select [Name]
  From TableName
  where Date = '1/2/2008'

)
0
 
louislietaerCommented:
Mine work to ;)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now