Find Records from Previous X Days

find records from previous x days?

SELECT *
FROM Table
WHERE GETDATE() >= DATEADD(DAY, -30, GETDATE())

Substitute the first GETDATE() with the appropriate column name.

SELECT *
FROM Table
WHERE Table.ColumnName >= DATEADD(DAY, -30, GETDATE())

Best Way to Find Records Created or Modified in the Past X Days?

You didn't say what database engine you're using. Here's solution that should work with SQL Server:

SELECT *
FROM Accounts
WHERE DATEADD(d, 5, CreateDate) >= GETDATE()
OR DATEADD(d, 5, UpdateWhen) >= GETDATE()

It uses DATEADD function which allows you to add different time intervals to DateTime values. In this example you add 5 days (that's what d is for as first parameter).

You could do it the other way: add -5 days to GETDATE() and compare with UpdateWhen and CreateDate directly:

CreateDate >= DATEADD(d, -5, GETDATE())

Pick one that you think is more readable and easier to understand.

Getting all SQL Server database records older than X days

Break down your query bit by bit.

On the face of it this bit looks fine

select * FROM [dbo].websiteTestLocation wtl
LEFT JOIN [dbo].website w ON w.websiteRecordId = wtl.websiteRecordId

But that next JOIN is all over the place. It looks like you are trying to limit a LEFT OUTER JOIN to just the records that are in table website

LEFT JOIN [dbo].websiteSnapshot snap ON snap.websiteSnapshotId IN (w.websiteSnapshotId)

Use an INNER JOIN instead and this ON clause

ON snap.websiteSnapshotId = w.websiteSnapshotId

Next your WHERE clause - which is where the problem you report comes from. You are trying to match a single value to a list of values, that will never work. All of this

WHERE (SELECT (websiteSnapshotId)
    FROM [dbo].websiteSnapshot
    WHERE websiteSnapshotStartTime IN (
        SELECT 
            (websiteSnapshotStartTime)
        FROM
            [dbo].websiteSnapshot
        WHERE
            websiteSnapshotStartTime < DATEADD(day, -1, GETDATE())
    )) = snap.websiteSnapshotId

can be replaced with

WHERE websiteSnapshotStartTime < DATEADD(day, -1, GETDATE())

Selecting a record because its date field is X days after another records date field SQL

You can unpivot the table with union all to generate one row per game and team, then use lag() to get the date of the "previous" game, and finally use that information to filter:

select gd.*
from (
    select gd.*, lag(date) over(partition by team order by date) lag_date 
    from (
        select gd.*, ateam team from gamedata gd
        union all
        select gd.*, hteam team from gamedata gd
    ) gd
) gd
where date > lag_date + interval 2 day

SQL: How do I find records that are within X days of one another?

If you don't want to restrict the join to department, why do you have that as a join criteria?

Also, you are only returning rows that have exactly 2 days difference:

WHERE e2.hire_date = e1.hire_date + INTERVAL '2 days'

but you need to include everything between the same date and 2 days out:

WHERE e2.hire_date between e1.hire_date and e1.hire_date + INTERVAL '2 days'
AND e2.employee_id != e1.employee_id

The added condition on employee_id prevents rows from joining to themselves.

Replace the join condition on department with the above condition:

SELECT
...
FROM employees AS e1
JOIN employees AS e2 ON e2.employee_id != e1.employee_id
  AND e2.hire_date between e1.hire_date and e1.hire_date + INTERVAL '2 days'

How do I mark ids that have had occurrences in the past X days? SQL Server

Using LAG to see if the date is exactly 10 days from the previous, per ID. Change to <= if you want it for the "range"

select
   id
   ,date
   ,Marker = case when dateadd(day,-10,[Date]) = lag([Date]) over (partition by id order by [Date]) then 1 else 0 end 
from YourTable
order by id, [Date]

---

Related Topics