SQL store value of select statement in variable

Question

1.00/5 (1 vote)

See more:

have the below code which prints out the previous 12 months however it populates in one column in a tabe. How do I get these 12 months to be populated within a variable? Should I try using a while loop?

SQL

declare @date date = '03 December 2015'
;WITH T AS(
SELECT 
    DATEADD(month, -1, @date) AS Start,
    12 Cnt
    UNION ALL
SELECT 
     DATEADD(month, -1, Start),
     Cnt-1
FROM T
WHERE Cnt-1>=0
)

SELECT     case when  LEN(MONTH(Start)) = 1 then  substring( cast(YEAR(start) as varchar(6))  + right('0',1)+ cast(MONTH(Start) as varchar(6)),1,10)
else   substring( cast(YEAR(start) as varchar(6))  + cast(MONTH(Start) as varchar(6)),1,10) end [YearMonth]
FROM    t

Posted 3-Dec-15 0:32am

Miss R

Updated 3-Dec-15 0:56am

Tomas Takac

v2

Add a Solution

Comments

John C Rayan 3-Dec-15 7:22am

You are trying to populate these values into a table in a column right? Why do you need variable ? Just update/insert the table with the 12 values you got. What's the issue here?

Miss R 3-Dec-15 7:23am

Because I want to pass these values within a tablename so I can get like 'xx_201504' and 'xx_201505' etc

ZurdoDev 3-Dec-15 7:42am

You can't put them all into a single variable. What exactly are you trying to do?

Miss R 3-Dec-15 7:48am

So I want the last 12 months on date input. I have the following but it does not calculate according to calendar yeas:
declare
--@enddate varchar(6),
@FirstTableMonth int =201412,
@Table_Name sysname,
@TableMonth int,
@end int,
@CurrentMonth int = 0,
@NextYearMonth int = 1,
@count int,
@enddate INT= 201601;

WHILE @CurrentMonth < 11
BEGIN

SELECT @TableMonth = CASE WHEN (@FirstTableMonth + @CurrentMonth) % 100 < 13 THEN
@FirstTableMonth + @CurrentMonth
ELSE
@FirstTableMonth + 100 - (@FirstTableMonth % 100) + @NextYearMonth
END,
@NextYearMonth = CASE WHEN (@FirstTableMonth + @CurrentMonth) % 100 < 13 THEN
@NextYearMonth
ELSE
@NextYearMonth - 1
END,

@Table_Name = 'xx_'+CAST(@TableMonth as nvarchar)+''

SET @CurrentMonth = CASE WHEN @TableMonth < @enddate THEN @CurrentMonth + 1 END

--PRINT @CurrentMonth;
PRINT @TableMonth;

END

Miss R 3-Dec-15 7:45am

Heres the link: http://www.codeproject.com/Questions/1061494/SQL-Select-previous-months-based-on-date

deepankarbhatnagar 3-Dec-15 7:46am

Please explain more clearly...

John C Rayan 3-Dec-15 7:56am

You are not using the SQL terms correctly to explain clearly.. I am lost ...sorry

Miss R 3-Dec-15 8:01am

What I am trying to achieve is to get a list of the last 12 months. If i type 201402 then it should show me

201303
201304
201305
201306
201307
201308
201309
201310
201311
201312
201401
201402

The commented code above does not do this correctly as it brings up dates such as 201400 and 201399 which is incorrect.

Any idea how to do this?

John C Rayan 3-Dec-15 10:30am

Hi
I have given the SQL in solution. You don't need any loop.

John C Rayan 3-Dec-15 10:49am

Let me know after you tested it.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2015-12-03T01:48:00

Solution 1

I have made changes in the solution. Instead of providing input as integer I suggest to provide input in date like 01-Feb-2014' instead of 201402.

SQL

DECLARE @TBL_MONTH DATETIME, @NAME AS VARCHAR(20), @CurrentMonth INT, @TMP_MONTH DATETIME,
		@Table_Name VARCHAR(20)

SELECT @TBL_MONTH = '01-FEB-2014', @CurrentMonth = 12

WHILE @CurrentMonth > 0
BEGIN
	
	SET @CurrentMonth = @CurrentMonth - 1

	SET @TMP_MONTH = DATEADD(month, @CurrentMonth*-1,  @TBL_MONTH)

	SELECT @NAME = CONVERT(VARCHAR(6), @TMP_MONTH,  112)

	PRINT @NAME
	
	SELECT @Table_Name = 'xx_'+@NAME

	PRINT @Table_Name

END

Posted 3-Dec-15 1:48am

_Asif_

Updated 3-Dec-15 21:58pm

v2

Comments

Miss R 3-Dec-15 7:52am

No. The values should not store in a table, it should store as printed output

_Asif_ 3-Dec-15 7:56am

You can do a loop over table, store the process_date in a variable @TABLENAME and then finally PRINT that variable as you are showing in ur comment :) though i can't comprehend what actually you are trying to acheive

Miss R 3-Dec-15 8:00am

What I am trying to achieve is to get a list of the last 12 months. If i type 201402 then it should show me

201303
201304
201305
201306
201307
201308
201309
201310
201311
201312
201401
201402

The commented code above does not do this correctly as it brings up dates such as 201400 and 201399 which is incorrect.

Any idea how to do this?

_Asif_ 4-Dec-15 3:59am

Have made changes to the solution. Try the new one

Miss R 3-Dec-15 8:23am

Asif, here is a link: http://www.codeproject.com/Questions/1061569/SQL-get-last-months

John C Rayan · Answer 2 · 2015-12-03T04:30:00

It gives you what you wanted.

SQL

DECLARE @FirstTableMonth varchar(6) =  201402
DECLARE @inDate Date , @toDate Date

SET @inDate = Convert(DATE, @FirstTableMonth + '01')


SET @toDate =    DATEADD(month, -10,@inDate)

;with months (idate)
AS
(
    SELECT @inDate

    UNION ALL

    SELECT DATEADD(month,-1,isnull(idate,@inDate))
    from months
    where idate >= @toDate
)
select  CONVERT(CHAR(6), idate, 112)  from months
option (maxrecursion 0)

201402
201401
201312
201311
201310
201309
201308
201307
201306
201305
201304
201303

sandeepmittal11 · Answer 3 · 2015-12-04T01:13:00

Solution 3

SQL

declare @date date = '03 December 2015'
declare @list varchar(500)
SET @list = (SELECT   LEFT(CONVERT(VARCHAR,DATEADD(MM, -1*number, @date),112),6) + CHAR(10)
FROM    master.dbo.spt_values
WHERE TYPE = 'P' and number between 1 and 12
for xml path(''))
select @list

Posted 4-Dec-15 1:13am

sandeepmittal11