October 9, 2014 at 3:40 am
Hi clever people
The following code just look/seem silly to me, thus the posting.
Does anyone have an easier/better way to display this? I plan to display each (date) column with monthly data.
Btw, our dates are displayed as integers like: 20140909.
This is also predictive data, that's why the months are going forward and not backward.
DECLARE @CurrentDate INT,
@MonthOne INT,
@MonthTwo INT,
@MonthThree INT,
@MonthFour INT,
@MonthFive INT,
@MonthSix INT,
@MonthSeven INT,
@MonthEight INT,
@MonthNine INT,
@MonthTen INT,
@MonthEleven INT,
@MonthTwelve INT
SET @CurrentDate = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)
SET @MonthOne = CAST(CONVERT( char(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0), 112) AS INT)
SET @MonthTwo = CAST(CONVERT( char(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()+30), 0), 112) AS INT)
SET @MonthThree = CAST(CONVERT( char(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()+60), 0), 112) AS INT)
SET @MonthFour = CAST(CONVERT( char(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()+90), 0), 112) AS INT)
SET @MonthFive = CAST(CONVERT( char(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()+120), 0), 112) AS INT)
SET @MonthSix = CAST(CONVERT( char(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()+150), 0), 112) AS INT)
SET @MonthSeven = CAST(CONVERT( char(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()+180), 0), 112) AS INT)
SET @MonthEight = CAST(CONVERT( char(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()+210), 0), 112) AS INT)
SET @MonthNine = CAST(CONVERT( char(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()+240), 0), 112) AS INT)
SET @MonthTen = CAST(CONVERT( char(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()+270), 0), 112) AS INT)
SET @MonthEleven = CAST(CONVERT( char(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()+300), 0), 112) AS INT)
SET @MonthTwelve = CAST(CONVERT( char(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()+330), 0), 112) AS INT)
SELECT @CurrentDate AS [Current Date]
,@MonthOne AS [Month One]
,@MonthTwo AS [Month Two]
,@MonthThree AS [Month Three]
,@MonthFour AS [Month Four]
,@MonthFive AS [Month Five]
,@MonthSix AS [Month Six]
,@MonthSeven AS [Month Seven]
,@MonthEight AS [Month Eight]
,@MonthNine AS [Month Nine]
,@MonthTen AS [Month Ten]
,@MonthEleven AS [Month Eleven]
,@MonthTwelve AS [Month Twelve]
October 9, 2014 at 4:12 am
This query returns the same result, but in a set-based manner:
WITH CTE_Tally AS
(
SELECT TOP 13 rid = ROW_NUMBER() OVER (ORDER BY object_id) - 1
FROM sys.columns
)
SELECT FinalDate = IIF(rid = 0,YEAR(CalcDate)*10000 + MONTH(CalcDate)*100 + DAY(CalcDate),YEAR(CalcDate)*10000 + MONTH(CalcDate)*100 + 1)
FROM
(
SELECT CalcDate = DATEADD(MONTH,rid,GETDATE()), rid
FROM CTE_Tally
) tmp
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 9, 2014 at 4:30 am
Hi Koen
Thanks for your reply. What version of SQL Server did you run this on? It might just be me being silly, but I can't run the code in SQL 2008. I have a feeling that I should not run this in Management Studio, or is it still Monday with me, lol?
October 9, 2014 at 4:44 am
frdrckmitchell7 (10/9/2014)
Hi KoenThanks for your reply. What version of SQL Server did you run this on? It might just be me being silly, but I can't run the code in SQL 2008. I have a feeling that I should not run this in Management Studio, or is it still Monday with me, lol?
The IIF statement is only available since SQL Server 2012.
It is a shorthand for writing CASE statements, so you can just replace it with the equivalent CASE. Once you've done this, you can run the code on any version since SQL Server 2005.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply