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

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)

Open in new window

ee2.txt
FairfieldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
OK, this sounds interesting :)

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 ?

0
FairfieldAuthor Commented:
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.
0
Mark WillsTopic AdvisorCommented:
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 :)

0
FairfieldAuthor Commented:
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
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)

Open in new window

t3.txt
0
Mark WillsTopic AdvisorCommented:
OK, would be inclined to use the formal PIVOT function with dynamic SQL and do something like :


DECLARE @sql varchar(max)  
DECLARE @columns varchar(max)  
 
SELECT @columns = isnull(@columns+',','') + '['+[Geographic Hierarchy Country] +']'
FROM ZWPLCSMEV_AP  
WHERE [Global Event Code] = 'PA'
GROUP BY [Geographic Hierarchy Country]  
ORDER BY [Geographic Hierarchy Country]  

--print @columns

set @sql = 'Select * from
(SELECT [Material Number], [ww_planned],[Planned Date], [Geographic Hierarchy Country]
 FROM ZWPLCSMEV_AP
 WHERE [Global Event Code] = ''PA'') srce
PIVOT
(max([planned date]) for [Geographic Hierarchy Country] in ('+@columns+')) pvt'

--print @sql

EXEC(@sql)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.