Column Heading getdate() - SQL Server Forums

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

Column Heading getdate()

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2015-01-07 : 10:42:00

I would like to make the column heading to be the current year for the Sales I'm adding below.


SELECT dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no,dbo.arslmfil_SQL.slspsn_name, 
SUM(CASE WHEN year(getdate()) = qivsalesmtdytdcustsalesperson.year THEN Sales END) AS convert(varchar(4),year(getdate()))
FROM            dbo.QIVSalesMTDYTDCustSalesPerson INNER JOIN
                         dbo.arslmfil_SQL ON dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no = dbo.arslmfil_SQL.humres_id
GROUP BY dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no, dbo.arslmfil_SQL.slspsn_name

What I have now gives me incorrect syntax near keyword convert

Vack
Aged Yak Warrior

530 Posts

Posted - 2015-01-07 : 11:38:50

I had to create a stored procedure to make this happen. Could not find any other way.

-- Insert statements for procedure here
declare @sql nvarchar(4000) ='

select dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no, dbo.arslmfil_SQL.slspsn_name,
ISNULL(SUM(CASE WHEN year(getdate()) = qivsalesmtdytdcustsalesperson.year THEN Sales END), 0) AS ['+ cast(cast(year(getdate()) as varchar(4)) as varchar(20))+ '],
ISNULL(SUM(CASE WHEN year(getdate())-1 = qivsalesmtdytdcustsalesperson.year THEN Sales END), 0) AS ['+ cast(cast(year(getdate())-1 as varchar(4)) as varchar(20))+ ']
FROM dbo.QIVSalesMTDYTDCustSalesPerson INNER JOIN
dbo.arslmfil_SQL ON dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no = dbo.arslmfil_SQL.humres_id
GROUP BY dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no, dbo.arslmfil_SQL.slspsn_name'

exec sp_executesql @sql
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-01-08 : 01:57:51

Yes it cannot be done without Dynamic SQL.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources