September 24, 2015 at 1:13 pm
Any way to tell where I'm getting this warning from:
Warning: Null value is eliminated by an aggregate or other SET operation.
September 24, 2015 at 1:17 pm
shorton2 (9/24/2015)
Any way to tell where I'm getting this warning from:Warning: Null value is eliminated by an aggregate or other SET operation.
That comes from all the MAX() functions. Nothing to be concerned as this is normal behavior.
September 24, 2015 at 1:24 pm
Great, thank you. What I have is working great.
Last thing to make this golden. All of my milestones have Forecast and Actual. But I have a handful where I need to include a Plan date column (i.e. MilestoneX-Plan). In the dynamic code I see how to add a Plan date for all milestones, but is there a way I could limit that column creation to only happen with a set of about 5 milestones?
September 24, 2015 at 1:31 pm
Use a CASE statement.
CREATE TABLE #Milestonetable(
JobNo INT,
MileStoneName CHAR(1),
ForecastDate DATE,
ActualDate DATE,
StatusFlag CHAR(1));
INSERT INTO #Milestonetable
VALUES (1,'A','2015/01/01',NULL,'T'),
(1,'B','2014/02/01','2015/01/01','F'),
(1,'C',NULL,NULL,'L'),
(2,'A','2015/03/01',NULL,'Q')
DECLARE @sql nvarchar(max);
SET @sql =
--This is the header. It includes columns that will group the data. It could include a CTE if needed.
N'SELECT JobNo ' + CHAR(10)
--This is the dynamic part. It includes the pivoted columns
+ (
SELECT DISTINCT
CHAR(9) + ',MAX(CASE WHEN MileStoneName = ' + QUOTENAME( MileStoneName, '''') + ' THEN ForecastDate END) AS Milestone' + MileStoneName + '_Fcst ' + CHAR(10) +
CHAR(9) + ',MAX(CASE WHEN MileStoneName = ' + QUOTENAME( MileStoneName, '''') + ' THEN ActualDate END) AS Milestone' + MileStoneName + '_Act ' + CHAR(10) +
CHAR(9) + ',MAX(CASE WHEN MileStoneName = ' + QUOTENAME( MileStoneName, '''') + ' THEN StatusFlag END) AS MileStone' + MileStoneName + '_Flag ' + CHAR(10) +
CASE WHEN MileStoneName IN ('C')
THEN CHAR(9) + ',MAX(CASE WHEN MileStoneName = ' + QUOTENAME( MileStoneName, '''') + ' THEN PlanDate END) AS MileStone' + MileStoneName + '_Plan ' + CHAR(10)
ELSE '' END
FROM #Milestonetable
--ORDER BY MileStoneName
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
--This is the footer. It includes everything after the column list (INTO, FROM, JOIN, APPLY, WHERE, GROUP BY, HAVING, ORDER BY) or even "totals" columns.
+ N'INTO SomeTable ' + CHAR(10)
+ N'FROM #Milestonetable ' + CHAR(10)
+ N'GROUP BY JobNo;';
PRINT @sql;
--EXEC sp_executesql @sql;
GO
DROP TABLE #Milestonetable;
September 24, 2015 at 3:06 pm
Sweet. I moved the plan date conditional to the beginning, and had a little trouble with the plus sign normally at the end of the THEN line but I sorted that by moving it to ouside the CASE and it appears to be working great.
-- Plan date
CASE WHEN Milestone IN ('task30','task31','task32')
THEN CHAR(9) + ',MAX(CASE WHEN Milestone = ' + QUOTENAME( Milestone, '''') + ' THEN PLAN_DATE END) AS ' + Milestone + '_P ' + CHAR(10)
ELSE '' END
+
I tried the PRINT @sql as well, but it only prints part of the SQL, presumably it has a limit. Do you know off-hand if there is a place to configure that in SSMS (or elsewhere) to show more/all?
Thanks,
Scott
September 25, 2015 at 8:23 am
With this great help I got brave and added a needed LEFT OUTER JOIN. I need to pull in a field from another table. Had some trouble, got it to work this way. Is this the right way to do it? Specifically, the only way I was able to get it to work and add the "Status" field to my select was to also add it to the group by clause. Didn't know if this was the correct way or not. Being new, I'm doing some educated guessing. Grateful if you can let me know if I got it wrong. For (my) clarity I tried it on the static code first.
DROP TABLE Milestonetable;
DROP TABLE SecondaryTable;
CREATE TABLE Milestonetable(
JobNo INT,
MileStoneName CHAR(1),
ForecastDate DATE,
ActualDate DATE,
StatusFlag CHAR(1));
INSERT INTO Milestonetable
VALUES (1,'A','2015/01/01',NULL,'T'),
(1,'B','2014/02/01','2015/01/01','F'),
(1,'C',NULL,NULL,'L'),
(2,'A','2015/03/01',NULL,'Q');
CREATE TABLE SecondaryTable (
JobNo2 INT,
Status varchar(10)
);
INSERT INTO SecondaryTable
VALUES ( 1, 'Active'),( 2, 'Closed');
SELECT
JobNo, status,
MAX(CASE WHEN MileStoneName = 'A' THEN ForecastDate END) AS MilestoneA_Fcst,
MAX(CASE WHEN MileStoneName = 'A' THEN ActualDate END) AS MilestoneA_Act,
MAX(CASE WHEN MileStoneName = 'B' THEN ForecastDate END) AS MilestoneB_Fcst,
MAX(CASE WHEN MileStoneName = 'B' THEN ActualDate END) AS MilestoneB_Act,
MAX(CASE WHEN MileStoneName = 'C' THEN ForecastDate END) AS MilestoneC_Fcst,
MAX(CASE WHEN MileStoneName = 'C' THEN ActualDate END) AS MilestoneC_Act
FROM Milestonetable
LEFT OUTER JOIN
secondarytable ON Milestonetable.JobNo = secondarytable.JobNo2
GROUP BY JobNo, Status;
September 25, 2015 at 8:33 am
It seems right to me. Just as a suggestion, whenever you're using more than one table, qualify your columns and use an alias for your tables. This won't change the way the query is processed, but it will help you when writing and debugging code.
SELECT
m.JobNo, s.status,
MAX(CASE WHEN m.MileStoneName = 'A' THEN m.ForecastDate END) AS MilestoneA_Fcst,
MAX(CASE WHEN m.MileStoneName = 'A' THEN m.ActualDate END) AS MilestoneA_Act,
MAX(CASE WHEN m.MileStoneName = 'B' THEN m.ForecastDate END) AS MilestoneB_Fcst,
MAX(CASE WHEN m.MileStoneName = 'B' THEN m.ActualDate END) AS MilestoneB_Act,
MAX(CASE WHEN m.MileStoneName = 'C' THEN m.ForecastDate END) AS MilestoneC_Fcst,
MAX(CASE WHEN m.MileStoneName = 'C' THEN m.ActualDate END) AS MilestoneC_Act
FROM Milestonetable m
LEFT OUTER JOIN
secondarytable s ON m.JobNo = s.JobNo2
GROUP BY m.JobNo, s.status;
September 25, 2015 at 8:45 am
Thanks Luis. Appreciate that tip. Will do.
I also found I could get the same result doing this. Not sure which is best, could cause an anomaly, or if it makes a difference
I moved the additional column into the middle (Aggregated?):
SELECT
JobNo,
MAX(CASE WHEN MileStoneName = 'A' THEN ForecastDate END) AS MilestoneA_Fcst,
MAX(CASE WHEN MileStoneName = 'A' THEN ActualDate END) AS MilestoneA_Act,
MAX(CASE WHEN MileStoneName = 'B' THEN ForecastDate END) AS MilestoneB_Fcst,
MAX(CASE WHEN MileStoneName = 'B' THEN ActualDate END) AS MilestoneB_Act,
MAX(CASE WHEN MileStoneName = 'C' THEN ForecastDate END) AS MilestoneC_Fcst,
MAX(CASE WHEN MileStoneName = 'C' THEN ActualDate END) AS MilestoneC_Act,
MAX(status) as status
FROM Milestonetable
LEFT OUTER JOIN
secondarytable ON Milestonetable.JobNo = secondarytable.JobNo2
GROUP BY JobNo;
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply