Help me build a SQL select statement

Question

SQL isn't my greatest strength and I need some help building a select statement.

Basically, this is my requirement. The table stores a list of names and a timestamp of when the name was entered in the table. Names may be entered multiple times during a week, but only once a day.

I want the select query to return names that were entered anytime in the past 7 days, but not today.

To get a list of names entered today, this is the statement I have:

Select * from table where Date(timestamp) = Date(now())

And to get a list of names entered in the past 7 days, not including today:

Select * from table where (Date(now())- Date(timestamp) < 7) and (date(timestamp) != date(now()))

If the first query returns a set or results, say A, and the second query returns B, how can I get

B-A

p.campbell · Accepted Answer · 2011-01-05 22:06:11Z

4

Try this if you're working with SQL Server:

 SELECT * FROM Table
 WHERE   Timestamp BETWEEN  
        dateadd(day,datediff(day,0,getdate()),-7), 
    AND dateadd(day,datediff(day,0,getdate()),0)

This ensures that the timestamp is between 00:00 7 days ago, and 00:00 today. Today's entries with time greater than 00:00 will not be included.

answered Jan 5, 2011 at 22:06

p.campbell

101k70 gold badges264 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joe Stefanelli Over a year ago

I could be wrong but, as I read the question, the requirement is to exclude names that were entered in the previous 7 days and also entered today.

p.campbell Over a year ago

@Joe: I went back to the question as well. "...return names that were entered anytime in the past 7 days, but not today"

Ayush Over a year ago

Joe was right in his interpretation of my question. However, when i went back to read my question, I do realized I worded it ambiguously. Sorry, and thank you for trying to help anyways.

Joe Stefanelli · Accepted Answer · 2011-01-05 22:06:41Z

3

In plain English, you want records from your second query where the name is not in your first query. In SQL:

Select * 
    from table 
    where (Date(now())- Date(timestamp) < 7) 
        and (date(timestamp) != date(now()))
        and name not in (Select name 
                             from table 
                             where Date(timestamp) = Date(now())
                        )

answered Jan 5, 2011 at 22:06

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Comments

Beth · Accepted Answer · 2011-01-05 22:03:52Z

1

not in

like

select pk from B where PK not in A

or you can do something like

Select * from table where (Date(now())- Date(timestamp) < 7) and (Date(now())- Date(timestamp) > 1)

answered Jan 5, 2011 at 22:03

Beth

9,6171 gold badge26 silver badges44 bronze badges

2 Comments

Ayush Over a year ago

The following query returns all names entered from day D-1 to day D-7. But from this set of results, I still need to exclude all results that may have been entered on day D. Thank you for trying, though.

Beth Over a year ago

wasn't sure which way you meant. why would they want to exclude names just because they were entered today? but since they do, then you need to use NOT IN, like in Joe's example.

Collectives™ on Stack Overflow

Help me build a SQL select statement

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related