How to create a month name as a column for a date range for previous month to next 5 months dynamically in sql server

Transact-SQL
1

How to create a month name as a column for a date range for previous month to next 5 months dynamically in sql server.

I want retrive 6 months data (previous month to next 5 months i.e August 2018 to Jan 2019) based on current month. suppose if I run same query next octomber month that time 6 months data should be sept 2018 to feb 2019 and months names should be consider as column names dynamicaly.

table :

CREATE TABLE [dbo].[empproj](
    [projectname] [varchar](50) NULL,
    [empname] [varchar](50) NULL,
    [startdate] [date] NULL,
    [enddate] [date] NULL,
    [projectstatus] [numeric](18, 2) NULL
) 
GO
INSERT [dbo].[empproj] ([projectname], [empname], [startdate], [enddate], [projectstatus]) VALUES (N'p1', N'e1', CAST(N'2018-04-01' AS Date), CAST(N'2018-12-31' AS Date), CAST(1.00 AS Numeric(18, 2)))
go
INSERT [dbo].[empproj] ([projectname], [empname], [startdate], [enddate], [projectstatus]) VALUES (N'p1', N'e5', CAST(N'2014-02-01' AS Date), CAST(N'2019-01-31' AS Date), CAST(0.25 AS Numeric(18, 2)))
GO
INSERT [dbo].[empproj] ([projectname], [empname], [startdate], [enddate], [projectstatus]) VALUES (N'p2', N'e1', CAST(N'2017-01-01' AS Date), CAST(N'2019-03-30' AS Date), CAST(0.75 AS Numeric(18, 2)))
GO

based on the above data I want output like below as per current month run the query.

projectname |empname | August2018| September2018|October2018|November2018| December2018|January2019 
p1      |e1      |   1.0     |  1.0         |  1.0      |  1.0       | 1.0          |0.0
p1      |e5      |   0.25    |  0.25        | 0.25      | 0.25       |0.25          |0.25
p2      |e1      |   0.75    |  0.75        | 0.75      |0.75        | 0.75         |0.75

if run same query in the next month (october) then result should come like below.

projectname |empname |  September2018|October2018|November2018| December2018|January2019 |February2019  
p1      |e1      |   1.0         |  1.0      |  1.0       | 1.0          |0.0        |0.0
p1      |e5      |   0.25        | 0.25      | 0.25       |0.25          |0.25       |0.0
p2      |e1      |   0.75        | 0.75      |0.75        | 0.75         |0.75       |0.75

I tried like below :

declare @start DATE = (select  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0))

declare @end DATE = (select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+4, 0))

;with months (date)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(month,1,date)
    from months
    where DATEADD(month,1,date)<=@end
)

select Datename(month,date)months from months

I have got stuck with logic.

How can I write a query to achieve this task month names dynamic column for 6 months data in sql server.

2

I would not recommend doing this - it can be done much easier in the tool that will be displaying the report/results.

You want to build either a cross-tab or pivot query and pivot the data into fixed column names (Month1, Month2, Month3, etc...). If you are using SSRS to display and prompt the users for start/end dates you then use the parameters in the report to display the header of that column based on the start date parameter.

If you are not using SSRS or something similar - you can add additional columns that contain the start date and end date, then in the tool for display pull those columns and calculate the column headers.

Changing column names makes it virtually impossible to bind that result set to any external process (SSRS, SSIS, Cognos, Power BI, etc...). You need fixed column names for those tools and then you have those tools dynamically define the column headers.

1 Like