Fairfield
asked on
Using dynamic pivot results for additional case query
I am using a dynamic pivot query to get a resultng data set and would like to compare the data set with another table using a case statement. The case statement would be used to check to see if the date from the dynamic pivot results are after the dates in a second table. I would like to case to put the same headers as the dynamic sql with a Y or N depending on if the date from the second table is after the date from the dynamic pivot result set. How would I perform this query?
Secondary table example data
[Material Number] GA_Date PA_Date ES_Date
110936-B21 1/2/2006 2/1/2006 5/6/2007
110445-B21 12/1/2004 1/1/2005
Secondary table example data
[Material Number] GA_Date PA_Date ES_Date
110936-B21 1/2/2006 2/1/2006 5/6/2007
110445-B21 12/1/2004 1/1/2005
DECLARE @sql varchar(max)
SET @sql = 'SELECT t1.[Material Number]'
SELECT @sql = @sql + ', (SELECT MAX(t2.[Planned Date])
FROM ZWPLCSMEV_AP t2
WHERE t2.[Material Number] = t1.[Material Number] AND
t2.[Geographic Hierarchy Country] = ''' + [Geographic Hierarchy Country] + ''' AND
t2.[Global Event Code] = ''PA'') AS [' + [Geographic Hierarchy Country] + ']'
FROM ZWPLCSMEV_AP
GROUP BY [Geographic Hierarchy Country]
ORDER BY [Geographic Hierarchy Country]
SET @sql = @sql + ' FROM ZWPLCSMEV_AP t1 GROUP BY t1.[Material Number] ORDER BY [Material Number]'
EXEC(@sql)
ee2.txt
ASKER
Yes, if possible in the cell itself. Also, I am using the pivot function but I need to do an additional check to see if the date pivot result is after the date from the first table.
OK, so the query above is setting up some raw data... but in a pivotted style. That will be difficult to add in to that sql because you are casting the date column as a different name (each time).
I am guessing that you want to compare against the PA_Date becuase of the "where" clause looking for event "PA"
Now the problem is if you string together the date and the "Y" or "N" then it is no longer a date - but we could format as yyyymmdd x where x is either Y or N and you can still convert the yyyymmdd as a date where ever it is going to end up, or, we have two columns for each "cell" one being for date and the other being for the over/under indicator.
But, rest assured it can be done given the above :)
So, if you could consider the above and get back to me, we can do the SQL from there :)
I am guessing that you want to compare against the PA_Date becuase of the "where" clause looking for event "PA"
Now the problem is if you string together the date and the "Y" or "N" then it is no longer a date - but we could format as yyyymmdd x where x is either Y or N and you can still convert the yyyymmdd as a date where ever it is going to end up, or, we have two columns for each "cell" one being for date and the other being for the over/under indicator.
But, rest assured it can be done given the above :)
So, if you could consider the above and get back to me, we can do the SQL from there :)
ASKER
Mark,
Rather than doing the check on the back end, I will do the check on the front side. However I need help with the code to re-do the dynamic pivot. Can you help me modify the attached code so I will have the following output?
[Material Number] WW_Planned AU BR CO, etc......
123456 12/1/2006 1/1/2004 1/2/2004 NULL
Rather than doing the check on the back end, I will do the check on the front side. However I need help with the code to re-do the dynamic pivot. Can you help me modify the attached code so I will have the following output?
[Material Number] WW_Planned AU BR CO, etc......
123456 12/1/2006 1/1/2004 1/2/2004 NULL
DECLARE @sql varchar(max)
SET @sql = 'SELECT t1.[Material Number]'
SELECT @sql = @sql + ', (SELECT MAX(t2.[Planned Date])
FROM ZWPLCSMEV_AP t2
WHERE t2.[Material Number] = t1.[Material Number] AND
t2.[Geographic Hierarchy Country] = ''' + [Geographic Hierarchy Country] + ''' AND
t2.[Global Event Code] = ''PA'') AS [' + [Geographic Hierarchy Country] + ']'
FROM ZWPLCSMEV_AP
GROUP BY [Geographic Hierarchy Country]
ORDER BY [Geographic Hierarchy Country]
SET @sql = @sql + ' FROM ZWPLCSMEV_AP t1 GROUP BY t1.[Material Number] ORDER BY [Material Number]'
EXEC(@sql)
t3.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Where do you want the Y/N to appear ? Would need to be in the "cell" itself - the column headers must remain globally applicable for every material code right ?
Any reason why you are not using the PIVOT function ?