Question :
I’m stuck on the following problem.
I have a start range date and end range date calculated by a query and I have three columns also produced by another query, the outcome of the last query is:
PRJ_ID | START_DATE | END_DATE
I want to produce an output using a stored procedure that considering all the months between start range date and end range date produces an output like this:
PRJ_ID | MONTHS1-YEAR | MONTHS2-YEAR | ... | MONTHSN-YEAR
XXXX | YES | YES | ... | NO
The yes or no is obtained checking if the current month is in between the start_date and end_date of the project (table obtained above).
I’m able to produce all the months between two dates with this code:
DECLARE @StartDate DATETIME,
@EndDate DATETIME;
SELECT @StartDate = '20110501'
,@EndDate = '20110801';
SELECT
DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
FROM
master.dbo.spt_values x
WHERE
x.type = 'P'
AND x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);
But I’m stuck on producing the last result.
Here is my try, but I’m getting an error on @prjdates format that is not datetime:
DECLARE @StartDate DATETIME,
@EndDate DATETIME,
@minStart DATETIME,
@orig_estimated_MaxEnd DATETIME,
@maxEnd DATETIME,
@curr_planned_MaxEnd DATETIME;
SELECT @StartDate = '20110501'
,@EndDate = '20150801';
SET @minStart = (SELECT MIN(start_date)
FROM [PPM].[dbo].[prj_details]
WHERE start_date IS NOT NULL
AND start_date <> '1900-01-01') ;
SET @orig_estimated_MaxEnd = (SELECT MAX( DATEADD(day,orig_estimated_duration*7, start_date)) As FinishDate
FROM [PPM].[dbo].[prj_details]
WHERE start_date IS NOT NULL);
SET @curr_planned_MaxEnd = (SELECT MAX(curr_planned_LF)
FROM [PPM].[dbo].[status_and_updates]
WHERE curr_planned_LF IS NOT NULL);
SET @maxEnd = (SELECT
CASE
WHEN @orig_estimated_MaxEnd > @curr_planned_MaxEnd
THEN @orig_estimated_MaxEnd
ELSE @curr_planned_MaxEnd
END AS MinValue);
print @minStart;
print @orig_estimated_MaxEnd;
print @curr_planned_MaxEnd;
print @maxEnd;
DECLARE @prjDates VARCHAR(MAX);
DECLARE @prjDatesTable TABLE
(
[prjDate] DATETIME
);
DECLARE @from datetime,
@to datetime;
SET @from = '20110501';
SET @to = '20150501';
;WITH Numbers (Number) AS
(
SELECT
row_number() over (order by object_id)
FROM
sys.all_objects)
INSERT INTO @prjDatesTable
SELECT
dateadd(month, number, @from) AS months
FROM Numbers
WHERE number <= datediff(month, @from, @to);
SELECT @prjDates = COALESCE(@prjDates + '],[', '') +
(LEFT(CONVERT(VARCHAR, [prjDate], 101),6) + RIGHT(YEAR([prjDate]),2))
FROM @prjDatesTable ORDER BY [prjDate] DESC;
DECLARE @projects TABLE(PRJ_ID varchar(200), START_DATE DATEtime, END_DATE datetime)
INSERT INTO @projects
SELECT p.id,start_date,curr_planned_LF as END_DATE
from [PPM].[dbo].[prj_details]as p
left join [PPM].[dbo].[status_and_updates] as s
on p.id=s.id
where curr_planned_LF is not null
and start_date IS NOT NULL;
--select * from @projects;
select *
from
@projects
pivot
(
count(prj_id)
for start_date in ([' + @prjDates + '])
) piv;
Answer :
I would approach this by first creating a permanent dates table I can use to help create the desired output. Then I would create a pivot table using dynamic T-SQL to allow for varying column names in the output.
Setup the test-bed in TEMPDB:
USE tempdb;
IF EXISTS (SELECT 1 FROM sys.objects o WHERE o.name = 'DatesTable')
BEGIN
DROP TABLE dbo.DatesTable;
END
CREATE TABLE dbo.DatesTable
(
dDate DATETIME
CONSTRAINT PK_DatesTable
PRIMARY KEY CLUSTERED
, iDay TINYINT
, iMonth TINYINT
, iYear SMALLINT
);
INSERT INTO DatesTable (dDate, iDay, iMonth, iYear)
SELECT TOP(90000) DatesList.dDate, DATEPART(DAY, DatesList.dDate), DATEPART(MONTH, DatesList.dDate), DATEPART(YEAR, DatesList.dDate)
FROM (
SELECT dDate = DATEADD(DAY, -1, ROW_NUMBER() OVER (ORDER BY o.object_id))
FROM sys.objects o, sys.objects o1, sys.objects o2
) DatesList;
IF EXISTS (SELECT 1 FROM sys.objects o WHERE o.name = 't')
BEGIN
DROP TABLE dbo.t;
END
CREATE TABLE dbo.t
(
PRJ_ID INT NOT NULL
, [START_DATE] DATETIME NOT NULL
, END_DATE DATETIME NOT NULL
);
INSERT INTO dbo.t (PRJ_ID, START_DATE, END_DATE)
VALUES (1, '2015-08-01', '2015-08-03')
, (2, '2015-07-01', '2015-08-03')
, (3, '2015-10-01', '2015-10-03');
Here’s the good bit:
DECLARE @PivotStmt NVARCHAR(MAX);
DECLARE @PivotCols NVARCHAR(MAX);
DECLARE @StartDate DATETIME,
@EndDate DATETIME;
SELECT @StartDate = '20150501'
,@EndDate = '20151101';
SET @PivotCols = '';
/* Here we are dynamically creating the month-columns for the pivot */
SELECT @PivotCols = @PivotCols + CASE WHEN @PivotCols = '' THEN '' ELSE ', ' END + '[' + STUFF(CONVERT(VARCHAR(30), CONVERT(DATETIME, CONVERT(VARCHAR(4), dt.iYear) + '-' + CONVERT(VARCHAR(2), dt.iMonth) + '-01 00:00:00'), 106),1,3,'') + ']'
FROM dbo.DatesTable dt
WHERE dt.dDate >= (SELECT MIN(t.START_DATE) FROM dbo.t)
AND dt.dDate <= (SELECT MAX(t.START_DATE) FROM dbo.t)
GROUP BY dt.iMonth
, dt.iYear
ORDER BY dt.iYear
, dt.iMonth;
SET @PivotStmt = 'SELECT PRJ_ID
, ' + @PivotCols + '
FROM
(
SELECT t.PRJ_ID
, MonthYear = STUFF(CONVERT(VARCHAR(30), CONVERT(DATETIME, CONVERT(VARCHAR(4), dt.iYear) + ''-'' + CONVERT(VARCHAR(2), dt.iMonth) + ''-01 00:00:00''), 106),1,3,'''')
, [Yes] = ''YES''
FROM dbo.t
INNER JOIN DatesTable dt ON t.START_DATE <= dt.dDate AND t.END_DATE >= dt.dDate
GROUP BY t.PRJ_ID
, dt.iMonth
, dt.iYear
) SourceTable
PIVOT (
MIN([Yes])
FOR MonthYear IN (' + @PivotCols + ')
) as pvt
ORDER BY PRJ_ID;';
/* print out the generated pivot statement for debugging */
PRINT (@PivotStmt);
/* execute the dynamically created pivot statement */
EXEC (@PivotStmt);
My example code above returns the following result set:
Notice, Sept 2015 is included in the output even though none of the data in dbo.t contains projects in September 2015.