How to create 12-months projection query in SQL

Question

Hi Team,

Have requirement as bellow, need to write query to get next 12 months projection data include current month. Could you please help me.

Please find-out bellow example input outputs.

SQL QUERY:

CREATE TABLE [dbo].[Projection](

[YearMonth] [nvarchar](50) NOT NULL,

[Account] [smallint] NOT NULL,

[Country] [nvarchar](50) NOT NULL,

[State] [nvarchar](50) NOT NULL,

[Sales] [smallint] NOT NULL

) ON [PRIMARY]

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Jan-24', 10001, N'India', N'AP', 10087)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Feb-24', 10001, N'India', N'AP', 12345)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Mar-24', 10001, N'India', N'AP', 12346)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Apr-24', 10001, N'India', N'AP', 12347)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'May-24', 10001, N'India', N'AP', 12348)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Jun-24', 10001, N'India', N'AP', 12349)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Jul-24', 10001, N'India', N'AP', 12350)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Aug-24', 10001, N'India', N'AP', 12351)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Sep-24', 10001, N'India', N'AP', 12352)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Oct-24', 10001, N'India', N'AP', 12353)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Nov-24', 10001, N'India', N'AP', 12354)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Dec-24', 10001, N'India', N'AP', 12355)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Jan-25', 10001, N'India', N'AP', 12356)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Feb-25', 10001, N'India', N'AP', 12357)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Mar-25', 10001, N'India', N'AP', 12358)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Apr-25', 10001, N'India', N'AP', 12359)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'May-25', 10001, N'India', N'AP', 12360)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Jun-25', 10001, N'India', N'AP', 12361)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Jul-25', 10001, N'India', N'AP', 12362)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Aug-25', 10001, N'India', N'AP', 12363)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Sep-25', 10001, N'India', N'AP', 12364)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Oct-25', 10001, N'India', N'AP', 12365)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Nov-25', 10001, N'India', N'AP', 12366)

GO

INSERT [dbo].[Projection] ([YearMonth], [Account], [Country], [State], [Sales]) VALUES (N'Dec-25', 10001, N'India', N'AP', 12367)

GO

INPUT:

OUTPUT:

Answer 1

Using dynamic SQL and PIVOT operator:

declare @account smallint = 10001
declare @start_date date = GETDATE()

declare @sql nvarchar(max) = ''

; with Q1 as
(
	select @start_date d, 1 n
	union all
	select dateadd(month, 1, d), n + 1
	from Q1
	where n < 12
), 
Q2 as
(
	select d, concat(left(datename(month, d), 3), '-', year(d) % 100) YearMonth
	from Q1
)
select @sql = concat(
	'select Country, State, Account, ', 
	string_agg(quotename(YearMonth), ', ') within group (order by d),
	' from ( select * from Projection where Account=@account ) t
	pivot ( sum (Sales) for YearMonth in (',
	string_agg(quotename(YearMonth), ', '),
	') ) as p'
	)
from Q2

exec sp_executesql @sql, @params=N'@account smallint', @account=@account