Coupla SQL [Enterprise Manager] questions. queries on 'yesterday' or 'last 7 days'

Q1 how do I write a query, that no matter what day it’s run it will always filter on a date field with yesterday’s date, or the last seven days?
Q2 Is there a simple way of implementing user input in a query?

In other words could I create a view, which my less tech-savvy colleagues could run in microsoft excel given an account number? and the query would filter by that account number.

A lot of this can be done using the getdate, dateadd, and datepart functions. For instance:

dateadd(d, -1, getdate()) will return the date and time exactly one day ago from this moment.

where datediff(d, fldDate, getdate()) = 1 will filter for all records where fldDate is yesterday… ie from midnight yesterday, up to one instant before midnight.

where datediff(d, fldDate, getdate()) between 0 and 7 will filter for all records where the date is between one week ago today and today, inclusively.

Hope that these help.