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;