concatenated string and variable as column name

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

General SQL Server Forums

New to SQL Server Programming

concatenated string and variable as column name

Author

Topic

MarkIbanez2
Starting Member

3 Posts

Posted - 2011-03-31 : 10:14:43

My master table (tblglmaster) has 12 monthly columns which I need to update based on the value of month(gltransactiondate). Gltransactiondate is a column in the transaction table tblgltransactions. I had almost duplicated the same routine 12 times, changing only the month references. It works but is inefficient.

Below code updates only the month02 column:

Declare @StartDate datetime
Declare @EndDate datetime
Declare @PostedBy nvarchar(5)
Set @StartDate = 'February 1, 2011'
Set @EndDate = 'February 28, 2011'
Set @PostedBy = '00012'

Update tblGLMaster
Set Month02 = Month02 + (Select Sum (d.Amount)
From tblGLTransactions As d
Where c.AccountID = d.AccountID And
c.BusinessUnitCode = d.BusinessUnitCode And
c.BranchCode = d.BranchCode And
d.GLTransactionDate >= @StartDate And
d.GLTransactionDate <= @EndDate And
d.PostingDate = '' And d.PostedBy = '' And
d.ProcessingYear = '' And d.ProcessingMonth = '' And
Month (d.GLTransactionDate) = 2
Group By d.AccountID, d.BusinessUnitCode, d.BranchCode)
From tblGLMaster As c
Join tblGLTransactions As d
On c.AccountID = d.AccountID And
c.BusinessUnitCode = d.BusinessUnitCode And
c.BranchCode = d.BranchCode And
d.GLTransactionDate >= @StartDate And
d.GLTransactionDate <= @EndDate

Is there a way where instead of referring to column month02, I can use a concatenation of "month" and the result of month(gltransactiondate)?

Answers and suggestions will be much appreciated.
Thanks.
Mark

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-03-31 : 10:39:16

This is entirely untested so there might be a lot of tweaking that needs done, but dynamic SQL might be the trick to this.

Declare @StartDate datetime
Declare @EndDate datetime
Declare @PostedBy nvarchar(5)

Declare @SQL varchar(1000)
Declare @txtMonthPart varchar(2)
Declare @intMonthPart int

Set @SQL = ''

Set @StartDate = 'February 1, 2011'
Set @EndDate = 'February 28, 2011'
Set @PostedBy = '00012'

Set @txtMonthPart = DATEPART(M, @StartDate)
Set @intMonthPart = DATEPART(M, @StartDate)

IF (LEN(@txtMonthPart) = 1)
BEGIN
SET @txtMonthPart = '0' + @txtMonthPart
END

Set @SQL = @SQL + '
Update tblGLMaster
Set Month' + @txtMonthPart + ' = Month' + @txtMonthPart + ' + (Select Sum (d.Amount)
From tblGLTransactions As d
Where c.AccountID = d.AccountID And
c.BusinessUnitCode = d.BusinessUnitCode And
c.BranchCode = d.BranchCode And
d.GLTransactionDate >= @StartDate And
d.GLTransactionDate <= @EndDate And
d.PostingDate = '' And d.PostedBy = '' And
d.ProcessingYear = '' And d.ProcessingMonth = '' And
Month (d.GLTransactionDate) = ' + @intMonthPart + '
Group By d.AccountID, d.BusinessUnitCode, d.BranchCode)
From tblGLMaster As c
Join tblGLTransactions As d
On c.AccountID = d.AccountID And
c.BusinessUnitCode = d.BusinessUnitCode And
c.BranchCode = d.BranchCode And
d.GLTransactionDate >= @StartDate And
d.GLTransactionDate <= @EndDate'

exec @SQL

Give that a whirl and see if it comes close to what you need.

Hey, it compiles.

MarkIbanez2
Starting Member

3 Posts

Posted - 2011-04-03 : 07:35:57

Thanks a lot ajthepoolman.
I shall try this now.
Cheers.

MarkIbanez2
Starting Member

3 Posts

Posted - 2011-04-06 : 11:20:04

Thanks a lot. After some tinkering, it works.

I however have a new problem now.
How do I force the enddate to equal the max value, meaning March 31 (or the passed date from vb's date picker, becomes March 31 + 23 hours + 59 minutes + 59 secondsetc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources