How do I assign columns as a variable in SQL?
new for SQL and need some help. I'm trying to use a variable as a column header and iterate over the sums over a specific range that changes. Here is the code below.
DECLARE @startmonth AS INT
DECLARE @endmonth AS INT
DECLARE @earnedpremiumyear AS INT
SET @startmonth = 200501
SET @endmonth = 200512
SET @earnedpremiumyear = 2005
WHILE @startmonth <= 201301
BEGIN
SELECT SUM(earnedpremium) AS @earnedpremiumyear
FROM dbo.Database
WHERE accountingmonth BETWEEN @startmonth AND @endmonth
AND earnedendingmonth BETWEEN @startmonth AND @endmonth
SET @startmonth = @startmonth + 100
SET @endmonth = @endmonth + 100
SET @earnedpremiumyear = @earnedpremiumyear + 1
END
I want the SUM headline to be an earned premium year from 2005 to 2013. When I run this code, I get this error:
Incorrect syntax near '@earnedpremiumyear'
so I am definitely calling the variable wrong. Can anyone please help?
Also, how do I make it so that instead of creating a table for the total per year, the sums are on the same table in one column or row?
Thank!
+3
source to share
3 answers
Can you try to simplify it down to the next one?
SELECT
LEFT(CAST(AccountingMonth AS VARCHAR(4)),4) AS AccountingYear,
SUM(EarnedPremium) AS TotalEarnedPremium
FROM Database.dbo.Table
WHERE
AccountingMonth BETWEEN 200501 AND 201301 AND
EarnedEndingMonth BETWEEN 200501 AND 201301
GROUP BY LEFT(CAST(AccountingMonth AS VARCHAR(4)),4)
+4
source to share
The only way I can do this is by writing a dynamic SQL query:
DECLARE @startmonth AS INT
DECLARE @endmonth AS INT
DECLARE @earnedpremiumyear AS INT
SET @startmonth = 200501
SET @endmonth = 200512
SET @earnedpremiumyear = 2005
DECLARE @sql VARCHAR(MAX) = '';
WHILE @startmonth <= 201301
BEGIN
SELECT @sql = 'SUM(earnedpremium) AS ' + @earnedpremiumyear
+ 'FROM dbo.Database
WHERE accountingmonth BETWEEN' + @startmonth + ' AND ' + @endmonth
...
EXEC(@sql)
END
It can get pretty ugly though.
0
source to share