how to write querry for this month,3months ,6months,annual

[developerit]

hi iam using sql server 2000 my table is branch BranchName nvarchar(50), ContractNo nvarchar(50), Email nvarchar(50), StartDate datetime, EndDate datetime, ContractReminder int,(5days,10days,15days) remindertype int(1month,3month,6month,annual) i have to send the email to the user before enddate and check the ContractReminder if it is for 5 days then send before 5days ,if it is for 10days then before 10 days and also check the remindertype if it is for one then before one month before contractreminder ie 5 days or 10 days i have to send the mail like this for three months, 6 months , and for annually how to write this querry can you give an idea which helps me

[Corporal Agarn]

It looks like you need a job to run every day that by using two datediff criteria using an OR between them. This could then get the list that is sent to. Sorry I cannot be more specific as I am installing SQL 2008 R2 on my system, so I cannot try an example. See BOL for Datediff and you will use days I would suggest using days for the month also as that would prevent the letter from being sent every day of the month.

SELECT * FROM branch
WHERE DATEDIFF('dd', GETDATE(), EndDate) = ContractReminder
OR DATEDIFF ('dd', GETDATE(), EndDate) = ReminderType * 30

NOT TESTED Good Luck, djj

[developerit]

hi iam using this querry SELECT BranchName,ContractNo, Email,StartDate,EndDate, ContractReminder, DATEADD(mm,ReminderType,StartDate)-ContractReminder as newd it works fine for the first time only for 1 month, 3months,or 6months, suppose the starttime is 2010-01-01 and enddate is 2010-12-31 the querry will works fine for only first time for 1st month, or 3rd month, or 6th month but i want the querry to check upto enddate repeatedly ie for a year i want the querry to be executed 12 times, if it is for 3 months the querry should be executed for 4 times in a year how can i get this querry please give suggesstion

[Simon_Whale]

try something like this. *assumption that all contract end dates are in the future

select branchname, contractno, email, startdate, enddate, contractreminder, remindertype
from branch
where convert(varchar(20), enddate,112) = convert(varchar920), dateadd(dd, contractreminder, current_timestamp),112)
-- this will then removes the issues of the times in the datetime column

Marc Clifton wrote:

That has nothing to do with VB. - Oh crap. I just defended VB!

[Corporal Agarn]

A suggestion would be

SELECT BranchName, ContractNo, Email, StartDate, EndDate,
CASE
-- notice that the 101 is my favorite see BOL Convert for list of others
WHEN CONVERT(VARCHAR, DATEADD(day, ContractReminder, GETDATE()), 101) > EndDate
THEN 'Less than ' + CAST(ContractReminder as VARCHAR) + ' days left'
WHEN CONVERT(VARCHAR, DATEADD(month, remindertype, GETDATE()), 101) > EndDate
THEN 'Less than ' + remindertype + ' months left'
END AS MyFlag
INTO #RList
WHERE CONVERT(VARCHAR, DATEADD(day, ContractReminder, GETDATE()), 101) > EndDate
OR CONVERT(VARCHAR, DATEADD(month, remindertype, GETDATE()), 101) > EndDate