Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SandyC
Starting Member
1 Post |
 Posted - 2002-08-20 : 16:25:44
|
| Hi all,Please be patient - I am a total newbie! I have read thru posts and did not find exactly what I need. This will be soooo simple for you guys!We have a customer that sends orders via ftp 6 times a day. Using VB we are parsing and updating our SQL 2000 database with brand new records, they are timestamped using system date/time. I need to schedule a job to run every 2 hours to check for this customer, compare the date/time field with the system date/time to see if two hours has gone by without a new record for this customer. Also, I have to send a netsend to me if that is the case, otherwise, the scheduler will do this again in 2 hours.I can get the date/times to compare...select max(order_date)as LastOrdDate, getdate() as NowDatefrom order_datawhere customer_id = 743(here is where I don't know the syntax) If the difference between LastOrdDate and NowDate > 2 hours then netsend to Sandy 'Yikes - 743 has not sent anything for 2 hours! else get outta here.I have done this in other programming languages, but do not know the syntax for SQL help!Thanks in advance! SandyC |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-08-20 : 16:48:29
|
| You shouldprobably schedule every 15 mins - as it is 4 hrs can go by before you get a warning.if dateadd(hh, -2, getdate() > (select max(order_date)as LastOrdDatefrom order_data where customer_id = 743)or for all of themselect customer_idfrom order_data group by customer_idhaving dateadd(hh, -2, getdate() > max(order_date)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
Checking for Updates