Facing Problem in Week Auto Update

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)

Facing Problem in Week Auto Update

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2009-08-14 : 00:26:18

Respected Coders,

I had a table name attendance_date, where the columns are:
attendance_dateID (numeric, 9)
date_from (datetime)
date_to (datetime)

Now in columns date_from and date_to, I had used the following formula for generating first day and the last day of a particular week:


date_from = (dateadd(day,(datediff(day,(-53684),getdate()) / 7 * 7 + 1),(-53684)))
date_to = (dateadd(day,(datediff(day,(-53684),getdate()) / 7 * 7 + 7),(-53684)))

Now, everything working fine, but the problem is whenever the new week coming, the last week date range in automatically converting to present week date range, e.g.
Last week, I inserted the following range: 03/08/2009 to 09/08/2009
But Present week, the last range is automatically converted to present week range: 10/08/2009 to 16/08/2009
This I don't want, I want every week should be static, that once inserted should not be converted to present week range.

PLEASE HELP,am in great need...

Daipayan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-14 : 00:30:49

quote:
Last week, I inserted the following range: 03/08/2009 to 09/08/2009
But Present week, the last range is automatically converted to present week range: 10/08/2009 to 16/08/2009

What do you mean exactly ?

The record you inserted last week, the date_from and date_to was change to 10/08/2009 to 16/08/2009 ?

KH
[spoiler]Time is always against us[/spoiler]

daipayan
Posting Yak Master

181 Posts

Posted - 2009-08-14 : 00:36:58

Sir,

Let me explain you with an example:
Let take present week range is 10/08/2009 to 16/08/2009
so on 09/08/2009, I inserted this week range in the respective columns.
Now, on 16/08/2009, that range is automatically converted to 17/08/2009 to 23/08/2009, this conversion I don't want. I want every week, I should input the week range, keeping the old week range in the column.
HOPE I MAKE YOU UNDERSTAND MY PROBLEM!

Daipayan

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-14 : 00:41:14

is there a SQL Agent job running that is updating the records?

daipayan
Posting Yak Master

181 Posts

Posted - 2009-08-14 : 00:44:42

quote:
Originally posted by russell

is there a SQL Agent job running that is updating the records?

Yes Sir,SQL Agent Job is running because I had activated auto-backup facility in the database maintainance

Daipayan

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-14 : 00:53:55

i mean is there some job running that is updating the records you inserted. thinking maybe it's missing a WHERE clause...

daipayan
Posting Yak Master

181 Posts

Posted - 2009-08-14 : 00:59:56

quote:
Originally posted by russell

i mean is there some job running that is updating the records you inserted. thinking maybe it's missing a WHERE clause...

Yes Sir,
I had a TRIGGER to update some tables in DB, but there is no JOB.

Daipayan

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-14 : 01:13:19

is that trigger updating your table?

daipayan
Posting Yak Master

181 Posts

Posted - 2009-08-14 : 01:24:05

quote:
Originally posted by russell

is that trigger updating your table?

The TRIGGER UPDATING SOME OTHER TABLES BY TAKING THE DATA FROM routine_date TABLE

Daipayan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-14 : 05:09:00

[code]
date_from = dateadd(day, datediff(day, '1753-01-01', getdate()) / 7 * 7, '1753-01-01'),
date_to = dateadd(day, datediff(day, '1753-01-01', getdate()) / 7 * 7 + 6, '1753-01-01')
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources