DateTime Query

Hi, am trying to build a scheduling system within my SQL Server application. Can someone point me in a good direction please?

OK, A user can select that they want something to happen Weekly, and on each Tuesday of every week. They of course can select any day from Monday through to Sunday. I would like to know how to take this data, and through a stored procedure update a table to set the "next execution date".

I have sorted the Daily timetable for each time, and the Monthly on a certain date seems easy enough, but I cant get the Weekly on a certain Day sorted. Any advice would be great!

Maybe you could post some code of your table and query...?

Though I'm not sure why you have multiple tables; monthly, weekly, daily.

You should just need one

NextExecutionMgr( DueDate datetime, FreqIntvl varchar(2), FreqAmt int, RecordKey varchar(200) )

index on DueDate, most likely a second index on RecordKey

The first item on your DueDate index is the next one to be processed.

When its time comes and once it is processesed you just adjust the date:

Code Snippet

case FreqIntvl when 'dy' then DueDate = DateAdd(dy, FreqAmt, DueDate)

when 'wk' then DueDate = DateAdd(wk, FreqAmt, DueDate)

etc.

end

(doesn't it suck that dateadd doesn't accept a variable for parameter one?)

|||

Why re-invent the wheel?

I would recommend exploring the SQL Agent Service, since it has full features calendaring and scheduling already built-in.

And if you are using SQL 2005 Express, which doens't include SQL Agent, you could explore a combination of using the Windows Scheduler service and SQLCmd.exe.

|||

Arnie, quite true.

I guess it just depends on what it is he's trying to schedule.

Agent is perfect for scheduled system level events and tasks.

But if he's trying to kick off application events with 1,000's of users, that a different thing.

Lotsa cats...

|||

And the skin just regrows...

I suspect that the solution will evolve into a combination of efforts -your outline about how to manage a 'queue' table, and some form of a scheduled process to 'POP' the queue.

There just isn't enough information to point the OP in the 'best' direction. SQL Agent, Notification Service, Service Broker Queues, some 'homegrown' hybrid, ...

Daterange for a daterange

It's late and I'm having a hard time figuring out how the heck to build my where clause.

Here's a sample table with data:

ID int
Value int
StartDate DateTime
EndDate DateTime

1 | 100 | 1/1/2004 | 1/23/2004
2 | 200 | 1/23/2004 | NULL

For all intents and purposes, the second record has a null end date because it's valid until a new value is entered. If I were to update the value again, the 3rd record would look like this.

3 | 300 | 1/24/2004 | NULL

And, since this was updated, I'd go back and update the 2nd record so that I know the End Date (the 3rd record's start date)

2 | 200 | 1/23/2004 | 1/24/2004

Ok, with that said, my application looks at each week in a year, and looks for a valid value for the given date. I need to say "for this week, give me the value." If 2 values fall within the given week, I want to grab the highest (MAX) value.

Any ideas on how I'd structure the SQL statement for this? The where clause is where I'm having funny (hey, that's kinda funny -- where and where).

Anyway, I appreciate any help that you all can give me on this one. It's getting late and my brain is burnt out for the day!Anybody? Sorry to bump this, I just though I may have had a bite or two by now.|||checking your clause...
hope is on the way !|||"for this week, give me the value."
If 2 values fall within the given week,
I want to grab the highest (MAX) value.

what is the value that you're talkign about ?
startdate ?|||ID int
Value int <-- [ this is what I'm getting ]
StartDate DateTime [ used in where clause ]
EndDate DateTime [ used in where clause ]|||and when you say "this week"
what kind of parameter are you sending

date ?
no of week in year ?|||A date range.

i.e. 2/8/2004 - 2/14/2004|||is this what your looking for ?

select max(value)
from range
where '2/8/2004' between startdate and enddate or
'2/14/2004' between startdate and enddate|||Hmm, that may work, BUT, there is still the situation where the most recent value does not have an enddate. I guess I'm going to need to OR that in. Any ideas on that?|||maybe better ?

select *
from range
where '2/8/2004' between startdate and isnull(enddate,'1/1/2999') or
'2/14/2004' between startdate and isnull(enddate,'1/1/2999')|||magical transformation of null to a far-far-far-date|||I think that will work out just fine! :)

Thanks a bunch, I'll work in implementation and check back if I forgot something. You have helped me past my brain fart, thanks!|||on sql i'm good enough to help a bit

Datepart curiosity.

Help,
I have build a Bip portal (Analysertool) wich gets information out of sql
server. I have a date field over there.
When the Analiser tools detects a date field it construkts 2 new fields...
field bij year and field by w. BUT this are the our w nummers + 1. So
the w numbers are not correct. I tried making a vieuw in SQL server. But
the function Datepart() gives me the same w number! Does anyone know a
solution'
Tnx in advanceDatepart doesn't calculate w numbers according to the ISO standard. Searc
h Books Online for the
ISOW function instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Judith van der Niet" <jniet@.mit.com> wrote in message
news:eW%235ZsrLFHA.568@.TK2MSFTNGP09.phx.gbl...
> Help,
> I have build a Bip portal (Analysertool) wich gets information out of sql
server. I have a date
> field over there.
> When the Analiser tools detects a date field it construkts 2 new fields...
. field bij year and
> field by w. BUT this are the our w nummers + 1. So the w numbers
are not correct. I tried
> making a vieuw in SQL server. But the function Datepart() gives me the sam
e w number! Does
> anyone know a solution'
> Tnx in advance
>|||You can use "SET DATEFIRST" to set the first day of the w to your
desired day.
If you want monday to be the first day then use
SET DATEFIRST 1
If you want tuesday to be the first then
SET DATEFIRST 2 .
and so on ...
Bala|||SET DATEFIRST Can be used for setting the startt day of the w.|||Note that DATEPART still won't return ISO w numbers if DATEFIRST is
set to 1. For that you should use the ISOWEEK function given in Books
Online as Tibor suggested.
David Portas
SQL Server MVP
--

DateAdd

Hello Everyone,
I trying to build an SQL statement that includes the last twelve
months in the report. When I use the DATEADD function ie >= DATEADD(MM, - 12, GETDATE())) the report brings back data 12 months
from the current date. I would like to include the whole month not
just the current date. I hope this make sense!
R/ A. AkinHello awakin,
You need to take of the day of the month as well day(getdate()) returns the
day of the month but the simplest is to build the date from scratch i.e
convert(datetime, cast((year(getdate())-1) *100 + month(getdate())as char(6))
+ '01' ,112)
This builds a date string in the ISO format yyyymmdd and then converts it
to a date using the relevant coversion style for the ISO format
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> Hello Everyone,
> I trying to build an SQL statement that includes the last twelve
> months in the report. When I use the DATEADD function ie >=> DATEADD(MM, - 12, GETDATE())) the report brings back data 12 months
> from the current date. I would like to include the whole month not
> just the current date. I hope this make sense!
> R/ A. Akin
>|||On Sep 30, 3:47 am, Simon Sabin <SimonSa...@.noemail.noemail> wrote:
> Hello awakin,
> You need to take of the day of the month as well day(getdate()) returns the
> day of the month but the simplest is to build the date from scratch i.e
> convert(datetime, cast((year(getdate())-1) *100 + month(getdate())as char(6))
> + '01' ,112)
> This builds a date string in the ISO format yyyymmdd and then converts it
> to a date using the relevant coversion style for the ISO format
> Simon Sabin
> SQL Server MVPhttp://sqlblogcasts.com/blogs/simons
>
> > Hello Everyone,
> > I trying to build an SQL statement that includes the last twelve
> > months in the report. When I use the DATEADD function ie >=> > DATEADD(MM, - 12, GETDATE())) the report brings back data 12 months
> > from the current date. I would like to include the whole month not
> > just the current date. I hope this make sense!
> > R/ A. Akin- Hide quoted text -
> - Show quoted text -
Simon,
Thanks for the response; here is what worked for me.
>= DATEADD(MM, - 12, CAST(CAST(YEAR(GETDATE()) AS VARCHAR) + ' - ' +
CAST(MONTH(GETDATE()) AS VARCHAR) + '-01' AS DATETIME)))
This allowed me to return the appropriate data.
r/ A.Akin