Update table y month

Last Post 13 May 2009 07:16 AM by JASBRYDEN. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
JASBRYDEN
New Member
New Member

--
12 May 2009 11:31 AM
I need some help with a SQL 2000 code.

I have a table that has buckets for 12 months, our fiscal year runs from April - March, so month 1 is April, month 2 is May and etc.


I want to create an update statement that would doing the following

check todays date, since we are in the month of May it should pull the data from month 2 and update the account_anaylsis table.

if we are in the month of June it should pull the data from month 3 and update the account_analysis table and so on.

How do I create a script to help me do this ??


update account_anaylsis
set account_balance= ??
update account_anaylsis
left join on glaltj2=gl_number



GJDEL GJCMP GJGLNO GJALTJ GLALTJ1 GLALTJ2 GLALTJ3 GLACCOUNT GJNAM GLDESC CURRENT_BALANCE_FORWARD CURR_YEAR_CUMM_TO_01 CURR_YEAR_CUMM_TO_02 CURR_YEAR_CUMM_TO_03 CURR_YEAR_CUMM_TO_04 CURR_YEAR_CUMM_TO_05 CURR_YEAR_CUMM_TO_06 CURR_YEAR_CUMM_TO_07 CURR_YEAR_CUMM_TO_08 CURR_YEAR_CUMM_TO_09 CURR_YEAR_CUMM_TO_10 CURR_YEAR_CUMM_TO_11 CURR_YEAR_CUMM_TO_12 CURR_YEAR_CUMM_TO_13 CURR_YEAR_CUMM_TO_14 BALANCE_SHEET PROFIT_AND_LOSS INCOME EXPENSE ASSETS CURRENT_AS_LIB LIABILITIES EQUITY

A 1 100000315 00000001100000000020 00 11000 20 00 / 11000 / 20 Freehold Land-Cost/Valu [00 / 11000 / 20] Freehold Land-Cost/Valu 1357324.58 1357324.58 1357324.58 1357324.58 1357324.58 1357324.58 1357324.58 1357324.58 1357324.58 1357324.58 1357324.58 1357324.58 1357324.58 .00 1357324.58 11000 NULL NULL NULL 11000 NULL NULL NULL
A 1 100000317 00000001101000000020 00 11010 20 00 / 11010 / 20 Freehold Bldg-Cost/Valu [00 / 11010 / 20] Freehold Bldg-Cost/Valu 5585540.69 5585540.69 5585540.69 5585540.69 5585540.69 5585540.69 5585540.69 5585540.69 5585540.69 5585540.69 5585540.69 5585540.69 5585540.69 .00 5585540.69 11010 NULL NULL NULL 11010 NULL NULL NULLText
JASBRYDEN
New Member
New Member

--
13 May 2009 07:16 AM
This is my script that I am using, it is working just wanted to know if I could use something else instead of the hardcode date

--April
IF CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)>='2009-04-01' and CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)<='2009-04-30'
update dbo.ASB_ACCOUNT_ANALYSIS
set ACCOUNT_BALANCE=CURR_YEAR_CUMM_TO_01
from dbo.ASB_ACCOUNT_ANALYSIS
inner join dbo.ASB_VGLLMST on
gjcmp=company and glaltj2=gl_number

--May
IF CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)>='2009-05-01' and CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)<='2009-05-31'
update dbo.ASB_ACCOUNT_ANALYSIS
set ACCOUNT_BALANCE=CURR_YEAR_CUMM_TO_02
from dbo.ASB_ACCOUNT_ANALYSIS
inner join dbo.ASB_VGLLMST on
gjcmp=company and glaltj2=gl_number

--June
IF CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)>='2009-06-01' and CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)<='2009-06-30'
update dbo.ASB_ACCOUNT_ANALYSIS
set ACCOUNT_BALANCE=CURR_YEAR_CUMM_TO_03
from dbo.ASB_ACCOUNT_ANALYSIS
inner join dbo.ASB_VGLLMST on
gjcmp=company and glaltj2=gl_number

--July
IF CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)>='2009-07-01' and CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)<='2009-07-31'
update dbo.ASB_ACCOUNT_ANALYSIS
set ACCOUNT_BALANCE=CURR_YEAR_CUMM_TO_04
from dbo.ASB_ACCOUNT_ANALYSIS
inner join dbo.ASB_VGLLMST on
gjcmp=company and glaltj2=gl_number

--August
IF CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)>='2009-08-01' and CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)<='2009-08-31'
update dbo.ASB_ACCOUNT_ANALYSIS
set ACCOUNT_BALANCE=CURR_YEAR_CUMM_TO_05
from dbo.ASB_ACCOUNT_ANALYSIS
inner join dbo.ASB_VGLLMST on
gjcmp=company and glaltj2=gl_number

--September
IF CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)>='2009-09-01' and CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)<='2009-09-30'
update dbo.ASB_ACCOUNT_ANALYSIS
set ACCOUNT_BALANCE=CURR_YEAR_CUMM_TO_06
from dbo.ASB_ACCOUNT_ANALYSIS
inner join dbo.ASB_VGLLMST on
gjcmp=company and glaltj2=gl_number

--October
IF CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)>='2009-10-01' and CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)<='2009-10-31'
update dbo.ASB_ACCOUNT_ANALYSIS
set ACCOUNT_BALANCE=CURR_YEAR_CUMM_TO_07
from dbo.ASB_ACCOUNT_ANALYSIS
inner join dbo.ASB_VGLLMST on
gjcmp=company and glaltj2=gl_number

--November
IF CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)>='2009-11-01' and CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)<='2009-11-31'
update dbo.ASB_ACCOUNT_ANALYSIS
set ACCOUNT_BALANCE=CURR_YEAR_CUMM_TO_08
from dbo.ASB_ACCOUNT_ANALYSIS
inner join dbo.ASB_VGLLMST on
gjcmp=company and glaltj2=gl_number

--December
IF CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)>='2009-12-01' and CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)<='2009-12-31'
update dbo.ASB_ACCOUNT_ANALYSIS
set ACCOUNT_BALANCE=CURR_YEAR_CUMM_TO_09
from dbo.ASB_ACCOUNT_ANALYSIS
inner join dbo.ASB_VGLLMST on
gjcmp=company and glaltj2=gl_number

--January
IF CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)>='2009-01-01' and CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)<='2009-01-31'
update dbo.ASB_ACCOUNT_ANALYSIS
set ACCOUNT_BALANCE=CURR_YEAR_CUMM_TO_10
from dbo.ASB_ACCOUNT_ANALYSIS
inner join dbo.ASB_VGLLMST on
gjcmp=company and glaltj2=gl_number

--February
IF CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)>='2009-02-01' and CAST(FLOOR(CAST(getdate() AS float))AS smalldatetime)<='2009-02-28'
update dbo.ASB_ACCOUNT_ANALYSIS
set ACCOUNT_BALANCE=CURR_YEAR_CUMM_TO_11
fr


Acceptable Use Policy
---