September 28, 2018 at 1:33 am
Hi I have one doubt in sql server .
how to create a month name as a column for a date range for previous month to next 5 months dynamicaly 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 strucked with logice.
please tell me how to write query to achive this task month names dynamic column for 6 months data in sql server
October 2, 2018 at 8:04 am
You can use a dynamic pivot. I have a table function called Tally that I use to generate the numbers. You pass in where you want to start and end. In this case you want to start at today and get 6 months, so I pass in 0 and 5. that's below as well
CREATE TABLE #empproj(
projectname varchar(50) NULL,
empname varchar(50) NULL,
startdate date NULL,
enddate date NULL,
projectstatus numeric(18, 2) NULL
)
GO
INSERT #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 #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 #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
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((select ',' + DateName(Month, DateAdd(month, N,cast(GetDate() as date))) + cast(datePart(year, DateAdd(month, N, cast(GetDate() as date))) as char(4))
from dbo.Tally(0,5)
order by DateAdd(month, N,cast(GetDate() as date))
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'select ProjectName, empname, ' + @cols + '
from (select e.ProjectName, e.empname,
case when v.today between e.startdate and e.enddate then e.projectstatus else 0.00 end [STatus],
Header
from #empproj e
cross apply
(
select DateName(Month, DateAdd(month, N,cast(GetDate() as date))) + cast(datePart(year, DateAdd(month, N, cast(GetDate() as date))) as char(4)) Header,
DateAdd(month, N, cast(GetDate() as date)) today
from dbo.Tally(0,5)
) v
) x
pivot
(
max([status])
for Header in (' + @cols + ')
) p'
exec (@Query)
Tally Table
Create FUNCTION [dbo].[Tally]
(
@pMin BIGINT
,@pMax BIGINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH T1(F) AS
(
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1
),
T2(F) AS
(SELECT 1 FROM T1 A, T1 B),
T3(F) AS
(SELECT 1 FROM T2 A, T2 B),
T4(F) AS
(SELECT 1 FROM T3 A, T3 B),
cteTally(N) AS
(
SELECT TOP ((@pMax-(((ABS(@pMin)+@pMin)/2)))+1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
+ ((((ABS(@pMin)+@pMin)/2))-1)
FROM T4
)
SELECT
N
FROM
cteTally T
;
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply