;WITH OpenPO  --Generate Different result set
AS
(
Select 
    r.purchpoolid			as Pool,
    r.CREATEDDATETIME       as CreatedDate,
	p.Deliverydate			as ETADate,
    p.purchid				as PurchaseOrder,
    p.linenum				as LineNumber,
    r.Orderaccount			as VendorAccount,
	p.Itemid				as ItemNumber,
	p.Name					as ItemName,
	p.QtyOrdered			as OrderQty,
	p.PurchReceivedNow		as RecvdQty,
	p.RemainPurchPhysical	as OpenQty,
	p.PurchPrice			as POUnitCost,
	p.LineAmount			as ExtPoCost,
    r.vendorref				as VendorReference,
	r.Inventlocationid		as Location,
	Case When r.vendorref like '%Jan-Feb SB%' Then 'JAN SB'	
	     When r.vendorref like '%JAN SB%' Then 'JAN SB'		
         When r.vendorref like '%Jan PO%' Then 'JAN PO'		
	     When r.vendorref like '%Feb FC%' Then 'FEB FC'		
	     When r.vendorref like '%Feb SB%'Then 'FEB SB'		
	     When r.vendorref like '%Mar SB%' or r.vendorref like '%Mar-Apr SB%' Then 'MAR SB'	
	     When r.vendorref like '%Mar BP%' Then 'MAR BP'		     	     
	     When r.vendorref like '%March FC%' Then 'MAR FC'		     	     
         When r.vendorref like '%Mar PO%' Then 'MAR PO'		
         When r.vendorref like '%Mar FC%' Then 'MAR FC'		              	     	     
	     When r.vendorref like '%APR FC%'Then 'APR FC'	
         When r.vendorref like '%APR SB%'Then 'APR SB'		     	     
         When r.vendorref like '%APR BP%'Then 'APR BP'	
         When r.vendorref like '%APR PO%'Then 'APR PO'	
         --When r.vendorref like '%APR FC%'Then 'APR FC'	                  	    
         When r.vendorref like '%April FC%'Then 'APR FC'	                  	             
	     When r.vendorref like '%May SB%' Then 'MAY SB'	
	     When r.vendorref like '%May FC%' Then 'MAY FC'		     
	     When r.vendorref like '%Jun FC%' Then 'JUN FC'		     	    	     
	     When r.vendorref like '%NOV%' Then 'NOV FC'		     
	else ' ' End As ETAMonth
From TABLE1 as p 
Left Outer Join TABLE2 as r 
	On p.Purchid = r.purchid	 
Where (r.CREATEDDATETIME <='2012-03-22'
and
p.purchstatus = 1 
and
r.purchpoolid = 'RP' 
and
r.inventlocationid in ('TRC','TCPW') 
)
),
OPENPO2 AS  --Generate Different ResultSet
(
 Select 
    --Summary by ETA Month and VendorAccount
	t.ItemNumber as SKU,
	t.VendorAccount as Vendor,
	t.ItemNumber+''+t.VendorAccount as SkuVendor,
	JANSB=SUM(case when t.ETAMonth = 'JAN SB' Then t.OpenQty else 0 End),
    JANPO=SUM(case when t.ETAMonth = 'JAN PO' Then t.OpenQty else 0 End),
    FEBFC=SUM(case when t.ETAMonth = 'FEB FC' Then t.OpenQty else 0 End),	
    FEBSB=SUM(case when t.ETAMonth = 'FEB SB' Then t.OpenQty else 0 End),
    MARFC=SUM(case when t.ETAMonth = 'MAR FC' Then t.OpenQty else 0 End),
    MARSB=SUM(case when t.ETAMonth = 'MAR SB' Then t.OpenQty else 0 End),
    MARBP=SUM(case when t.ETAMonth = 'MAR BP' Then t.OpenQty else 0 End),		    	    
    MARPO=SUM(case when t.ETAMonth = 'MAR PO' Then t.OpenQty else 0 End),		    	    
    APRFC=SUM(case when t.ETAMonth = 'APR FC' Then t.OpenQty else 0 End),		    	        
    APRSB=SUM(case when t.ETAMonth = 'APR SB' Then t.OpenQty else 0 End),		
    APRPO=SUM(case when t.ETAMonth = 'APR PO' Then t.OpenQty else 0 End),	
    MAYSB=SUM(case when t.ETAMonth = 'MAY SB' Then t.OpenQty else 0 End),	    	    	                	            
    MAYFC=SUM(case when t.ETAMonth = 'MAY FC' Then t.OpenQty else 0 End),	    	    	                	                
    JUNFC=SUM(case when t.ETAMonth = 'JUN FC' Then t.OpenQty else 0 End),	    	    	                	                    
    NOVFC=SUM(case when t.ETAMonth = 'NOV FC' Then t.OpenQty else 0 End)    	    	                	                    
From OpenPO t
Where t.ETAMonth is not null
and t.VendorAccount in ('AC8','RNP','WCS')
Group by t.ItemNumber, t.VendorAccount, t.ItemNumber+''+t.VendorAccount
),
OPENPO3 AS  
(
Select ItemNumber, SUM(OpenQTY) as OPENQTY
FROM OpenPO Where ETAMonth is not null Group by ItemNumber
)
Select * From OpenPo3;--Generate Different result set