string works in the SELECT portion of the procedure but fails in the
WHERE portion.
The entire SP is listed below.
Specifically, I have a problem with this portion in the WHERE clause:
DATEADD(Day,tblMyEventTableName.ReminderDays, @.DateNow) Between
CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1) + '/' +
str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
str(DATEPART(Year, @.DateNow)),101) AND
tblMyEventTableName.RecurrenceEnd)
(tblMyEventTableName.ReminderDays = days ahead the user wants to be
reminded)
If I use DATEPART(Month, @.DateNow)+1
it fails even though I use this in the SELECT portion. The error
mesage is "The conversion of char data type to smalldatetime data type
resulted in an out-of range smalldatetime value."
If I use DATEPART(Month, @.DateNow)
it doesn't fail.
If I use DATEPART(Month, @.DateNow)+2
it doesn't fail.
If I use DATEPART(Month, @.DateNow)+3
it fails with error message "Error converting data type datetime to
smalldatetime."
If I use DATEPART(Month, @.DateNow)+4
it doesn't fail.
What the SP is trying to do is to evaluate a series of dates in a
table of personal reminders. If the user has set the reminder to
"monthly", the SP evaluates if the day of the month has already passed
today's date, it so, it creates a reminder date (myReminderDate) with
next month's day of the month.
(RecurrenceEnd is the date the reminder is set to stop):
Alter Procedure SPExample
@.DateNow smalldatetime
As
SELECT
CASE WHEN tblMyEventTableName.RecurrencePattern ='monthly' AND
DATEPART(d, @.DateNow) <= DATEPART(d,tblMyEventTableName.TaskDateTime)
and tblMyEventTableName.RecurrenceEnd > @.DateNow
/*monthly event: today day is less than or equal to than monthly task
month-day so remind this month*/
THEN CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)) + '/' +
str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
str(DATEPART(Year, @.DateNow)),101) ELSE
CASE WHEN tblMyEventTableName.RecurrencePattern ='monthly' AND
DATEPART(d, @.DateNow) > DATEPART(d,tblMyEventTableName.TaskDateTime)
and tblMyEventTableName.RecurrenceEnd > @.DateNow
/*monthly event: today day is greater than monthly task month-day so
remind next month*/
THEN CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1) + '/'
+ str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
str(DATEPART(Year, @.DateNow)),101) ELSE
/*RecurrencePattern is not set to monthly so just use the reminder
date*/
tblMyEventTableName.TaskDateTime
END
END
AS myReminderDate, tblMyEventTableName.myTaskName
FROM
tblMyEventTableName
WHERE
/* takes care of monthly events that are after or equal today's day of
year */
tblMyEventTableName.RecurrencePattern ='monthly'
AND
DATEPART(d, @.DateNow) <= DATEPART(d,tblMyEventTableName.TaskDateTime)
AND
tblMyEventTableName.RecurrenceEnd > @.DateNow
AND
DATEADD(Day,tblMyEventTableName.ReminderDays, @.DateNow) Between
CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)) + '/' +
str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
str(DATEPART(Year, @.DateNow)),101) AND
tblMyEventTableName.RecurrenceEnd)
OR
/* takes care of monthly events that are before today's day of year */
tblMyEventTableName.RecurrencePattern ='monthly'
AND
DATEPART(d, @.DateNow) > DATEPART(d,tblMyEventTableName.TaskDateTime)
AND
tblMyEventTableName.RecurrenceEnd > @.DateNow
AND
DATEADD(Day,tblMyEventTableName.ReminderDays, @.DateNow) Between
CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1) + '/' +
str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
str(DATEPART(Year, @.DateNow)),101) AND
tblMyEventTableName.RecurrenceEnd)Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I have a stored procedure using Convert where the exact same Convert
> string works in the SELECT portion of the procedure but fails in the
> WHERE portion.
> The entire SP is listed below.
> Specifically, I have a problem with this portion in the WHERE clause:
> DATEADD(Day,tblMyEventTableName.ReminderDays, @.DateNow) Between
> CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1) + '/' +
> str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
> str(DATEPART(Year, @.DateNow)),101) AND
> tblMyEventTableName.RecurrenceEnd)
Your entire procedure is a bit too complicated to encourage me to
dive into the details, at least not without the table definition
and sample data.
But if I understand the above correctly, it will not work if
taskdatetime is for instance 20030131, as you will land on the
non-existing date 20030231. It seems that you need to refine your
business rules to cover this case.
Also, I don't know how big your table is, but if there is a index
on blMyEventTableName.ReminderDays, the expression above will not
use that index, since the column is part of an expression.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns947A2B67E4B1Yazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > I have a stored procedure using Convert where the exact same Convert
> > string works in the SELECT portion of the procedure but fails in the
> > WHERE portion.
> > The entire SP is listed below.
> > Specifically, I have a problem with this portion in the WHERE clause:
> > DATEADD(Day,tblMyEventTableName.ReminderDays, @.DateNow) Between
> > CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1) + '/' +
> > str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
> > str(DATEPART(Year, @.DateNow)),101) AND
> > tblMyEventTableName.RecurrenceEnd)
> Your entire procedure is a bit too complicated to encourage me to
> dive into the details, at least not without the table definition
> and sample data.
> But if I understand the above correctly, it will not work if
> taskdatetime is for instance 20030131, as you will land on the
> non-existing date 20030231. It seems that you need to refine your
> business rules to cover this case.
> Also, I don't know how big your table is, but if there is a index
> on blMyEventTableName.ReminderDays, the expression above will not
> use that index, since the column is part of an expression.
I think the following clause is not correct:
CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1)
Basically you are attempting to convert an integer number, ie the
month + 1 to a smalldatetime. I would have expected something like:
CONVERT(int,str(DATEPART(Month, @.DateNow)+1)
-- For example
declare @.DateNow smalldatetime
select @.DateNow = '31-Dec-2003'
-- This generates the error Syntax error converting character string
to smalldatetime data type.
select CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1))
-- This works
select CONVERT(int,str(DATEPART(Month, @.DateNow)+1))|||Mystery Man (PromisedOyster@.hotmail.com) writes:
> I think the following clause is not correct:
> CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1)
Yes, taking out of context it is grossly incorrect, because there is a
right parathesis missing. This is the complete expression, reformatted
for legibility:
CONVERT(smalldatetime,
str(DATEPART(Month, @.DateNow) + 1) + '/' +
str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
str(DATEPART(Year, @.DateNow)),
101)
I did the same reflection as you, but I tried in QA to see what it
would actually return. It took a while to grasp that syntax error...
Anway, not even the complete expression is good, because it does not
work for dates like 2004-01-30.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||But the convert is being run on the whole section m/d/y:
CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1) + '/' +
str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
str(DATEPART(Year, @.DateNow)),101)
PromisedOyster@.hotmail.com (Mystery Man) wrote in message news:<87c81238.0401240245.253f0a69@.posting.google.com>...
> Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns947A2B67E4B1Yazorman@.127.0.0.1>...
> > Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > > I have a stored procedure using Convert where the exact same Convert
> > > string works in the SELECT portion of the procedure but fails in the
> > > WHERE portion.
> > > The entire SP is listed below.
> > > Specifically, I have a problem with this portion in the WHERE clause:
> > > > DATEADD(Day,tblMyEventTableName.ReminderDays, @.DateNow) Between
> > > CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1) + '/' +
> > > str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
> > > str(DATEPART(Year, @.DateNow)),101) AND
> > > tblMyEventTableName.RecurrenceEnd)
> > Your entire procedure is a bit too complicated to encourage me to
> > dive into the details, at least not without the table definition
> > and sample data.
> > But if I understand the above correctly, it will not work if
> > taskdatetime is for instance 20030131, as you will land on the
> > non-existing date 20030231. It seems that you need to refine your
> > business rules to cover this case.
> > Also, I don't know how big your table is, but if there is a index
> > on blMyEventTableName.ReminderDays, the expression above will not
> > use that index, since the column is part of an expression.
>
> I think the following clause is not correct:
> CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1)
> Basically you are attempting to convert an integer number, ie the
> month + 1 to a smalldatetime. I would have expected something like:
> CONVERT(int,str(DATEPART(Month, @.DateNow)+1)
>
> -- For example
> declare @.DateNow smalldatetime
> select @.DateNow = '31-Dec-2003'
> -- This generates the error Syntax error converting character string
> to smalldatetime data type.
> select CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1))
> -- This works
> select CONVERT(int,str(DATEPART(Month, @.DateNow)+1))|||Thanks!> I see my error. But how can I accomplish this?
I have a table of tasks with a smalldatetime taskdate field, an
integer field RemDays which is the days in advance they want to be
reminded, and a reminder interval field
A user enters a task/reminder set to Jan.30,2004.
They want to be reminded of this every month, four days in advance of
the date (the 30th of every month).
They log on, let's say the current date is Feb. 27, 2004.
So, since it's three days before Feb.30,2004 the event will show up on
their reminder list. (OK, so that's the first problem- there is NO
Feb.30!)
I want to create sql server SP code that determines that DATETODAY
(Feb. 27) is BETWEEN the (30th of the month) and (30th of the month -
4 (the days in advance to remind)).
That's what I was trying to accomplish in that convert statement,
though poorly!
In the same table are tasks that are not recurring, as well as tasks
with annual reminders (those were easy to figure out.)
I'm going to have the same problem with tasks with weekly reminders as
well.
The SP populates a form that shows all tasks on the reminder list in
descending order, so the task to be reminded on the 30th of every
month, shows in in line with the annual birthday reminder of someone
born on Feb. 28, 1957, and the one time (non recurring) task reminder
for an appointment on Feb. 27, 2004.
Any help is greatly appreciated.
lq
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns947A9EA01D044Yazorman@.127.0.0.1>...
> Mystery Man (PromisedOyster@.hotmail.com) writes:
> > I think the following clause is not correct:
> > CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1)
> Yes, taking out of context it is grossly incorrect, because there is a
> right parathesis missing. This is the complete expression, reformatted
> for legibility:
> CONVERT(smalldatetime,
> str(DATEPART(Month, @.DateNow) + 1) + '/' +
> str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
> str(DATEPART(Year, @.DateNow)),
> 101)
> I did the same reflection as you, but I tried in QA to see what it
> would actually return. It took a while to grasp that syntax error...
> Anway, not even the complete expression is good, because it does not
> work for dates like 2004-01-30.|||To further elaborate:
tblMyTaskTable:
Date Task ReminderInterval
ReminderDaysinAdvance
2/28/1957 Joe Smith Birthday Annual 7
1/30/2004 Rent Due Monthly 4
2/27/2004 Dentist Appointment None 3
1/25/2004 Shrink Appointment Weekly 2
1/25/2004 Car Payment Due Monthly 4
What I'm trying to produce is a form that shows this list when a user
logs in on Feb. 26:
Feb. 25 Shrink Appointment
Feb. 27 Dentist Appointment
Feb. 28 Joe Smith Birthday (1957)
Feb. 30 Rent Due (except there's no Feb. 30 so I have to deal with
that too!)
(the car payment won't show up because today is Feb. 26 and the next
reminder won't trigger until four days before the 25th of the next
month.)
This is proving to be very complex!
Any help is appreciated.
lq
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns947A9EA01D044Yazorman@.127.0.0.1>...
> Mystery Man (PromisedOyster@.hotmail.com) writes:
> > I think the following clause is not correct:
> > CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1)
> Yes, taking out of context it is grossly incorrect, because there is a
> right parathesis missing. This is the complete expression, reformatted
> for legibility:
> CONVERT(smalldatetime,
> str(DATEPART(Month, @.DateNow) + 1) + '/' +
> str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
> str(DATEPART(Year, @.DateNow)),
> 101)
> I did the same reflection as you, but I tried in QA to see what it
> would actually return. It took a while to grasp that syntax error...
> Anway, not even the complete expression is good, because it does not
> work for dates like 2004-01-30.|||Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> Feb. 30 Rent Due (except there's no Feb. 30 so I have to deal with
> that too!)
> (the car payment won't show up because today is Feb. 26 and the next
> reminder won't trigger until four days before the 25th of the next
> month.)
> This is proving to be very complex!
> Any help is appreciated.
First you need to settle on the business rules. Should 30 Feb translate
to 28 Feb most years, and 29 Feb leap years? Or should it translate to
March 2nd?
Once you have settled on the business rules, you may keep this in
mind if you ask for further assistance. When you ask with help with
a query it is often useful to include the following:
o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired output of that sample data.
o A short narrative.
That permits anyone who assists you post a tested solution.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I've trying this in the Annual and Monthly case. I would try a union
all with the significant columns and use it as subquery for the final
output. I did not exhaustive try, so take it as is and good luck!
Diego Buendia
Barcelona Spain
create table T (
Date smalldatetime,
Task varchar(30),
ReminderInterval varchar(10),
ReminderDaysinAdvance int
)
insert T values ( '19570228', 'Joe Smith Birthday', 'Annual', 7 )
insert T values ( '20040130', 'Rent Due', 'Monthly', 4 )
insert T values ( '20040227', 'Dentist Appointment', 'None', 3 )
insert T values ( '20040125', 'Shrink Appointment', 'Weekly', 2 )
insert T values ( '20040125', 'Car Payment Due', 'Monthly', 4 )
declare @.day smalldatetime
set @.day = '20040226'
/*
case Annual: get the years in between, add to current day,
get difference in days and filter if less than reminderDaysInAdvance
*/
select
-- these columns are for demo only, you should filter them
datediff( year, date, @.day ),
dateadd( year, datediff( year, date, @.day ), date ),
datediff( day, @.day, dateadd( year, datediff( year, date, @.day ),
date ) ),
reminderdaysinadvance,
date, @.day, task, year(date) as aniversary
from t
where
reminderinterval = 'Annual'
and
1 =
case when
datediff( day, @.day, dateadd( year, datediff( year, date, @.day ),
date ) )
between 0 And ReminderDaysInAdvance
then 1
else 0
end
/*
case Monthly
*/
select
datediff( month, date, @.day ),
dateadd( month, datediff( month, date, @.day ), date ),
datediff( day, @.day, dateadd( month, datediff( month, date, @.day ),
date ) ),
reminderdaysinadvance,
date, @.day, task, year(date) as aniversary
from t
where
reminderinterval = 'Monthly'
and
1 =
case when
datediff( day, @.day, dateadd( month, datediff( month, date, @.day ),
date ) )
between 0 and ReminderDaysInAdvance
then 1
else 0
end|||Lauren,
The keys to this problem are:
1. Let the database do the date math. It already knows what a valid date
is and what, for example, 1/30/2004 + one month is. Let it do that work.
2. Treat each reminder Interval as a different problem. Make each have
it's own SQL and put them together with a UNION.
3. Recognize that your SQL ends up comparing getdate() (ie: today) between
2 dates. For example, the monthly reminder would look like:
and getdate() between ThisMonthsReminder - DaysInAdvance
and ThisMonthsReminder
So, you need to first figure out what this month's reminder is (for monthly
reminders). This month's reminder is:
dateadd(mm, datediff(mm,reminder_date,getdate()), reminder_date)
- the datediff figures out how many months between today and my reminder
date.
- the dateadd adds that number of months to my reminder date to give me
ThisMonthsReminder
What I did to solve this is write a procedure which takes any date (not just
today) as an arguement and gives you the annual and monthly reminders. The
weekly and None Intervals should follow the same pattern.
Creating the tables and inserting the data were done like this:
create table tblMyTaskTable(reminder_date datetime,
task varchar(100),
reminder_interval varchar(30),
days_in_advance integer)
go
insert into tblMyTaskTable values('2/28/1957','Joe Smith
Birthday','Annual',7)
go
insert into tblMyTaskTable values('1/30/2004','Rent Due','Monthly','4')
go
insert into tblMyTaskTable values('2/27/2004','Dentist
Appointment','None','3')
go
insert into tblMyTaskTable values('1/25/2004','Shrink
Appointment','Weekly','2')
go
insert into tblMyTaskTable values('1/25/2004','Car Payment
Due','Monthly','4')
go
The procedure looks like:
create procedure get_reminders (@.as_date datetime)
as
begin
declare @.s_year char(4)
declare @.s_date char(5)
set @.s_date = substring(convert(varchar,@.as_date,120),6,5)
set @.s_year = substring(convert(varchar,@.as_date,120),1,4)
print @.s_date
print @.s_year
select dateadd(yy,datediff(yy,reminder_date,@.as_date),rem inder_date),task
from tblMytasktable
where reminder_interval = 'Annual'
and @.as_date between
dateadd(dd,(-1)*days_in_advance,dateadd(yy,datediff(yy,reminder _date,@.as_dat
e),reminder_date))
and dateadd(yy,datediff(yy,reminder_date,@.as_date),rem inder_date)
union
select dateadd(mm,datediff(mm,reminder_date,@.as_date),rem inder_date), task
from tblMytasktable
where reminder_interval = 'Monthly'
and @.as_date between
dateadd(dd,(-1)*days_in_advance,dateadd(mm,datediff(mm,reminder _date,@.as_dat
e),reminder_date) )
and dateadd(mm,datediff(mm,reminder_date,@.as_date),rem inder_date)
end
call the procedure for 2/26/04 like this:
exec get_reminders '2004-02-26'
The biggest problem with this procedure and the main query is that it cannot
use an index on reminder_date. So, if you are using this with more than 200
or so reminders, it could be slow, depending on your hardware.
Let me know if you need help with the weekly and None interval sections of
the SQL. Also, you might find some help with SQL Server on our
www.TechnicalVideos.net site.
Best regards,
Chuck Conover
www.TechnicalVideos.net
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0401241342.6931ae64@.posting.google.c om...
> To further elaborate:
> tblMyTaskTable:
> Date Task ReminderInterval
> ReminderDaysinAdvance
> 2/28/1957 Joe Smith Birthday Annual 7
> 1/30/2004 Rent Due Monthly 4
> 2/27/2004 Dentist Appointment None 3
> 1/25/2004 Shrink Appointment Weekly 2
> 1/25/2004 Car Payment Due Monthly 4
> What I'm trying to produce is a form that shows this list when a user
> logs in on Feb. 26:
> Feb. 25 Shrink Appointment
> Feb. 27 Dentist Appointment
> Feb. 28 Joe Smith Birthday (1957)
> Feb. 30 Rent Due (except there's no Feb. 30 so I have to deal with
> that too!)
> (the car payment won't show up because today is Feb. 26 and the next
> reminder won't trigger until four days before the 25th of the next
> month.)
> This is proving to be very complex!
> Any help is appreciated.
> lq
>
>
>
>
> Erland Sommarskog <sommar@.algonet.se> wrote in message
news:<Xns947A9EA01D044Yazorman@.127.0.0.1>...
> > Mystery Man (PromisedOyster@.hotmail.com) writes:
> > > I think the following clause is not correct:
> > > > CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1)
> > Yes, taking out of context it is grossly incorrect, because there is a
> > right parathesis missing. This is the complete expression, reformatted
> > for legibility:
> > CONVERT(smalldatetime,
> > str(DATEPART(Month, @.DateNow) + 1) + '/' +
> > str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/'
+
> > str(DATEPART(Year, @.DateNow)),
> > 101)
> > I did the same reflection as you, but I tried in QA to see what it
> > would actually return. It took a while to grasp that syntax error...
> > Anway, not even the complete expression is good, because it does not
> > work for dates like 2004-01-30.|||Chuck,
Thank you so much for the solution!
I modified your SP and included weekly and non-recurring events and it
works great.
I notice in your SP you included:
> declare @.s_year char(4)
> declare @.s_date char(5)
> set @.s_date = substring(convert(varchar,@.as_date,120),6,5)
> set @.s_year = substring(convert(varchar,@.as_date,120),1,4)
...though you don't use it in the code.
A question, I haven't used Union in SPs before. How can I do an ORDER
BY for the whole result. I have four select statements joined with the
unions.
Thanks,
lq
"Chuck Conover" <cconover@.commspeed.net> wrote in message news:<1075063730.111466@.news.commspeed.net>...
> Lauren,
> The keys to this problem are:
> 1. Let the database do the date math. It already knows what a valid date
> is and what, for example, 1/30/2004 + one month is. Let it do that work.
> 2. Treat each reminder Interval as a different problem. Make each have
> it's own SQL and put them together with a UNION.
> 3. Recognize that your SQL ends up comparing getdate() (ie: today) between
> 2 dates. For example, the monthly reminder would look like:
> and getdate() between ThisMonthsReminder - DaysInAdvance
> and ThisMonthsReminder
> So, you need to first figure out what this month's reminder is (for monthly
> reminders). This month's reminder is:
> dateadd(mm, datediff(mm,reminder_date,getdate()), reminder_date)
> - the datediff figures out how many months between today and my reminder
> date.
> - the dateadd adds that number of months to my reminder date to give me
> ThisMonthsReminder
> What I did to solve this is write a procedure which takes any date (not just
> today) as an arguement and gives you the annual and monthly reminders. The
> weekly and None Intervals should follow the same pattern.
> Creating the tables and inserting the data were done like this:
> create table tblMyTaskTable(reminder_date datetime,
> task varchar(100),
> reminder_interval varchar(30),
> days_in_advance integer)
> go
> insert into tblMyTaskTable values('2/28/1957','Joe Smith
> Birthday','Annual',7)
> go
> insert into tblMyTaskTable values('1/30/2004','Rent Due','Monthly','4')
> go
> insert into tblMyTaskTable values('2/27/2004','Dentist
> Appointment','None','3')
> go
> insert into tblMyTaskTable values('1/25/2004','Shrink
> Appointment','Weekly','2')
> go
> insert into tblMyTaskTable values('1/25/2004','Car Payment
> Due','Monthly','4')
> go
>
> The procedure looks like:
> create procedure get_reminders (@.as_date datetime)
> as
> begin
> declare @.s_year char(4)
> declare @.s_date char(5)
> set @.s_date = substring(convert(varchar,@.as_date,120),6,5)
> set @.s_year = substring(convert(varchar,@.as_date,120),1,4)
> print @.s_date
> print @.s_year
> select dateadd(yy,datediff(yy,reminder_date,@.as_date),rem inder_date),task
> from tblMytasktable
> where reminder_interval = 'Annual'
> and @.as_date between
> dateadd(dd,(-1)*days_in_advance,dateadd(yy,datediff(yy,reminder _date,@.as_dat
> e),reminder_date))
> and dateadd(yy,datediff(yy,reminder_date,@.as_date),rem inder_date)
> union
> select dateadd(mm,datediff(mm,reminder_date,@.as_date),rem inder_date), task
> from tblMytasktable
> where reminder_interval = 'Monthly'
> and @.as_date between
> dateadd(dd,(-1)*days_in_advance,dateadd(mm,datediff(mm,reminder _date,@.as_dat
> e),reminder_date) )
> and dateadd(mm,datediff(mm,reminder_date,@.as_date),rem inder_date)
> end
> call the procedure for 2/26/04 like this:
> exec get_reminders '2004-02-26'
> The biggest problem with this procedure and the main query is that it cannot
> use an index on reminder_date. So, if you are using this with more than 200
> or so reminders, it could be slow, depending on your hardware.
> Let me know if you need help with the weekly and None interval sections of
> the SQL. Also, you might find some help with SQL Server on our
> www.TechnicalVideos.net site.
> Best regards,
> Chuck Conover
> www.TechnicalVideos.net
>
>
> "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> news:47e5bd72.0401241342.6931ae64@.posting.google.c om...
> > To further elaborate:
> > tblMyTaskTable:
> > Date Task ReminderInterval
> > ReminderDaysinAdvance
> > 2/28/1957 Joe Smith Birthday Annual 7
> > 1/30/2004 Rent Due Monthly 4
> > 2/27/2004 Dentist Appointment None 3
> > 1/25/2004 Shrink Appointment Weekly 2
> > 1/25/2004 Car Payment Due Monthly 4
> > What I'm trying to produce is a form that shows this list when a user
> > logs in on Feb. 26:
> > Feb. 25 Shrink Appointment
> > Feb. 27 Dentist Appointment
> > Feb. 28 Joe Smith Birthday (1957)
> > Feb. 30 Rent Due (except there's no Feb. 30 so I have to deal with
> > that too!)
> > (the car payment won't show up because today is Feb. 26 and the next
> > reminder won't trigger until four days before the 25th of the next
> > month.)
> > This is proving to be very complex!
> > Any help is appreciated.
> > lq
> > Erland Sommarskog <sommar@.algonet.se> wrote in message
> news:<Xns947A9EA01D044Yazorman@.127.0.0.1>...
> > > Mystery Man (PromisedOyster@.hotmail.com) writes:
> > > > I think the following clause is not correct:
> > > > > > CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1)
> > > > Yes, taking out of context it is grossly incorrect, because there is a
> > > right parathesis missing. This is the complete expression, reformatted
> > > for legibility:
> > > > CONVERT(smalldatetime,
> > > str(DATEPART(Month, @.DateNow) + 1) + '/' +
> > > str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/'
> +
> > > str(DATEPART(Year, @.DateNow)),
> > > 101)
> > > > I did the same reflection as you, but I tried in QA to see what it
> > > would actually return. It took a while to grasp that syntax error...
> > > > Anway, not even the complete expression is good, because it does not
> > > work for dates like 2004-01-30.|||Diego,
Thanks for pointing me in the right direction on this.
lq
dbuendiab@.yahoo.es (Diego Buendia) wrote in message news:<71e97616.0401251129.43fffb13@.posting.google.com>...
> I've trying this in the Annual and Monthly case. I would try a union
> all with the significant columns and use it as subquery for the final
> output. I did not exhaustive try, so take it as is and good luck!
> Diego Buendia
> Barcelona Spain
> create table T (
> Date smalldatetime,
> Task varchar(30),
> ReminderInterval varchar(10),
> ReminderDaysinAdvance int
> )
> insert T values ( '19570228', 'Joe Smith Birthday', 'Annual', 7 )
> insert T values ( '20040130', 'Rent Due', 'Monthly', 4 )
> insert T values ( '20040227', 'Dentist Appointment', 'None', 3 )
> insert T values ( '20040125', 'Shrink Appointment', 'Weekly', 2 )
> insert T values ( '20040125', 'Car Payment Due', 'Monthly', 4 )
> declare @.day smalldatetime
> set @.day = '20040226'
> /*
> case Annual: get the years in between, add to current day,
> get difference in days and filter if less than reminderDaysInAdvance
> */
> select
> -- these columns are for demo only, you should filter them
> datediff( year, date, @.day ),
> dateadd( year, datediff( year, date, @.day ), date ),
> datediff( day, @.day, dateadd( year, datediff( year, date, @.day ),
> date ) ),
> reminderdaysinadvance,
> date, @.day, task, year(date) as aniversary
> from t
> where
> reminderinterval = 'Annual'
> and
> 1 =
> case when
> datediff( day, @.day, dateadd( year, datediff( year, date, @.day ),
> date ) )
> between 0 And ReminderDaysInAdvance
> then 1
> else 0
> end
>
> /*
> case Monthly
> */
> select
> datediff( month, date, @.day ),
> dateadd( month, datediff( month, date, @.day ), date ),
> datediff( day, @.day, dateadd( month, datediff( month, date, @.day ),
> date ) ),
> reminderdaysinadvance,
> date, @.day, task, year(date) as aniversary
> from t
> where
> reminderinterval = 'Monthly'
> and
> 1 =
> case when
> datediff( day, @.day, dateadd( month, datediff( month, date, @.day ),
> date ) )
> between 0 and ReminderDaysInAdvance
> then 1
> else 0
> end|||Lauren,
Truthfully, it took me about an hour to figure out this solution, given
the complexity. So, it went thru many changes. @.s_year and @.s_date were
used in an earlier version.
To do "order by" when using a union, specify the column number instead
of the column name like:
order by 1 <-- to order by the first column
order by 2,1 <-- to order by the second column, then the first column
Best regards,
Chuck Conover
www.TechnicalVideos.net
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0401270819.19e6068@.posting.google.co m...
> Chuck,
> Thank you so much for the solution!
> I modified your SP and included weekly and non-recurring events and it
> works great.
> I notice in your SP you included:
> > declare @.s_year char(4)
> > declare @.s_date char(5)
> > set @.s_date = substring(convert(varchar,@.as_date,120),6,5)
> > set @.s_year = substring(convert(varchar,@.as_date,120),1,4)
> ...though you don't use it in the code.
> A question, I haven't used Union in SPs before. How can I do an ORDER
> BY for the whole result. I have four select statements joined with the
> unions.
> Thanks,
> lq
>
> "Chuck Conover" <cconover@.commspeed.net> wrote in message
news:<1075063730.111466@.news.commspeed.net>...
> > Lauren,
> > The keys to this problem are:
> > 1. Let the database do the date math. It already knows what a valid
date
> > is and what, for example, 1/30/2004 + one month is. Let it do that
work.
> > 2. Treat each reminder Interval as a different problem. Make each have
> > it's own SQL and put them together with a UNION.
> > 3. Recognize that your SQL ends up comparing getdate() (ie: today)
between
> > 2 dates. For example, the monthly reminder would look like:
> > and getdate() between ThisMonthsReminder - DaysInAdvance
> > and ThisMonthsReminder
> > So, you need to first figure out what this month's reminder is (for
monthly
> > reminders). This month's reminder is:
> > dateadd(mm, datediff(mm,reminder_date,getdate()), reminder_date)
> > - the datediff figures out how many months between today and my
reminder
> > date.
> > - the dateadd adds that number of months to my reminder date to give me
> > ThisMonthsReminder
> > What I did to solve this is write a procedure which takes any date (not
just
> > today) as an arguement and gives you the annual and monthly reminders.
The
> > weekly and None Intervals should follow the same pattern.
> > Creating the tables and inserting the data were done like this:
> > create table tblMyTaskTable(reminder_date datetime,
> > task varchar(100),
> > reminder_interval varchar(30),
> > days_in_advance integer)
> > go
> > insert into tblMyTaskTable values('2/28/1957','Joe Smith
> > Birthday','Annual',7)
> > go
> > insert into tblMyTaskTable values('1/30/2004','Rent Due','Monthly','4')
> > go
> > insert into tblMyTaskTable values('2/27/2004','Dentist
> > Appointment','None','3')
> > go
> > insert into tblMyTaskTable values('1/25/2004','Shrink
> > Appointment','Weekly','2')
> > go
> > insert into tblMyTaskTable values('1/25/2004','Car Payment
> > Due','Monthly','4')
> > go
> > The procedure looks like:
> > create procedure get_reminders (@.as_date datetime)
> > as
> > begin
> > declare @.s_year char(4)
> > declare @.s_date char(5)
> > set @.s_date = substring(convert(varchar,@.as_date,120),6,5)
> > set @.s_year = substring(convert(varchar,@.as_date,120),1,4)
> > print @.s_date
> > print @.s_year
> > select
dateadd(yy,datediff(yy,reminder_date,@.as_date),rem inder_date),task
> > from tblMytasktable
> > where reminder_interval = 'Annual'
> > and @.as_date between
dateadd(dd,(-1)*days_in_advance,dateadd(yy,datediff(yy,reminder _date,@.as_dat
> > e),reminder_date))
> > and dateadd(yy,datediff(yy,reminder_date,@.as_date),rem inder_date)
> > union
> > select dateadd(mm,datediff(mm,reminder_date,@.as_date),rem inder_date),
task
> > from tblMytasktable
> > where reminder_interval = 'Monthly'
> > and @.as_date between
dateadd(dd,(-1)*days_in_advance,dateadd(mm,datediff(mm,reminder _date,@.as_dat
> > e),reminder_date) )
> > and dateadd(mm,datediff(mm,reminder_date,@.as_date),rem inder_date)
> > end
> > call the procedure for 2/26/04 like this:
> > exec get_reminders '2004-02-26'
> > The biggest problem with this procedure and the main query is that it
cannot
> > use an index on reminder_date. So, if you are using this with more than
200
> > or so reminders, it could be slow, depending on your hardware.
> > Let me know if you need help with the weekly and None interval sections
of
> > the SQL. Also, you might find some help with SQL Server on our
> > www.TechnicalVideos.net site.
> > Best regards,
> > Chuck Conover
> > www.TechnicalVideos.net
> > "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> > news:47e5bd72.0401241342.6931ae64@.posting.google.c om...
> > > To further elaborate:
> > > > tblMyTaskTable:
> > > > Date Task ReminderInterval
> > > ReminderDaysinAdvance
> > > 2/28/1957 Joe Smith Birthday Annual 7
> > > 1/30/2004 Rent Due Monthly 4
> > > 2/27/2004 Dentist Appointment None 3
> > > 1/25/2004 Shrink Appointment Weekly 2
> > > 1/25/2004 Car Payment Due Monthly 4
> > > > What I'm trying to produce is a form that shows this list when a user
> > > logs in on Feb. 26:
> > > > Feb. 25 Shrink Appointment
> > > Feb. 27 Dentist Appointment
> > > Feb. 28 Joe Smith Birthday (1957)
> > > Feb. 30 Rent Due (except there's no Feb. 30 so I have to deal with
> > > that too!)
> > > > (the car payment won't show up because today is Feb. 26 and the next
> > > reminder won't trigger until four days before the 25th of the next
> > > month.)
> > > > This is proving to be very complex!
> > > > Any help is appreciated.
> > > lq
> > > > > > > > > > > Erland Sommarskog <sommar@.algonet.se> wrote in message
> > news:<Xns947A9EA01D044Yazorman@.127.0.0.1>...
> > > > Mystery Man (PromisedOyster@.hotmail.com) writes:
> > > > > I think the following clause is not correct:
> > > > > > > > CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1)
> > > > > > Yes, taking out of context it is grossly incorrect, because there is
a
> > > > right parathesis missing. This is the complete expression,
reformatted
> > > > for legibility:
> > > > > > CONVERT(smalldatetime,
> > > > str(DATEPART(Month, @.DateNow) + 1) + '/' +
> > > > str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) +
'/'
> > +
> > > > str(DATEPART(Year, @.DateNow)),
> > > > 101)
> > > > > > I did the same reflection as you, but I tried in QA to see what it
> > > > would actually return. It took a while to grasp that syntax error...
> > > > > > Anway, not even the complete expression is good, because it does not
> > > > work for dates like 2004-01-30.|||Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> A question, I haven't used Union in SPs before. How can I do an ORDER
> BY for the whole result. I have four select statements joined with the
> unions.
Here is an example:
SELECT CompanyName
FROM Northwind..Customers
UNION
SELECT ProductName
FROM Northwind..Products
ORDER BY CompanyName
The ORDER BY clause comes after all SELECT statements, and you use
the column names of the first SELECT. Of course, you can use column
numbers, as suggested by Chuck.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for all the help. The reminder calendar is working slendidly
now. (Unfortunately it already worked splendidly on an MS Access2K.MDB
with VBA before with many hours of trial and error. So much to redo in
SSQL Server...)
I made a brain dead mistake on the Order By and now it works, but it
leads me to another question.
Since the SP is many lines long, it seems very silly to copy the whole
SP into a another SP just to change the sort order. Is there a
simpler solution?
I suppose I could pass it a tinyint and a nvarchar parameter to handle
the sorting:
@.myColumn tinyint, @.myOrder nvarchar(4) = null
...
ORDER BY @.myColumn + ' ' + @.myOrder
But this could get very complex with multiple column sorting
variations.
Is there a better way?
Thanks again for all the help!
lq
"Chuck Conover" <cconover@.commspeed.net> wrote in message news:<1075222542.653551@.news.commspeed.net>...
> Lauren,
> Truthfully, it took me about an hour to figure out this solution, given
> the complexity. So, it went thru many changes. @.s_year and @.s_date were
> used in an earlier version.
> To do "order by" when using a union, specify the column number instead
> of the column name like:
> order by 1 <-- to order by the first column
> order by 2,1 <-- to order by the second column, then the first column
> Best regards,
> Chuck Conover
> www.TechnicalVideos.net
>
> "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> news:47e5bd72.0401270819.19e6068@.posting.google.co m...
> > Chuck,
> > Thank you so much for the solution!
> > I modified your SP and included weekly and non-recurring events and it
> > works great.
> > I notice in your SP you included:
> > > declare @.s_year char(4)
> > > declare @.s_date char(5)
> > > > set @.s_date = substring(convert(varchar,@.as_date,120),6,5)
> > > set @.s_year = substring(convert(varchar,@.as_date,120),1,4)
> > ...though you don't use it in the code.
> > A question, I haven't used Union in SPs before. How can I do an ORDER
> > BY for the whole result. I have four select statements joined with the
> > unions.
> > Thanks,
> > lq
> > "Chuck Conover" <cconover@.commspeed.net> wrote in message
> news:<1075063730.111466@.news.commspeed.net>...
> > > Lauren,
> > > The keys to this problem are:
> > > 1. Let the database do the date math. It already knows what a valid
> date
> > > is and what, for example, 1/30/2004 + one month is. Let it do that
> work.
> > > > 2. Treat each reminder Interval as a different problem. Make each have
> > > it's own SQL and put them together with a UNION.
> > > > 3. Recognize that your SQL ends up comparing getdate() (ie: today)
> between
> > > 2 dates. For example, the monthly reminder would look like:
> > > > and getdate() between ThisMonthsReminder - DaysInAdvance
> > > and ThisMonthsReminder
> > > > So, you need to first figure out what this month's reminder is (for
> monthly
> > > reminders). This month's reminder is:
> > > > dateadd(mm, datediff(mm,reminder_date,getdate()), reminder_date)
> > > - the datediff figures out how many months between today and my
> reminder
> > > date.
> > > - the dateadd adds that number of months to my reminder date to give me
> > > ThisMonthsReminder
> > > > What I did to solve this is write a procedure which takes any date (not
> just
> > > today) as an arguement and gives you the annual and monthly reminders.
> The
> > > weekly and None Intervals should follow the same pattern.
> > > > Creating the tables and inserting the data were done like this:
> > > > create table tblMyTaskTable(reminder_date datetime,
> > > task varchar(100),
> > > reminder_interval varchar(30),
> > > days_in_advance integer)
> > > go
> > > insert into tblMyTaskTable values('2/28/1957','Joe Smith
> > > Birthday','Annual',7)
> > > go
> > > insert into tblMyTaskTable values('1/30/2004','Rent Due','Monthly','4')
> > > go
> > > insert into tblMyTaskTable values('2/27/2004','Dentist
> > > Appointment','None','3')
> > > go
> > > insert into tblMyTaskTable values('1/25/2004','Shrink
> > > Appointment','Weekly','2')
> > > go
> > > insert into tblMyTaskTable values('1/25/2004','Car Payment
> > > Due','Monthly','4')
> > > go
> > > > > The procedure looks like:
> > > create procedure get_reminders (@.as_date datetime)
> > > as
> > > begin
> > > > declare @.s_year char(4)
> > > declare @.s_date char(5)
> > > > set @.s_date = substring(convert(varchar,@.as_date,120),6,5)
> > > set @.s_year = substring(convert(varchar,@.as_date,120),1,4)
> > > > print @.s_date
> > > print @.s_year
> > > > select
> dateadd(yy,datediff(yy,reminder_date,@.as_date),rem inder_date),task
> > > from tblMytasktable
> > > where reminder_interval = 'Annual'
> > > and @.as_date between
> > > dateadd(dd,(-1)*days_in_advance,dateadd(yy,datediff(yy,reminder _date,@.as_dat
> > > e),reminder_date))
> > > and dateadd(yy,datediff(yy,reminder_date,@.as_date),rem inder_date)
> > > > union
> > > > select dateadd(mm,datediff(mm,reminder_date,@.as_date),rem inder_date),
> task
> > > from tblMytasktable
> > > where reminder_interval = 'Monthly'
> > > and @.as_date between
> > dateadd(dd,(-1)*days_in_advance,dateadd(mm,datediff(mm,reminder _date,@.as_dat
> > > e),reminder_date) )
> > > and dateadd(mm,datediff(mm,reminder_date,@.as_date),rem inder_date)
> > > > end
> > > > call the procedure for 2/26/04 like this:
> > > > exec get_reminders '2004-02-26'
> > > > The biggest problem with this procedure and the main query is that it
> cannot
> > > use an index on reminder_date. So, if you are using this with more than
> 200
> > > or so reminders, it could be slow, depending on your hardware.
> > > > Let me know if you need help with the weekly and None interval sections
> of
> > > the SQL. Also, you might find some help with SQL Server on our
> > > www.TechnicalVideos.net site.
> > > > Best regards,
> > > Chuck Conover
> > > www.TechnicalVideos.net
> > > > > > > > "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> > > news:47e5bd72.0401241342.6931ae64@.posting.google.c om...
> > > > To further elaborate:
> > > > > > tblMyTaskTable:
> > > > > > Date Task ReminderInterval
> > > > ReminderDaysinAdvance
> > > > 2/28/1957 Joe Smith Birthday Annual 7
> > > > 1/30/2004 Rent Due Monthly 4
> > > > 2/27/2004 Dentist Appointment None 3
> > > > 1/25/2004 Shrink Appointment Weekly 2
> > > > 1/25/2004 Car Payment Due Monthly 4
> > > > > > What I'm trying to produce is a form that shows this list when a user
> > > > logs in on Feb. 26:
> > > > > > Feb. 25 Shrink Appointment
> > > > Feb. 27 Dentist Appointment
> > > > Feb. 28 Joe Smith Birthday (1957)
> > > > Feb. 30 Rent Due (except there's no Feb. 30 so I have to deal with
> > > > that too!)
> > > > > > (the car payment won't show up because today is Feb. 26 and the next
> > > > reminder won't trigger until four days before the 25th of the next
> > > > month.)
> > > > > > This is proving to be very complex!
> > > > > > Any help is appreciated.
> > > > lq
> > > > > > > > > > > > > > > > > > > > Erland Sommarskog <sommar@.algonet.se> wrote in message
> news:<Xns947A9EA01D044Yazorman@.127.0.0.1>...
> > > > > Mystery Man (PromisedOyster@.hotmail.com) writes:
> > > > > > I think the following clause is not correct:
> > > > > > > > > > CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1)
> > > > > > > > Yes, taking out of context it is grossly incorrect, because there is
> a
> > > > > right parathesis missing. This is the complete expression,
> reformatted
> > > > > for legibility:
> > > > > > > > CONVERT(smalldatetime,
> > > > > str(DATEPART(Month, @.DateNow) + 1) + '/' +
> > > > > str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) +
> '/'
> +
> > > > > str(DATEPART(Year, @.DateNow)),
> > > > > 101)
> > > > > > > > I did the same reflection as you, but I tried in QA to see what it
> > > > > would actually return. It took a while to grasp that syntax error...
> > > > > > > > Anway, not even the complete expression is good, because it does not
> > > > > work for dates like 2004-01-30.|||Erland,
I had a brain spaz on that one. Sorry. But thanks for the reply.
lq
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns947DF244CC05BYazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > A question, I haven't used Union in SPs before. How can I do an ORDER
> > BY for the whole result. I have four select statements joined with the
> > unions.
> Here is an example:
> SELECT CompanyName
> FROM Northwind..Customers
> UNION
> SELECT ProductName
> FROM Northwind..Products
> ORDER BY CompanyName
> The ORDER BY clause comes after all SELECT statements, and you use
> the column names of the first SELECT. Of course, you can use column
> numbers, as suggested by Chuck.|||Chuck,
So what if I want to include a recurring pattern for Weekday reminders?
I see T-SQL "dw" would I just substitute that for "ww" ?
lq
"Chuck Conover" <cconover@.commspeed.net> wrote in message news:<1075222542.653551@.news.commspeed.net>...
> Lauren,
> Truthfully, it took me about an hour to figure out this solution, given
> the complexity. So, it went thru many changes. @.s_year and @.s_date were
> used in an earlier version.
> To do "order by" when using a union, specify the column number instead
> of the column name like:
> order by 1 <-- to order by the first column
> order by 2,1 <-- to order by the second column, then the first column
> Best regards,
> Chuck Conover
> www.TechnicalVideos.net
>
> "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> news:47e5bd72.0401270819.19e6068@.posting.google.co m...
> > Chuck,
> > Thank you so much for the solution!
> > I modified your SP and included weekly and non-recurring events and it
> > works great.
> > I notice in your SP you included:
> > > declare @.s_year char(4)
> > > declare @.s_date char(5)
> > > > set @.s_date = substring(convert(varchar,@.as_date,120),6,5)
> > > set @.s_year = substring(convert(varchar,@.as_date,120),1,4)
> > ...though you don't use it in the code.
> > A question, I haven't used Union in SPs before. How can I do an ORDER
> > BY for the whole result. I have four select statements joined with the
> > unions.
> > Thanks,
> > lq
> > "Chuck Conover" <cconover@.commspeed.net> wrote in message
> news:<1075063730.111466@.news.commspeed.net>...
> > > Lauren,
> > > The keys to this problem are:
> > > 1. Let the database do the date math. It already knows what a valid
> date
> > > is and what, for example, 1/30/2004 + one month is. Let it do that
> work.
> > > > 2. Treat each reminder Interval as a different problem. Make each have
> > > it's own SQL and put them together with a UNION.
> > > > 3. Recognize that your SQL ends up comparing getdate() (ie: today)
> between
> > > 2 dates. For example, the monthly reminder would look like:
> > > > and getdate() between ThisMonthsReminder - DaysInAdvance
> > > and ThisMonthsReminder
> > > > So, you need to first figure out what this month's reminder is (for
> monthly
> > > reminders). This month's reminder is:
> > > > dateadd(mm, datediff(mm,reminder_date,getdate()), reminder_date)
> > > - the datediff figures out how many months between today and my
> reminder
> > > date.
> > > - the dateadd adds that number of months to my reminder date to give me
> > > ThisMonthsReminder
> > > > What I did to solve this is write a procedure which takes any date (not
> just
> > > today) as an arguement and gives you the annual and monthly reminders.
> The
> > > weekly and None Intervals should follow the same pattern.
> > > > Creating the tables and inserting the data were done like this:
> > > > create table tblMyTaskTable(reminder_date datetime,
> > > task varchar(100),
> > > reminder_interval varchar(30),
> > > days_in_advance integer)
> > > go
> > > insert into tblMyTaskTable values('2/28/1957','Joe Smith
> > > Birthday','Annual',7)
> > > go
> > > insert into tblMyTaskTable values('1/30/2004','Rent Due','Monthly','4')
> > > go
> > > insert into tblMyTaskTable values('2/27/2004','Dentist
> > > Appointment','None','3')
> > > go
> > > insert into tblMyTaskTable values('1/25/2004','Shrink
> > > Appointment','Weekly','2')
> > > go
> > > insert into tblMyTaskTable values('1/25/2004','Car Payment
> > > Due','Monthly','4')
> > > go
> > > > > The procedure looks like:
> > > create procedure get_reminders (@.as_date datetime)
> > > as
> > > begin
> > > > declare @.s_year char(4)
> > > declare @.s_date char(5)
> > > > set @.s_date = substring(convert(varchar,@.as_date,120),6,5)
> > > set @.s_year = substring(convert(varchar,@.as_date,120),1,4)
> > > > print @.s_date
> > > print @.s_year
> > > > select
> dateadd(yy,datediff(yy,reminder_date,@.as_date),rem inder_date),task
> > > from tblMytasktable
> > > where reminder_interval = 'Annual'
> > > and @.as_date between
> > > dateadd(dd,(-1)*days_in_advance,dateadd(yy,datediff(yy,reminder _date,@.as_dat
> > > e),reminder_date))
> > > and dateadd(yy,datediff(yy,reminder_date,@.as_date),rem inder_date)
> > > > union
> > > > select dateadd(mm,datediff(mm,reminder_date,@.as_date),rem inder_date),
> task
> > > from tblMytasktable
> > > where reminder_interval = 'Monthly'
> > > and @.as_date between
> > dateadd(dd,(-1)*days_in_advance,dateadd(mm,datediff(mm,reminder _date,@.as_dat
> > > e),reminder_date) )
> > > and dateadd(mm,datediff(mm,reminder_date,@.as_date),rem inder_date)
> > > > end
> > > > call the procedure for 2/26/04 like this:
> > > > exec get_reminders '2004-02-26'
> > > > The biggest problem with this procedure and the main query is that it
> cannot
> > > use an index on reminder_date. So, if you are using this with more than
> 200
> > > or so reminders, it could be slow, depending on your hardware.
> > > > Let me know if you need help with the weekly and None interval sections
> of
> > > the SQL. Also, you might find some help with SQL Server on our
> > > www.TechnicalVideos.net site.
> > > > Best regards,
> > > Chuck Conover
> > > www.TechnicalVideos.net
> > > > > > > > "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> > > news:47e5bd72.0401241342.6931ae64@.posting.google.c om...
> > > > To further elaborate:
> > > > > > tblMyTaskTable:
> > > > > > Date Task ReminderInterval
> > > > ReminderDaysinAdvance
> > > > 2/28/1957 Joe Smith Birthday Annual 7
> > > > 1/30/2004 Rent Due Monthly 4
> > > > 2/27/2004 Dentist Appointment None 3
> > > > 1/25/2004 Shrink Appointment Weekly 2
> > > > 1/25/2004 Car Payment Due Monthly 4
> > > > > > What I'm trying to produce is a form that shows this list when a user
> > > > logs in on Feb. 26:
> > > > > > Feb. 25 Shrink Appointment
> > > > Feb. 27 Dentist Appointment
> > > > Feb. 28 Joe Smith Birthday (1957)
> > > > Feb. 30 Rent Due (except there's no Feb. 30 so I have to deal with
> > > > that too!)
> > > > > > (the car payment won't show up because today is Feb. 26 and the next
> > > > reminder won't trigger until four days before the 25th of the next
> > > > month.)
> > > > > > This is proving to be very complex!
> > > > > > Any help is appreciated.
> > > > lq
> > > > > > > > > > > > > > > > > > > > Erland Sommarskog <sommar@.algonet.se> wrote in message
> news:<Xns947A9EA01D044Yazorman@.127.0.0.1>...
> > > > > Mystery Man (PromisedOyster@.hotmail.com) writes:
> > > > > > I think the following clause is not correct:
> > > > > > > > > > CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1)
> > > > > > > > Yes, taking out of context it is grossly incorrect, because there is
> a
> > > > > right parathesis missing. This is the complete expression,
> reformatted
> > > > > for legibility:
> > > > > > > > CONVERT(smalldatetime,
> > > > > str(DATEPART(Month, @.DateNow) + 1) + '/' +
> > > > > str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) +
> '/'
> +
> > > > > str(DATEPART(Year, @.DateNow)),
> > > > > 101)
> > > > > > > > I did the same reflection as you, but I tried in QA to see what it
> > > > > would actually return. It took a while to grasp that syntax error...
> > > > > > > > Anway, not even the complete expression is good, because it does not
> > > > > work for dates like 2004-01-30.|||Sorry, I keep answering my own questions...
For weekday:
DatePart(dw,(@.as_date) Between 2 and 6
"Chuck Conover" <cconover@.commspeed.net> wrote in message news:<1075222542.653551@.news.commspeed.net>...
> Lauren,
> Truthfully, it took me about an hour to figure out this solution, given
> the complexity. So, it went thru many changes. @.s_year and @.s_date were
> used in an earlier version.
> To do "order by" when using a union, specify the column number instead
> of the column name like:
> order by 1 <-- to order by the first column
> order by 2,1 <-- to order by the second column, then the first column
> Best regards,
> Chuck Conover
> www.TechnicalVideos.net
>
> "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> news:47e5bd72.0401270819.19e6068@.posting.google.co m...
> > Chuck,
> > Thank you so much for the solution!
> > I modified your SP and included weekly and non-recurring events and it
> > works great.
> > I notice in your SP you included:
> > > declare @.s_year char(4)
> > > declare @.s_date char(5)
> > > > set @.s_date = substring(convert(varchar,@.as_date,120),6,5)
> > > set @.s_year = substring(convert(varchar,@.as_date,120),1,4)
> > ...though you don't use it in the code.
> > A question, I haven't used Union in SPs before. How can I do an ORDER
> > BY for the whole result. I have four select statements joined with the
> > unions.
> > Thanks,
> > lq
> > "Chuck Conover" <cconover@.commspeed.net> wrote in message
> news:<1075063730.111466@.news.commspeed.net>...
> > > Lauren,
> > > The keys to this problem are:
> > > 1. Let the database do the date math. It already knows what a valid
> date
> > > is and what, for example, 1/30/2004 + one month is. Let it do that
> work.
> > > > 2. Treat each reminder Interval as a different problem. Make each have
> > > it's own SQL and put them together with a UNION.
> > > > 3. Recognize that your SQL ends up comparing getdate() (ie: today)
> between
> > > 2 dates. For example, the monthly reminder would look like:
> > > > and getdate() between ThisMonthsReminder - DaysInAdvance
> > > and ThisMonthsReminder
> > > > So, you need to first figure out what this month's reminder is (for
> monthly
> > > reminders). This month's reminder is:
> > > > dateadd(mm, datediff(mm,reminder_date,getdate()), reminder_date)
> > > - the datediff figures out how many months between today and my
> reminder
> > > date.
> > > - the dateadd adds that number of months to my reminder date to give me
> > > ThisMonthsReminder
> > > > What I did to solve this is write a procedure which takes any date (not
> just
> > > today) as an arguement and gives you the annual and monthly reminders.
> The
> > > weekly and None Intervals should follow the same pattern.
> > > > Creating the tables and inserting the data were done like this:
> > > > create table tblMyTaskTable(reminder_date datetime,
> > > task varchar(100),
> > > reminder_interval varchar(30),
> > > days_in_advance integer)
> > > go
> > > insert into tblMyTaskTable values('2/28/1957','Joe Smith
> > > Birthday','Annual',7)
> > > go
> > > insert into tblMyTaskTable values('1/30/2004','Rent Due','Monthly','4')
> > > go
> > > insert into tblMyTaskTable values('2/27/2004','Dentist
> > > Appointment','None','3')
> > > go
> > > insert into tblMyTaskTable values('1/25/2004','Shrink
> > > Appointment','Weekly','2')
> > > go
> > > insert into tblMyTaskTable values('1/25/2004','Car Payment
> > > Due','Monthly','4')
> > > go
> > > > > The procedure looks like:
> > > create procedure get_reminders (@.as_date datetime)
> > > as
> > > begin
> > > > declare @.s_year char(4)
> > > declare @.s_date char(5)
> > > > set @.s_date = substring(convert(varchar,@.as_date,120),6,5)
> > > set @.s_year = substring(convert(varchar,@.as_date,120),1,4)
> > > > print @.s_date
> > > print @.s_year
> > > > select
> dateadd(yy,datediff(yy,reminder_date,@.as_date),rem inder_date),task
> > > from tblMytasktable
> > > where reminder_interval = 'Annual'
> > > and @.as_date between
> > > dateadd(dd,(-1)*days_in_advance,dateadd(yy,datediff(yy,reminder _date,@.as_dat
> > > e),reminder_date))
> > > and dateadd(yy,datediff(yy,reminder_date,@.as_date),rem inder_date)
> > > > union
> > > > select dateadd(mm,datediff(mm,reminder_date,@.as_date),rem inder_date),
> task
> > > from tblMytasktable
> > > where reminder_interval = 'Monthly'
> > > and @.as_date between
> > dateadd(dd,(-1)*days_in_advance,dateadd(mm,datediff(mm,reminder _date,@.as_dat
> > > e),reminder_date) )
> > > and dateadd(mm,datediff(mm,reminder_date,@.as_date),rem inder_date)
> > > > end
> > > > call the procedure for 2/26/04 like this:
> > > > exec get_reminders '2004-02-26'
> > > > The biggest problem with this procedure and the main query is that it
> cannot
> > > use an index on reminder_date. So, if you are using this with more than
> 200
> > > or so reminders, it could be slow, depending on your hardware.
> > > > Let me know if you need help with the weekly and None interval sections
> of
> > > the SQL. Also, you might find some help with SQL Server on our
> > > www.TechnicalVideos.net site.
> > > > Best regards,
> > > Chuck Conover
> > > www.TechnicalVideos.net
> > > > > > > > "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> > > news:47e5bd72.0401241342.6931ae64@.posting.google.c om...
> > > > To further elaborate:
> > > > > > tblMyTaskTable:
> > > > > > Date Task ReminderInterval
> > > > ReminderDaysinAdvance
> > > > 2/28/1957 Joe Smith Birthday Annual 7
> > > > 1/30/2004 Rent Due Monthly 4
> > > > 2/27/2004 Dentist Appointment None 3
> > > > 1/25/2004 Shrink Appointment Weekly 2
> > > > 1/25/2004 Car Payment Due Monthly 4
> > > > > > What I'm trying to produce is a form that shows this list when a user
> > > > logs in on Feb. 26:
> > > > > > Feb. 25 Shrink Appointment
> > > > Feb. 27 Dentist Appointment
> > > > Feb. 28 Joe Smith Birthday (1957)
> > > > Feb. 30 Rent Due (except there's no Feb. 30 so I have to deal with
> > > > that too!)
> > > > > > (the car payment won't show up because today is Feb. 26 and the next
> > > > reminder won't trigger until four days before the 25th of the next
> > > > month.)
> > > > > > This is proving to be very complex!
> > > > > > Any help is appreciated.
> > > > lq
> > > > > > > > > > > > > > > > > > > > Erland Sommarskog <sommar@.algonet.se> wrote in message
> news:<Xns947A9EA01D044Yazorman@.127.0.0.1>...
> > > > > Mystery Man (PromisedOyster@.hotmail.com) writes:
> > > > > > I think the following clause is not correct:
> > > > > > > > > > CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1)
> > > > > > > > Yes, taking out of context it is grossly incorrect, because there is
> a
> > > > > right parathesis missing. This is the complete expression,
> reformatted
> > > > > for legibility:
> > > > > > > > CONVERT(smalldatetime,
> > > > > str(DATEPART(Month, @.DateNow) + 1) + '/' +
> > > > > str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) +
> '/'
> +
> > > > > str(DATEPART(Year, @.DateNow)),
> > > > > 101)
> > > > > > > > I did the same reflection as you, but I tried in QA to see what it
> > > > > would actually return. It took a while to grasp that syntax error...
> > > > > > > > Anway, not even the complete expression is good, because it does not
> > > > > work for dates like 2004-01-30.|||Lauren,
Now you're getting complicated. Give me an example of what you're
looking for and I might be able to work up a solution.
Best regards,
Chuck
www.TechnicalVideos.net
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0401271845.e4d9bff@.posting.google.co m...
> Chuck,
> So what if I want to include a recurring pattern for Weekday reminders?
> I see T-SQL "dw" would I just substitute that for "ww" ?
> lq
>
> "Chuck Conover" <cconover@.commspeed.net> wrote in message
news:<1075222542.653551@.news.commspeed.net>...
> > Lauren,
> > Truthfully, it took me about an hour to figure out this solution,
given
> > the complexity. So, it went thru many changes. @.s_year and @.s_date
were
> > used in an earlier version.
> > To do "order by" when using a union, specify the column number
instead
> > of the column name like:
> > order by 1 <-- to order by the first column
> > order by 2,1 <-- to order by the second column, then the first column
> > Best regards,
> > Chuck Conover
> > www.TechnicalVideos.net
> > "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> > news:47e5bd72.0401270819.19e6068@.posting.google.co m...
> > > Chuck,
> > > Thank you so much for the solution!
> > > I modified your SP and included weekly and non-recurring events and it
> > > works great.
> > > I notice in your SP you included:
> > > > > declare @.s_year char(4)
> > > > declare @.s_date char(5)
> > > > > > set @.s_date = substring(convert(varchar,@.as_date,120),6,5)
> > > > set @.s_year = substring(convert(varchar,@.as_date,120),1,4)
> > > > ...though you don't use it in the code.
> > > > A question, I haven't used Union in SPs before. How can I do an ORDER
> > > BY for the whole result. I have four select statements joined with the
> > > unions.
> > > Thanks,
> > > lq
> > > > > "Chuck Conover" <cconover@.commspeed.net> wrote in message
> > news:<1075063730.111466@.news.commspeed.net>...
> > > > Lauren,
> > > > The keys to this problem are:
> > > > 1. Let the database do the date math. It already knows what a
valid
> > date
> > > > is and what, for example, 1/30/2004 + one month is. Let it do that
> > work.
> > > > > > 2. Treat each reminder Interval as a different problem. Make each
have
> > > > it's own SQL and put them together with a UNION.
> > > > > > 3. Recognize that your SQL ends up comparing getdate() (ie: today)
> > between
> > > > 2 dates. For example, the monthly reminder would look like:
> > > > > > and getdate() between ThisMonthsReminder - DaysInAdvance
> > > > and ThisMonthsReminder
> > > > > > So, you need to first figure out what this month's reminder is (for
> > monthly
> > > > reminders). This month's reminder is:
> > > > > > dateadd(mm, datediff(mm,reminder_date,getdate()), reminder_date)
> > > > - the datediff figures out how many months between today and my
> > reminder
> > > > date.
> > > > - the dateadd adds that number of months to my reminder date to
give me
> > > > ThisMonthsReminder
> > > > > > What I did to solve this is write a procedure which takes any date
(not
> > just
> > > > today) as an arguement and gives you the annual and monthly
reminders.
> > The
> > > > weekly and None Intervals should follow the same pattern.
> > > > > > Creating the tables and inserting the data were done like this:
> > > > > > create table tblMyTaskTable(reminder_date datetime,
> > > > task varchar(100),
> > > > reminder_interval varchar(30),
> > > > days_in_advance integer)
> > > > go
> > > > insert into tblMyTaskTable values('2/28/1957','Joe Smith
> > > > Birthday','Annual',7)
> > > > go
> > > > insert into tblMyTaskTable values('1/30/2004','Rent
Due','Monthly','4')
> > > > go
> > > > insert into tblMyTaskTable values('2/27/2004','Dentist
> > > > Appointment','None','3')
> > > > go
> > > > insert into tblMyTaskTable values('1/25/2004','Shrink
> > > > Appointment','Weekly','2')
> > > > go
> > > > insert into tblMyTaskTable values('1/25/2004','Car Payment
> > > > Due','Monthly','4')
> > > > go
> > > > > > > > The procedure looks like:
> > > > create procedure get_reminders (@.as_date datetime)
> > > > as
> > > > begin
> > > > > > declare @.s_year char(4)
> > > > declare @.s_date char(5)
> > > > > > set @.s_date = substring(convert(varchar,@.as_date,120),6,5)
> > > > set @.s_year = substring(convert(varchar,@.as_date,120),1,4)
> > > > > > print @.s_date
> > > > print @.s_year
> > > > > > select
> > dateadd(yy,datediff(yy,reminder_date,@.as_date),rem inder_date),task
> > > > from tblMytasktable
> > > > where reminder_interval = 'Annual'
> > > > and @.as_date between
> > > > dateadd(dd,(-1)*days_in_advance,dateadd(yy,datediff(yy,reminder _date,@.as_dat
> > > > e),reminder_date))
> > > > and
dateadd(yy,datediff(yy,reminder_date,@.as_date),rem inder_date)
> > > > > > union
> > > > > > select
dateadd(mm,datediff(mm,reminder_date,@.as_date),rem inder_date),
> > task
> > > > from tblMytasktable
> > > > where reminder_interval = 'Monthly'
> > > > and @.as_date between
> > dateadd(dd,(-1)*days_in_advance,dateadd(mm,datediff(mm,reminder _date,@.as_dat
> > > > e),reminder_date) )
> > > > and
dateadd(mm,datediff(mm,reminder_date,@.as_date),rem inder_date)
> > > > > > end
> > > > > > call the procedure for 2/26/04 like this:
> > > > > > exec get_reminders '2004-02-26'
> > > > > > The biggest problem with this procedure and the main query is that
it
> > cannot
> > > > use an index on reminder_date. So, if you are using this with more
than
> > 200
> > > > or so reminders, it could be slow, depending on your hardware.
> > > > > > Let me know if you need help with the weekly and None interval
sections
> > of
> > > > the SQL. Also, you might find some help with SQL Server on our
> > > > www.TechnicalVideos.net site.
> > > > > > Best regards,
> > > > Chuck Conover
> > > > www.TechnicalVideos.net
> > > > > > > > > > > > > > "Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
> > > > news:47e5bd72.0401241342.6931ae64@.posting.google.c om...
> > > > > To further elaborate:
> > > > > > > > tblMyTaskTable:
> > > > > > > > Date Task ReminderInterval
> > > > > ReminderDaysinAdvance
> > > > > 2/28/1957 Joe Smith Birthday Annual 7
> > > > > 1/30/2004 Rent Due Monthly 4
> > > > > 2/27/2004 Dentist Appointment None 3
> > > > > 1/25/2004 Shrink Appointment Weekly 2
> > > > > 1/25/2004 Car Payment Due Monthly 4
> > > > > > > > What I'm trying to produce is a form that shows this list when a
user
> > > > > logs in on Feb. 26:
> > > > > > > > Feb. 25 Shrink Appointment
> > > > > Feb. 27 Dentist Appointment
> > > > > Feb. 28 Joe Smith Birthday (1957)
> > > > > Feb. 30 Rent Due (except there's no Feb. 30 so I have to deal
with
> > > > > that too!)
> > > > > > > > (the car payment won't show up because today is Feb. 26 and the
next
> > > > > reminder won't trigger until four days before the 25th of the next
> > > > > month.)
> > > > > > > > This is proving to be very complex!
> > > > > > > > Any help is appreciated.
> > > > > lq
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > Erland Sommarskog <sommar@.algonet.se> wrote in message
> > news:<Xns947A9EA01D044Yazorman@.127.0.0.1>...
> > > > > > Mystery Man (PromisedOyster@.hotmail.com) writes:
> > > > > > > I think the following clause is not correct:
> > > > > > > > > > > > CONVERT(smalldatetime,str(DATEPART(Month, @.DateNow)+1)
> > > > > > > > > > Yes, taking out of context it is grossly incorrect, because
there is
> > a
> > > > > > right parathesis missing. This is the complete expression,
> > reformatted
> > > > > > for legibility:
> > > > > > > > > > CONVERT(smalldatetime,
> > > > > > str(DATEPART(Month, @.DateNow) + 1) + '/' +
> > > > > > str(DATEPART(Day,
tblMyEventTableName.TaskDateTime)) +
> > '/'
> > +
> > > > > > str(DATEPART(Year, @.DateNow)),
> > > > > > 101)
> > > > > > > > > > I did the same reflection as you, but I tried in QA to see what
it
> > > > > > would actually return. It took a while to grasp that syntax
error...
> > > > > > > > > > Anway, not even the complete expression is good, because it does
not
> > > > > > work for dates like 2004-01-30.|||Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> Thanks for all the help. The reminder calendar is working slendidly
> now. (Unfortunately it already worked splendidly on an MS Access2K.MDB
> with VBA before with many hours of trial and error. So much to redo in
> SSQL Server...)
> I made a brain dead mistake on the Order By and now it works, but it
> leads me to another question.
> Since the SP is many lines long, it seems very silly to copy the whole
> SP into a another SP just to change the sort order. Is there a
> simpler solution?
> I suppose I could pass it a tinyint and a nvarchar parameter to handle
> the sorting:
> @.myColumn tinyint, @.myOrder nvarchar(4) = null
> ...
> ORDER BY @.myColumn + ' ' + @.myOrder
> But this could get very complex with multiple column sorting
> variations.
> Is there a better way?
I don't know what sort orders you are looking for, but the above ORDER BY
clause is equal to no ORDER BY clause at all, because you sort by a constant
value.
The standard way of dynamic sorting in T-SQL is to use a case expression:
CASE @.mycolumn
WHEN 1 THEN this_col
WHEN 2 THEN that_col
WHEN 3 THEN that_col_other_there
END
But this only works if all columns are of the same data type, since the
CASE expression has a fixed data type determined by some rules I don't
recall right now.
This can be addressed with:
ORDER BY CASE @.mycolumn WHEN 1 THEN date_col ELSE NULL END,
CASE @.mycolumn WHEN 2 THEN str_col ELSE NULL END,
CASE @.mycolumn WHEN 3 THEN int_col ELSE NULL EMD
And yes, if you have very many columns that you want to permit sorting
on, this can become very complex. One alternative is do to the sorting
client-side. This had the advantage that you can rearrange the data
for the user, without doing a server round-trip.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
No comments:
Post a Comment