SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Run of query only first day of year
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Junior Sqler
Starting Member

18 Posts

Posted - 12/19/2013 :  05:32:08  Show Profile  Reply with Quote
Hello!
I have created a table Abc and i want to update its rows only every first day of year. The following is not working.Could you help me?

CREATE TABLE Abc ( A int,B int,C int)

insert into Abc
case when month(getdate())=1 and day(getdate()=1 then
(select a1, b1, c1
from dbo.table a
where a1= '###')
end

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 12/19/2013 :  06:11:57  Show Profile  Reply with Quote
CREATE TABLE Abc ( A int,B int,C int)

insert into Abc
select a1, b1, c1
from dbo.table a
where a1= '###'
AND month(getdate())=1 and day(getdate()=1


Do you want to manually run the above script or want to set automatic run by SQL Server Agent Jobs?

--
Chandu
Go to Top of Page

Junior Sqler
Starting Member

18 Posts

Posted - 12/19/2013 :  06:25:55  Show Profile  Reply with Quote
This script is part of code that will run automatically daily by sql server agent jobs.. but this specific script will need to run only once per year
The problem is that variable C=avg(cost) of previous year for a specific category(b1).. For example if avg cost for yr 2012 is 100 in table ABC i would like to have A=2013 B(name of category) and C=100..
For that reason i do not think that your proposal will work for me.. Actually the real code is like this:

CREATE TABLE Abc ( A date, B varchar(20),C int)

insert into Abc
case when month(getdate())=1 and day(getdate()=1 then
(select a1,b1 avg(c1)
from dbo.table a
where a1= YEAR(GetDate())-1
groub by a1,b1)
end
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 12/19/2013 :  06:32:01  Show Profile  Reply with Quote
Scipt part so you can make use of IF...ELSE statement

if (month(getdate())=1 and day(getdate()=1 )
BEGIN
insert into Abc
(select a1,b1 avg(c1)
from dbo.table a
where a1= YEAR(GetDate())-1
groub by a1,b1)
end


--
Chandu
Go to Top of Page

Junior Sqler
Starting Member

18 Posts

Posted - 12/19/2013 :  06:45:13  Show Profile  Reply with Quote
oook!!thank you very much!!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 12/19/2013 :  06:48:31  Show Profile  Reply with Quote
welcome

--
Chandu
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
409 Posts

Posted - 12/19/2013 :  18:33:20  Show Profile  Reply with Quote
if datepart(dayofyear, getdate()) = 1
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 12/24/2013 :  08:02:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
ScottPletcher's code is simpler. That's definitely a celver use of datepart function

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000