x

How to increase column name or value in SQL

I have below columns in my table EtatMonthRolling

 select 
 [ID]
 [Month_201801], [Month_201802], [Month_201803], [Month_201804], [Month_201805], [Month_201806],
 [Month_201807], [Month_201808], [Month_201809], [Month_201810], [Month_201811], [Month_201812],
 [Month_201901], [Month_201902], [Month_201903], [Month_201904], [Month_201905], [Month_201906],
 [Month_201907], [Month_201908], [Month_201909], [Month_201910], [Month_201911], [Month_201912]
  from MonthRolling

And i want to select 12 month rolling for forecast eg-> if we are in 2018/02 then i want to select 2018/03 .......till 2019/02 if we are in 2018/03 then i want to select 2018/04....... till 2019/03

I achieved this by unpivot the column (to convert columns to rows) then performed the monthRolling function then again pivot the column.

But as my column name is dynamic i am facing issue to store this into table. So i want to store column as [Forecast Month + 1], [Forecast Month + 2], [Forecast Month + 3] ....... [Forecast Month + 12]

Note- I tried using dynamic sql, but i personally feel is not good option. So could you please suggest how can i achieve this.

Create Table:

 CREATE TABLE [dbo].[EtatMonthRolling](
     [ID] [varchar](100) NULL, [Month_201701] [int] NULL, [Month_201702] [int] NULL,
     [Month_201703] [int] NULL, [Month_201704] [int] NULL, [Month_201705] [int] NULL,
     [Month_201706] [int] NULL, [Month_201707] [int] NULL, [Month_201708] [int] NULL,
     [Month_201709] [int] NULL, [Month_201710] [int] NULL, [Month_201711] [int] NULL,
     [Month_201712] [int] NULL, [Month_201801] [int] NULL, [Month_201802] [int] NULL,
     [Month_201803] [int] NULL, [Month_201804] [int] NULL, [Month_201805] [int] NULL,
     [Month_201806] [int] NULL, [Month_201807] [int] NULL, [Month_201808] [int] NULL,
     [Month_201809] [int] NULL, [Month_201810] [int] NULL, [Month_201811] [int] NULL,
     [Month_201812] [int] NULL, [Month_201901] [int] NULL, [Month_201902] [int] NULL,
     [Month_201903] [int] NULL, [Month_201904] [int] NULL, [Month_201905] [int] NULL,
     [Month_201906] [int] NULL, [Month_201907] [int] NULL, [Month_201908] [int] NULL,
     [Month_201909] [int] NULL, [Month_201910] [int] NULL, [Month_201911] [int] NULL,
     [Month_201912] [int] NULL
 )

Code:

 SELECT [ID],[MonthName] /*, [MonthForecast] AS [Inventory]*/ FROM (
 SELECT *,
 CONVERT(DATE,(LEFT(RIGHT([MonthName],6),4) + '-' + RIGHT(RIGHT([MonthName],6),2) + '-' + '01' ))
 AS MonthDate -- Converting MonthName to Date
 FROM (
 SELECT [ID], [MonthName], [MonthForecast] FROM (
 SELECT * FROM (
 SELECT
 [ID], [month_201701], [month_201702], [month_201703], [month_201704], [month_201705], [month_201706], 
 [month_201707], [month_201708], [month_201709], 
 [month_201710], [month_201711], [month_201712], [month_201801], [month_201802], [month_201803], 
 [month_201804], [month_201805], [month_201806], [month_201807], 
 [month_201808], [month_201809], [month_201810], [month_201811], [month_201812], [month_201901], 
 [month_201902], [month_201903], [month_201904], [month_201905], 
 [month_201906], [month_201907], [month_201908], [month_201909], [month_201910], [month_201911], 
 [month_201912], /*,
 DENSE_RANK() OVER (ORDER BY CONVERT(DATE,SyncDate) desc) AS LatestRecord */ FROM EtatMonthRolling )A 
 /*WHERE LatestRecord=1*/)B
 
 UNPIVOT
 
 ( [MonthForecast] FOR [MonthName] IN ( 
 [month_201701], [month_201702], [month_201703], [month_201704], [month_201705], [month_201706],
     [month_201707], [month_201708], [month_201709], [month_201710],
     [month_201711], [month_201712], [month_201801], [month_201802], [month_201803], [month_201804],
     [month_201805], [month_201806], [month_201807], [month_201808],
     [month_201809], [month_201810], [month_201811], [month_201812], [month_201901], [month_201902],
     [month_201903], [month_201904], [month_201905], [month_201906],
     [month_201907], [month_201908], [month_201909], [month_201910], [month_201911], [month_201912]
      )) AS UnPvot)A)B 
      WHERE MonthDate < DATEADD(MONTH,12,GETDATE()) and MonthDate > DATEADD(MONTH,0,GETDATE())



more ▼

asked Feb 13 at 07:24 AM in Default

avatar image

GayatriPanigrahi
1 1

@GayatriPanigrahi The problem lies in the design of the table. Instead of storing month values in a single column, data is some how being stored in multiple columns? What kind of data is stored in these columns? Please post some dummy data and the required output, may be it could clear the requirement a bit more.

5 days ago Usman Butt

@GayatriPanigrahi Why do you need such a strange design (with months as columns)? Also, what are you going to do with the columns which are toward the end of the table? For example, column named month_201911 has only one column into the future month relative to it, so there is no 12 months rolling forecast for this column, the whole forecast is nothing but the value in the month_201912 column . The next column, namely month_201912 does not have any forecast values so it's own forecast value is guaranteed to be NULL.

What I am trying to say is that it is actually possible to design the table properly and then come up with the query to display pivoted results.

5 days ago Oleg

Hi Oleg,

Appreciate your proactive response for every query. I know this is very horrible database structure, but this is totally on client demand. And every year it require hard code changes where new 12Month will be added (eg-> till i have 201912 data then next year client will send data 202001......202012) and it keeps on increasing every year.

I know this is very difficult to maintain as well, but have to do.

But i got stuck in dynamic name (earlier explanation) change.

4 days ago GayatriPanigrahi

@GayatriPanigrahi Too bad that my answers to your questions do not appear to help even though I believe that they provided solutions precisely as requested. I am referring to Check value exist in previous month in sql and Calculate value using previous and current month. In fact, you don't appear to comment and/or upvote and/or accept any of the answers. ID repetition with respect to month in SQL answered by @Kev Riley ♦♦ shows the same lack of any response (a very good answer by the way). In a mean time, responses might help the new site users who have similar questions to find answers which someone else already marked as helpful so they don't have to repeat history.

4 days ago Oleg
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1183
x1095
x2

asked: 5 days ago

Seen: 52 times

Last Updated: 4 days ago

Copyright 2018 Redgate Software. Privacy Policy