Sproc Issue ! - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sproc Issue !

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-01-03 : 07:39:11

Hi there Guys.
I have a stored procedure that extracts data out of a members table and places all data that has been entered into this table less or equal than a day old into a memberinfo2 table.

Here is my SProc code :

CREATE procedure membernumallocate
as
declare
@maxnum integer,
@surname char(20),
@forenames char(20),
@addr1 char(30),
@addr2 char(30)
@addr3 char (30)

drop table newmemberinfo2

select distinct * into newmemberinfo2
from newmembersinfo
where joindate >= getdate() - 2
GO

I have scheduled a batch file to run this spoc everynight at eight a clock in windows scheduled tasks to import all new members that have joined after 8:00 o clock the following evening via a web application.Now the problem I have is in the red text... , If I put where joindate >= getdate() - 1 which I thought would be the way I don't get any new members input into the newmemberinfo2 table I only get new members if I use joindate >= getdate() - 2 which so far has not given me any duplicates contrary to what I thought.

Could anyone please advise me if this is right ?
And if so why 2 days if I am only looking for new members that have joined only over the last 24 hours ?

thanks Guys.

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2006-01-03 : 07:49:59

Try using datediff function. Check Books online for more information.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-03 : 07:59:19

Is your joindate contain date & time ? getdate() will return you current date & time.
The following statement will strip off the time component of the current date

dateadd(day, 0, datediff(day, 0, getdate()))

So this will give you getdate() - 1 without the time (or actually 00:00:00)

where joindate >= dateadd(day, 0, datediff(day, 1, getdate()))

-----------------
[KH]
2006 a new beginning

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 08:03:38

where joindate >=dateadd(day,datediff(day,0,getdate()),-2)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-03 : 08:58:37

Dunno why, but that scares me Madhi! I would do

where joindate >=dateadd(day,datediff(day,0,getdate())-2, 0)

Kristen

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-01-03 : 09:00:08

Ok thanks guys for all your input, I have been told to leave the sproc by my boss as he reckons it is working now and if it is not broken don't fix it type of attitude which is quiet frustrating to say the least...

I will have to do a couple of tests to see if I can find a instance when joindate >= getdate() - 2 fails to work properly..

Kristen
Test

22859 Posts

Posted - 2006-01-03 : 09:07:43

"... to see if I can find a instance when joindate >= getdate() - 2 fails to work properly"

Can people "JOIN" at 8 O'Clock ayt night?

What happens to someone who joins at 8PM and the scheduled task the next night runs a minute late?

What happens if the scheduled task fails one night? (SQL Service is stopped, for example)

For me I would want to store the MAX date of the most recently processed "joiner", and then run "from that date" the next time the task runs.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 09:18:06

quote:
Originally posted by Kristen

Dunno why, but that scares me Madhi! I would do

where joindate >=dateadd(day,datediff(day,0,getdate())-2, 0)

Kristen

Well Kris.
This returns same result


Select dateadd(day,datediff(day,0,date),-2) as 'Mine' ,dateadd(day,datediff(day,0,date-2),0) as 'Yours' from
(
Select getdate()-3 as date
union all
Select getdate()-1 as date
union all
Select getdate()-63 as date
union all
Select getdate() as date
) T

Madhivanan

Failing to plan is Planning to fail

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-01-03 : 09:18:49

Let me try and explain clearer Kristen as I don't think I had my initial facts right.

Every morning at one o clock a dts runs from a web application transforming data to the newmembersinfo table( this contails all new and exsisting members).After which this sp runs at three o clock in the morning using the above query.
This process runs again the very next day at the same time.
The web application is still up and running at these times and still allows new members to be input...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-03 : 10:27:44

Seems to me that if you want a time-based fool proof way of getting new memebers you need to store the datetime that you processed the last batch. In other words, your process should read the [LastProcessedDate] from a meta table and import all members since that date then, upon successfull completion of the current process, update the {LastProcessedDate] to the current process time.

this method will allow continuous new users without any overlap and without missing any.


--this table designed to hold only 1 row
create table ProcessMetadata (lastProcessDate datetime not null)
go

--initialize with last processed date
insert ProcessMetadata values (<yourBestGuess>)
go

declare @currProcess datetime
	,@lastProcess datetime

select	@curr = getdate()
	,@lastProcess = lastProcessDate
from	ProcessMetadata

--process new members
select distinct [explicitColumnList]
into	newmemberinfo2
from	newmembersinfo
where	joindate > @lastProcess
and	joindate <= @currProcess

--if no problems occured update metadata
update ProcessMetadata set
	lastProcessDate = @curr

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2006-01-03 : 10:30:10

Well, I'm afraid I don't quite "get it" Jim, but it sounds pretty scary to my aged ears ...

I'm prepared to be amazed in a years time when you tell me your boss was right and it has not been the source of lots of problems!

Kristen

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-01-04 : 05:20:06

where joindate >=dateadd(day,datediff(day,0,getdate())-2, 0)

Ok guys as always you all have been a great help but I have decided to go with Kristen and Madhi's idea and use the above code.The thing I can't get my head around is the getdate()) - 2 syntax why -2 when you only want - 1 day earlier.I know -2 works because I have tested it but I still can't grasp the concept of -2 days if it is getting run every 24 hrs and I only require 1 days new members entries.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-04 : 06:24:38

As Getdate() includes both Date and Time, you need to omit Time part to get proper result

Example 1 : Direct use of Getdate() in where clause


Select * from
(
Select getdate()-0.001 as today union all 
Select getdate()+0.02 union all 
Select getdate() 
) T where today=getdate()

Example 2 : Omit Time part



Select * from
(
Select getdate()-0.001 as today union all 
Select getdate()+0.02 union all 
Select getdate() 
) T where today>=DateAdd(day,Datediff(day,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-01-04 : 16:05:05

That is a great example Madhi, Lesson learned thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources