Using SQL to select data from last week

Question

What is the best way to select data from the previous week? The below returns a syntax error. SELECT COUNT(dbo.Calls.kbpCallID) NoofoutboundCalls FROM (((dbo.Calls LEFT OUTER JOIN dbo.OrganizationContacts ON dbo.Calls.kbpOrganizationID = dbo.OrganizationContacts.cmcOrganizationID AND dbo.Calls.KbplocationID = dbo.OrganizationContacts.cmclocationID AND dbo.Calls.kbpcontactid = dbo.OrganizationContacts.cmccontactid) LEFT OUTER JOIN dbo.OrganizationLocations ON dbo.OrganizationContacts.cmcOrganizationID = dbo.OrganizationLocations.cmlOrganizationID AND dbo.OrganizationContacts.cmclocationId = dbo.OrganizationLocations.cmllocationid AND dbo.OrganizationContacts.cmccontactid = dbo.organizationlocations.cmlapinvoicecontactid) LEFT OUTER JOIN dbo.Organizations ON dbo.Calls.kbpOrganizationID = dbo.Organizations.cmoOrganizationID) * * WHERE dbo.Calls.kbpOpenedDate = DATEADD(week, - 1,) * * AND (dbo.Organizations.cmoCustomerStatus = 1 OR dbo.Organizations.cmoCustomerStatus = 2) AND dbo.Calls.kbpCallTypeID = 'SALOB'

Answer 1

You haven't completed the syntax for the DATEADD function.

DATEADD requires 3 parameters:

1. The grain of the function - in your case WEEK
2. The number of intervals - in your case -1
3. The column that you are interacting on - in your case this is missing

As it seems to be based on the current date then I think you want that line to be:

`WHERE dbo.Calls.kbpOpenedDate = DATEADD(week, -1, GETDATE())`

However, that also is a specific date and time that you are looking for equality on. More likely you are looking for a range so you may have to use greater than instead of equals or include another DATEADD to define the start/end range along with this one.