|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
I have to search keyword "searching"
Out of this keyword i have to find any 8 characters in the following string
"string to search" .
Can u suggest any approach ?
|
|
|
|
|
look at the patindex function.
Marc Clifton wrote: That has nothing to do with VB. - Oh crap. I just defended VB!
|
|
|
|
|
Hi..
please solve my prob i try this query
select PRODUCTID,u_price, max(qc_dt) dt
from qc where PRODUCTID=1011
group by PRODUCTID,u_price
order by PRODUCTID
result of this query is like
productid.,u_price.,dt
1011......6.00.....2010-01-27 00:00:00.000
1011......5.50.....2010-04-27 00:00:00.000
but i want to show the max date record like
productid.,u_price.,dt
1011......5.50.....2010-04-27 00:00:00.000
please tell me the query
hope you understand
i am waiting ur reply.
|
|
|
|
|
Try this
select top 1 PRODUCTID,u_price, max(qc_dt) dt
from qc where PRODUCTID=1011
group by PRODUCTID,u_price
order by qc_dt
|
|
|
|
|
thanks this query is working for me 
|
|
|
|
|
 when i change the productid query show old record but i want to show latest u_price of product.
|
|
|
|
|
try
select top 1 productid, u_price, qc_dt
from (select productid, u_price, qc_dt
from qc
where productid = 1011
order by qc_dt desc) z
Marc Clifton wrote: That has nothing to do with VB. - Oh crap. I just defended VB!
|
|
|
|
|
I have a table that has a PK index on a VARCHAR(10) field. I insert 'A'. Next I try to insert 'A '. The insert isn't allowed because of the index. How can I make it so that 'A ' is allowed to be inserted?
Thank you in advance.
EDIT: The first is just A. The second was A[space].
modified on Monday, May 3, 2010 10:35 AM
|
|
|
|
|
yeah, primary keys are a nuissance; they have a habit of wanting to be unique. Remove the old 'A' before you insert a new 'A'.
|
|
|
|
|
The first is just A. The second was A[space].
|
|
|
|
|
If you genuinely need to add a second 'A' (rather than just trying to find out what happens if you do), then the chances are that the design of your database is less than optimal.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
yes, there is an issue with trailing spaces; here[^] is one discussion of that topic. I would avoid such situations by trimming all text before it goes into the database, otherwise you'll get trouble sooner or later, as you can hardly see the spaces at all and they may cause your selections to behave inconsistently.
|
|
|
|
|
Too slow Pattyn! 
I still think it's in the wrong place. Shouldn't it be a reply to the OPs' second post?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
As he modified the OP, my answer stands; no need to demote it...
|
|
|
|
|
OK. You get away with it.............this time. 
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
you're too kind. almost one of a.
|
|
|
|
|
Luc Pattyn wrote: as you can hardly see the spaces at all
I had a PHB asked me once if I could make the spaces blink. That way they could be seen. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Chris Meech wrote: make the spaces blink.
That makes perfect sense: show all special characters the way MS Word does ("show/hide P"), however don't do it permanently, give the user the option to blink them.
|
|
|
|
|
VARCHARs don't make good primary keys anyway. 
|
|
|
|
|
hi
SELECT *
FROM Employees
WHERE (FirstName LIKE N'%ф3rcwvcwvcehveivewiuvgetiugheiheiogheighetighiogheioghioegheiowghioghi
oetjehbvjwebvjefbvjefbvjhebvfjkwbejkfbvjhebvjewbvbevjkewkjvjevfw
jhevfjhebvjefbvejwkfbvewjvbejwbvjefbvjewbvfjkebvjkefvbjefvbjefbvefw
ewvhbewjvbejvbejvfbejfvbjehfvbjewfbvjwefvbewjfvbejhvfbjkevbjhefbvefb
ewkljhvewfvekfjvekfvewkljfbvkejfvbkefbvkewfv%')
In the above query, I have more than 128 characters to search in like predicate, what should be in this case ?
and the error message is
The identifier that starts with is too long. Maximum length is 128.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
