Need computed column based on month.

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 2008 Forums

Transact-SQL (2008)

Need computed column based on month.

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2012-01-20 : 12:10:53

I have a table like this:

CREATE TABLE #Test (
       [InstNumber]			VARCHAR(4)
      ,[GLLineNumber]		VARCHAR(10)
      ,[GLLineDescription]	VARCHAR(100)
      ,[NovemberBalance]	DECIMAL(23,2)
      ,[DecemberBalance]	DECIMAL(23,2)
      ,[JanuaryBalance]		DECIMAL(23,2)
      ,[FebruaryBalance]	DECIMAL(23,2)
      ,[MarchBalance]		DECIMAL(23,2)
      ,[AprilBalance]		DECIMAL(23,2)
      ,[MayBalance]			DECIMAL(23,2)
      ,[JuneBalance]		DECIMAL(23,2)
      ,[JulyBalance]		DECIMAL(23,2)
      ,[AugustBalance]		DECIMAL(23,2)
      ,[SeptemberBalance]	DECIMAL(23,2)
      ,[OctoberBalance]		DECIMAL(23,2)
      ,[CurrentBalance]		DECIMAL(23,2)
)

The column CurrentBalance is supposed to be the value contained in the appropriate column for LAST month. For example, CurrentBalance as of January should be the value in the column DecemberBalance. It could be, I suppose, done with a trigger or an update statement, or maybe a computed column. Any ideas?

Duane

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-20 : 13:20:45

can you normalize you table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2012-01-20 : 13:37:02

Good idea, but the structure is pretty rigid as I am migrating a data mart from SQL Server 2000 to 2008 with some limited enhancements. These are actually import and stage tables brought in through flat files with this structure.

Duane

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-20 : 13:38:42

[code][UPDATE #Table
SET DecemberBalance = JanuaryBalance[/code]

If that doesn't work can you post sample data and expected output?

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2012-01-20 : 14:04:58

What I want is something like this in pseudocode:
DECLARE @ReportDate DateTime
SET @ReportDate = GetDate()
IF Month(@ReportDate()) = 'January' THEN CurrentBalance = DecemberBalance
ELSE
IF Month(@ReportDate()) = 'February' THEN CurrentBalance = JanuaryBalance
ELSE
IF Month(@ReportDate()) = 'March' THEN CurrentBalance = FebruaryBalance
ELSE
IF Month(@ReportDate()) = 'April' THEN CurrentBalance = MarchBalance
ELSE
IF Month(@ReportDate()) = 'May' THEN CurrentBalance = AprilBalance
ELSE
IF Month(@ReportDate()) = 'June' THEN CurrentBalance = MayBalance
ELSE
IF Month(@ReportDate()) = 'July' THEN CurrentBalance = JuneBalance
ELSE
IF Month(@ReportDate()) = 'August' THEN CurrentBalance = JulyBalance
ELSE
IF Month(@ReportDate()) = 'September' THEN CurrentBalance = AugustBalance
ELSE
IF Month(@ReportDate()) = 'October' THEN CurrentBalance = SeptemberBalance
ELSE
IF Month(@ReportDate()) = 'November' THEN CurrentBalance = OctoberBalance
ELSE
IF Month(@ReportDate()) = 'December' THEN CurrentBalance = NovemberBalance
END IF

The data really can be any numerical random values in the monthly columns. Right now the last column is empty and I want to fill it with the appropriate value from the correct montly column. Let's say for a given row or InstNumber, that NovemberBalance = $1,000, DecemberBalance is $159.34, and DecemberBalance = $229.11. If the column gets inserted/updated in January, then CurrentBalance will be cleared and set to $159.34. Next month, in February, it will be set to $229.11. LAST month (in December, it would have been set to $1,000, the value in the NovemberColumn. No sums or other calculations. Just a copy of the value in the appropriate Month column to CurrentBalance.
I think that I am almost answering my own question. Maybe I need a CASE statement if I can figure out how to get it correctly.

Duane

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2012-01-20 : 15:08:19

Actually, I figured it out and it works. Let me know if you see any pitfalls or improvements.

DECLARE @strReportDate VARCHAR(10)
SET @strReportDate = CAST(DATENAME(MONTH,GETDATE()) AS VARCHAR(10))
SELECT [InstNumber]
      ,[GLLineNumber]
      ,[GLLineDescription]
      ,[NovemberBalance]
      ,[DecemberBalance]
      ,[JanuaryBalance]
      ,[FebruaryBalance]
      ,[MarchBalance]
      ,[AprilBalance]
      ,[MayBalance]
      ,[JuneBalance]
      ,[JulyBalance]
      ,[AugustBalance]
      ,[SeptemberBalance]
      ,[OctoberBalance]
      ,[CurrentBalance]
      ,CASE @strReportDate 
           WHEN 'January'	THEN DecemberBalance 
           WHEN 'Febuary'	THEN JanuaryBalance
           WHEN 'March'		THEN FebruaryBalance
           WHEN 'April'		THEN MarchBalance
           WHEN 'May'		THEN AprilBalance
           WHEN 'June'		THEN MayBalance
           WHEN 'July'		THEN JuneBalance
           WHEN 'August'	THEN JulyBalance
           WHEN 'September' 	THEN AugustBalance
           WHEN 'October'	THEN SeptemberBalance
           WHEN 'November'	THEN OctoberBalance
           WHEN 'December'	THEN NovemberBalance
           ELSE  Null
       END
       AS CurrentBalance
FROM #Test

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources