Finding months in incremental fashion

Question

Finding months in incremental fashion

frdrckmitchell7

Ten Centuries

Points: 1351
More actions
October 8, 2014 at 3:05 am

#397476

Hi people
First of all thank you for your help yesterday; however, I would like to continue with the problem that I have:
This is what I have thus far:
SELECT CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)
SELECT YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + 1
As you all know the first gives the current date and the second sets the date to the beginning of the month.
I currently have eight decimal fields named MonthOne, MonthTwo...MonthEight, where MonthOne I want to populate with the current month's data and MonthTwo with the next month's data, and I am sure you can follow the pattern. So all of it gets populated with the data for eight months starting from the current month. This is prediction data, that's why the months are going forward and not backward.
Here is a simple sample:
MonthOne |MonthTwo |MonthThree ...
20.10 |230.12 |2010.24 ....
1000.21 |555.5 |99921
What I need help with is set up the months to find the months with the code (or something similar) above in an incremental fashion. Is there anyone that can help with this?
Kind regards
Fred

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

PamelaJackson123456 Newbie Points: 1 More actions · Answer 1

that's fathomable, It doesn't generally require any more investment to compose a sproc and pass parameters to it than it does to compose installed code. It can be more performable I guess, might it work. website

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 2

The code below will give you today's date, the first day of the current month, and the first day of the two following months. I am sure you can figure out the pattern and modify it for other months.

SELECT DATEADD(day, DATEDIFF(day, '20000101', CURRENT_TIMESTAMP), '20000101');

SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP), '20000101');

SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP), '20000201');

SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP), '20000301');

The report you want to make is a crosstab. When possible, try to offload this formatting to the client. Deliver the data in a relational format (with the month as one of the columns) and have the client transpose the results to the format you (or your manager) prefers.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 3

You can do something like this

SELECT *

FROM (VALUES ('My Aggregated data1',

'My Aggregated data2'))T(data1, data2)

CROSS APPLY (SELECT Datename(mm, Dateadd(mm, number, Getdate()))Months

FROM master.dbo.spt_values

WHERE type = 'P'

AND number < 8)T1

The code can be easily converted to a crosstab query using dynamic sql based on the dynamic values for next 8 months but as Hugo suggested better to do the cross tabing at the application end.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

frdrckmitchell7 (10/8/2014)
Hi people
First of all thank you for your help yesterday; however, I would like to continue with the problem that I have:
This is what I have thus far:
SELECT CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)
SELECT YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + 1
As you all know the first gives the current date and the second sets the date to the beginning of the month.
I currently have eight decimal fields named MonthOne, MonthTwo...MonthEight, where MonthOne I want to populate with the current month's data and MonthTwo with the next month's data, and I am sure you can follow the pattern. So all of it gets populated with the data for eight months starting from the current month. This is prediction data, that's why the months are going forward and not backward.
Here is a simple sample:
MonthOne |MonthTwo |MonthThree ...
20.10 |230.12 |2010.24 ....
1000.21 |555.5 |99921
What I need help with is set up the months to find the months with the code (or something similar) above in an incremental fashion. Is there anyone that can help with this?
Kind regards
Fred

Do you really want to settle for names like "MonthOne" or do you want the actual month names?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100942 More actions · Answer 5

Use a tally table -- a table of just sequential numbers -- to generate the months. You can use an in-line tally table or a stored tally table. You can get the code to create a tally table from the web. Note: Most such code uses "N" as the column name for the number; I despise single-letter names (just imagine trying to do a global change on that!), so I use "number" instead as the column name. Pick your own column name and adjust the code below accordingly:

DECLARE @total_months_to_list int

SET @total_months_to_list = 8

SELECT DATEADD(MONTH, months.number, current_month) AS month

FROM (

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS current_month

) AS starting_date

INNER JOIN dbo.tally months ON months.number BETWEEN 0 AND @total_months_to_list - 1

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

Ah... sorry... didn't realize this was a post from nearly 2 years ago and the OP hasn't logged in since 2015-11-12.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)