CODEWITHSUNDEEP
sql
user1022585
user1022585

Reputation: 13651

SQL query exclude phrasing

I'm having trouble with a SQL query, I can't think how to phrase it.

It's to pull a count of events from a log since you last checked it.

Currently I have this:

SELECT COUNT(*) AS alertcount, date AS alertdate 
FROM alerts WHERE to_id = '$id' AND date > '$lastcheck'

But, I want it to exclude all fields where expire=1 and the date is over 30 mins old.

So if both of them expressions are true, it will ignore that record in the count.

Upvotes: 2

Views: 267

Answers (4)

Diego
Diego

Reputation: 36146

SELECT COUNT(*) AS alertcount, date AS alertdate 
FROM alerts 
WHERE to_id = '$id' AND (date between '$lastcheck' and getdate() -30 minutes ) and expire!=1

I dont know your DB engine but if is SQL Server, use the dateadd function to calculate the getdate() -30 minutes expression

EDIT: here is the condition with your new comment. not sure where the to_id will fit in this scenario and also you dont need a date parameter

WHERE ((expire=0) OR (date > getdate() -30 minutes and expire=1) )

Upvotes: 1

Massimiliano Peluso
Massimiliano Peluso

Reputation: 26737

SELECT COUNT(*) AS alertcount, date AS alertdate 
FROM alerts WHERE to_id = '$id' AND date > '$lastcheck'
AND(expire<>1 AND DateDiff(MINUTE, [date]  , GETDATE())>30)

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

I think you are looking for HAVING, something like:

SELECT COUNT(*) AS alertcount, date AS alertdate 
FROM alerts 
WHERE to_id = '$id' AND date > '$lastcheck'
HAVING expire = 1 and DATEADD(minute, -30, GETDATE()) < yourDateField

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726579

This should work (I am using SQL Server date-time functions)

SELECT
    COUNT(*) AS alertcount
,   date AS alertdate
FROM alerts
WHERE to_id = '$id' AND date > '$lastcheck'
AND (expire <> 1 OR dateadd(minute, -30, getdate()) < date)

Upvotes: 1

Related Questions