[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

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
0
Fairfield
Asked:
Fairfield
  • 3
  • 2
1 Solution
 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now