erikTsomik
asked on
sql help
I need to write a query to issue reminders on the first day of each month
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*/
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*/
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
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
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....
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....
ASKER
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
Open in new window
orOpen in new window
~bp