Link to home
Start Free TrialLog in
Avatar of erikTsomik
erikTsomikFlag for United States of America

asked on

sql help

I need to write a query to issue reminders on the first day of each month
Avatar of Bill Prew
Bill Prew

Not sure exactly what you mean.  A SQL query will only tell you some data, it can't run constantly and pop up an alarm on the first day.  So you just fashion a simple query that returns the day of the month and when it is 1 you take the action you want.

SELECT DAY(CURDATE());

Open in new window

or

SELECT CASE DAY(CURDATE()) WHEN 1 THEN "FIRST DAY" ELSE "" END;

Open in new window

~bp
Easiest is to create a job that runs on the first day of the month (or late on the last day of the month for the next month).

Or, if you need to check for first day yourself in code, you can do this:

--I'm using a variable in case the code ever needs run on a different date.

DECLARE @run_date datetime
SET @run_date = GETDATE()

IF DAY(@run_date) = 1
BEGIN
    ...run code to do notifications..
END /*IF*/
Avatar of erikTsomik

ASKER

Let say am dealing with the cars management. I need to send out a reminder on the first day of each month. The are also conditions around it.

Such I may be able to say send reminder 1 week before the first day of the month OR 2 days before the first day of the month

SELECT * 
FROM car C
inner join carActivities CA on ca.carKey = C.carKey
 left outer join Location L on L.locationKey = C.locationkey
 inner join lkup_carActivity LCA on LCA.activityID = CA.activityID
WHERE C.carActiveIn = 1 and CA.activityID = 5

Open in new window

Hi,

So what is your question in the last comment. Do you want SQL Server to send email for you.., Do you want to use SQL Job? SSIS package etc?

Please provide more info....
Neither, I just want the sql query that will tell when the reminders are due to be send out
In that case i think you should send the reminders a week before the car needs to serviced(for example). Lets say my next service due date it 15th march 2017. So I should an email on 7 march saying your car needs to serviced on 15th march 2017.

Basically for each customer you will different day to send reminder if their service is due to different days of course.

Also you need to provide columns from these table to write the SQL query.

Hope it helps!
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
We'll need to know which column is the reminder and what's the datatype but imagine that column is ReminderDate and is a DATE column:
SELECT * 
FROM car C
    INNER JOIN carActivities CA on ca.carKey = C.carKey
    LEFT OUTER JOIN Location L on L.locationKey = C.locationkey
    INNER JOIN lkup_carActivity LCA on LCA.activityID = CA.activityID
WHERE C.carActiveIn = 1 AND CA.activityID = 5 AND 
    ReminderDate > GETDATE() AND DAY(ReminderDate)=1

Open in new window

But this query won't run alone so you'll need a mechanism to fire up the query and the best is a SQL Agent job where you can define one or more schedules (every week, every month, ....) for it to run.