Solved

SQL Query Alert 30 Day Reminder

Posted on 2014-10-10
5
263 Views
Last Modified: 2014-11-24
I have a SQL Database that is back end to a program we use for accounting and business mgt.  Inside the program, we can write multiple alerts based on query's of the data stored in SQL.  The current query i have written displays an alert when someone has a birthday.

set transaction isolation level read uncommitted
SELECT
AlertMessage = 'Happy Birthday '+firstname+' '+lastname+' !!'
FROM dbo.Employees
WHERE DATEPART(d, birthdate) <= DATEPART(d, GETDATE())
AND DATEPART(m, birthdate) = DATEPART(m, GETDATE())

I would like to modify it so that it shows the alert 30 days before the birthday is going to happen.
0
Comment
Question by:thirdrockit
  • 3
5 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 40373960
SELECT 
AlertMessage = ' 30 days before you wish Happy Birthday '+firstname+' '+lastname+' !!'
FROM dbo.Employees
WHERE DATEPART(d, birthdate) <= DATEPART(d, GETDATE() - 30)
AND DATEPART(m, birthdate) = DATEPART(m, GETDATE() )

Open in new window

0
 

Author Comment

by:thirdrockit
ID: 40373984
after running it, it shows 2 people who had a birthday yesterday 10/9.  If there birthday has already passed, should the alert still run?
0
 

Author Comment

by:thirdrockit
ID: 40373999
In other words, it needs to alert for 30 days and then on the day, don't alert anymore.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40374001
As usual with birthdays, leap birth days are a pita.  Not sure how this code handles those.

For exactly 30 days ago only:

WHERE
    birthdate = DATEADD(DAY, +30, DATEADD(YEAR, -(YEAR(GETDATE()) - YEAR(birthdate)), CAST(GETDATE() AS date))) AND
    GETDATE() < DATEADD(DAY, 1, DATEADD(YEAR, YEAR(@current_date) - YEAR(birthdate), CAST(birthdate AS date)))
0
 

Author Comment

by:thirdrockit
ID: 40377877
I declared @current_date as integer.  The query ran with no errors but it doesn't give me any results.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question