May 28, 2016 at 12:51 am
MS
mIdtdIdnamedueDate
11forecastedDate1/1/2015
21hypercareDate11/30/2016
31LOE 1 7/4/2016
41LOE 27/4/2016
51demo for yy test10/15/2016
61Implementation – testing7/4/2016
71Phased Rollout – final7/4/2016
82forecastedDate1/7/2016
92hypercareDate11/12/2016
102domain - ForteNULL
112Fortis completion1/1/2016
122Certification NULL
132Implementation 7/4/2016
MSRevised
mIdrevisedDate
11/5/2015
11/8/2015
33/25/2017
22/1/2016
212/30/2016
34/28/2016
44/28/2016
510/1/2016
67/28/2016
77/28/2016
84/28/2016
98/4/2016
95/28/2016
1110/4/2016
1110/5/2016
1311/1/2016
Output
1. Will be passing the 'tId' number, for instance 1, lets call it tid (1)
2. Want to compare tId (1)'s all milestones (except hypercareDate) with tid(1)'s forecastedDate milestone
3. return if any of the milestone date (other than hypercareDate) is greater than the forecastedDate
The above 3 steps are simple, but I have to first compare the milestones date with its corresponding revised
dates, if any, from the revised table, and pick the max date among all that needs to be compared with the forecastedDate
In this case,
for tId (1), it would be ==> 3/25/2017
for tId (2), it would be ==> 11/1/2017
May 28, 2016 at 9:02 am
Please help us help you by providing create table statements and insert statements to set up your sample data. Then we can write a query based of those things and be certain it does what is required.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 28, 2016 at 3:30 pm
@kevin, Thanks for your response. I managed to solve this, given below sample code.
INSERT INTO @mstab
SELECT [mId]
, [tId]
, [msDate]
FROM [dbo].[MS]
WHERE ([msName] NOT LIKE 'forecastedDate' AND [msName] NOT LIKE 'hypercareDate'))
SELECT @maxForecastedDate = [dbo].[fnGetMaxDate] ( 'forecastedDate');
SET @maxmilestoneDate = (SELECT MAX(maxDate)
FROM ( SELECT ms.msDueDate AS dueDate
, mr.msRevisedDate AS revDate
FROM @mstab as ms
LEFT JOIN [MSRev] as mr on ms.msId = mr.msId
) maxDate
UNPIVOT (maxDate FOR DateCols IN (dueDate, revDate))up );
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply