Link to home
Start Free TrialLog in
Avatar of Fairfield
FairfieldFlag for United States of America

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

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
Avatar of Mark Wills
Mark Wills
Flag of Australia image

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 ?

Avatar of Fairfield

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 :)

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
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial