मैंने एक SQL सर्वर संग्रहीत प्रक्रिया लिखी है, जिसमें 2 पैरामीटर होते हैं। वर्तमान संग्रहीत प्रक्रिया नीचे के रूप में काम करती है, जब पैरामीटर मान तालिका के कॉलम में इंगित करता है, तो यह केवल तालिका से उस पैरामीटर मान के लिए विशिष्ट पंक्तियों को प्रदर्शित करेगा।

अब आवश्यकता एक नई संग्रहीत प्रक्रिया लिखने के बिना है, मुझे उसी संग्रहीत प्रक्रिया में एक शर्त लिखनी है ताकि यह इस तरह से काम करे कि जब उपयोगकर्ता कोई अन्य पैरामीटर दर्ज करता है तो 0 मान कहें जो तालिका में उपलब्ध नहीं है, यह फ़िल्टर किए बिना, आउटपुट में तालिका के सभी SAPID का डेटा दिखाना चाहिए।

मैं जिस पैरामीटर का उल्लेख कर रहा हूं वह नीचे संग्रहीत प्रक्रिया में SAPID पैरामीटर है, इसलिए जब उपयोगकर्ता SAPID के अलावा msasow तालिका GVBDUH में उपलब्ध किसी भी SAPID में प्रवेश करता है। कॉलम, इसे फ़िल्टर किए बिना सभी SAPIDs विवरण प्रदर्शित करना चाहिए; जब हम SAPID से गुजरते हैं जो GVBDUH कॉलम में उपलब्ध है, तो यह केवल उस SAPID (जो वर्तमान में काम कर रहा है) से संबंधित डेटा प्रदर्शित करेगा, इसलिए जब उपयोगकर्ता कोई अन्य SAPID जो GUBDUH कॉलम में उपलब्ध नहीं है, उसे बिना फ़िल्टर किए GVBDUH कॉलम में उपलब्ध सभी SAPIDS का डेटा प्रदर्शित करना चाहिए

यह मेरी संग्रहीत प्रक्रिया है:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetL1AgeingBucket] 
    (@sapid VARCHAR(10),         
     @RagValue VARCHAR(50))          
AS
    DECLARE @temp TABLE
                  (          
                      [Project code] VARCHAR(100),
                      [L3 Sales Name] VARCHAR(100),          
                      PO_NUMBER NVARCHAR(MAX),
                      POValidTo NVARCHAR(MAX),
                      [DU Name] VARCHAR(100),
                      [SDU Name] VARCHAR(100),
                      [VBDU Name] VARCHAR(100),         
                      [Customer Name] VARCHAR(100),
                      [PO KIF] VARCHAR(MAX)          
                  )           

insert into @temp          
select distinct t1.[Project code], t3.[L3 Sales Name], t2.PONumber, CONVERT(varchar(10), t2.[POValidTo], 126),        
t1.[DU Desc (L4)] [DU Name], t1.[SDU Name (L3)] [SDU Name], t1.[VBDU Desc (L2)] [VBDU Name],t1.[Customer Name], t1.[PO KIFID]          
from TBL_MSA_SOW t1           
inner join [tbl_PO] as t2           
on t1.[Project code]=t2.[Project Code]          
inner join [tbl_Sales_mapping] as t3          
on t1.[Project Code]=t3.[Project Code]           
where t2.[PO RAG check]=@RagValue          
and t1.[GVBDUH Code] =@sapid and t3.[AM Sales Name] !='-';          

with cte1 as          
(select distinct t1.[DU Desc (L4)] as [L4], t1.[SDU Name (L3)] as [L3], t1.[VBDU Desc (L2)] as [L2], t3.[Customer Name], t3.[AM Sales Name] as AM,           
 t1.[Project code], t3.[L3 Sales Name] as [Sales L3 Name],           
t3.[L2 sales Name] as [Sales L2 Name], Convert(nvarchar(255),ISNULL(t1.[MSA], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[MSA KIFNO], '')) as [MSA / KIFNO],     
CONVERT(varchar(10), t1.[MSA End Date], 126) as [MSA End Date], Convert(nvarchar(255),ISNULL(t1.[Customer SOW], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[SOW KIFNO], '')) as [SOW / KIFNO],    
  CONVERT(varchar(10), t1.[SOW End Date], 126) as [SOW End Date],      
round(t1.[Avg Rev based on AMJ '18 PP (in $K)],0) as [$ K  / Month Impact],  
 t1.[Other KIFID], CONVERT(varchar(10),     
 t1.[Other KIFID End Date], 126) as [Other KIFID End Date],          
t3.[Customer Group]            
from [tbl_MSA_SOW] as t1 left join [tbl_PO] as t2           
on t1.[Project code] = t2.[Project Code]          
inner join [tbl_Sales_mapping] as t3           
on t1.[Project Code]=t3.[Project Code]           
where (t1.[MSA RAG check]=@RagValue or t1.[SOW RAG check]=@RagValue          
or t2.[PO RAG check]=@RagValue)           
and t1.[GVBDUH Code] =@sapid and t3.[AM Sales Name] !='-'          
group by t3.[L3 Sales Name], t3.[AM Sales Name], t1.[DU Desc (L4)] , t1.[SDU Name (L3)], t1.[VBDU Desc (L2)], t3.[Customer Name],           
t1.[Project code], t3.[L2 sales Name],  Convert(nvarchar(255),ISNULL(t1.[MSA], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[MSA KIFNO], '')),     
CONVERT(varchar(10), t1.[MSA End Date], 126), Convert(nvarchar(255),ISNULL(t1.[Customer SOW], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[SOW KIFNO], '')),    
CONVERT(varchar(10), t1.[SOW End Date], 126),      
t1.[Avg Rev based on AMJ '18 PP (in $K)], 
t1.[Other KIFID], CONVERT(varchar(10),     
t1.[Other KIFID End Date], 126), t3.[Customer Group]),         

cte2 as          
(select distinct t1.[DU Desc (L4)] as [L4], t1.[SDU Name (L3)] as [L3], t1.[VBDU Desc (L2)] as [L2], t3.[Customer Name], t3.[AM Sales Name] as AM,           
 t1.[Project code], t3.[L3 Sales Name] as [Sales L3 Name],           
t3.[L2 sales Name] as [Sales L2 Name],Convert(nvarchar(255),ISNULL(t1.[MSA], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[MSA KIFNO], '')) as [MSA / KIFNO],
 CONVERT(varchar(10), t1.[MSA End Date], 126) as [MSA End Date],    
  '' as [SOW / KIFNo], CONVERT(varchar(10), t1.[SOW End Date],    
 126) as [SOW End Date],      
round(t1.[Avg Rev based on AMJ '18 PP (in $K)],0) as [$ K  / Month Impact], t1.[Other KIFID],    
 CONVERT(varchar(10), t1.[Other KIFID End Date], 126) as [Other KIFID End Date]             
from [tbl_MSA_SOW] as t1 left join [tbl_PO] as t2           
on t1.[Project code] = t2.[Project Code]          
inner join [tbl_Sales_mapping] as t3           
on t1.[Project Code]=t3.[Project Code]           
where t1.[MSA RAG check]=@RagValue           
and t1.[GVBDUH Code] =@sapid and t3.[AM Sales Name] !='-'          
group by t3.[L3 Sales Name], t3.[AM Sales Name], t1.[DU Desc (L4)] , t1.[SDU Name (L3)], t1.[VBDU Desc (L2)], t3.[Customer Name],          
t1.[Project code], t3.[L2 sales Name], Convert(nvarchar(255),ISNULL(t1.[MSA], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[MSA KIFNO], '')),    
CONVERT(varchar(10), t1.[MSA End Date], 126), Convert(nvarchar(255),ISNULL(t1.[Customer SOW], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[SOW KIFNO], '')), CONVERT(varchar(10), t1.[SOW End Date], 126),       
t1.[Avg Rev based on AMJ '18 PP (in $K)], t1.[Other KIFID], CONVERT(varchar(10), t1.[Other KIFID End Date], 126)),         

cte3 as          
(select distinct t1.[DU Desc (L4)] as [L4], t1.[SDU Name (L3)] as [L3], t1.[VBDU Desc (L2)] as [L2], t3.[Customer Name], t3.[AM Sales Name] as AM,           
 t1.[Project code], t3.[L3 Sales Name] as [Sales L3 Name],           
t3.[L2 sales Name] as [Sales L2 Name],  '' as [MSA / KIFNo], CONVERT(varchar(10), t1.[MSA End Date], 126) as [MSA End Date], Convert(nvarchar(255),ISNULL(t1.[Customer SOW], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[SOW KIFNO], '')) as [SOW / KIFNO],    
 CONVERT(varchar(10), t1.[SOW End Date], 126) as [SOW End Date],      
round(t1.[Avg Rev based on AMJ '18 PP (in $K)],0) as [$ K  / Month Impact], t1.[Other KIFID],    
 CONVERT(varchar(10), t1.[Other KIFID End Date], 126) as [Other KIFID End Date]            
from [tbl_MSA_SOW] as t1 left join [tbl_PO] as t2           
on t1.[Project code] = t2.[Project Code]          
inner join [tbl_Sales_mapping] as t3           
on t1.[Project Code]=t3.[Project Code]           
where t1.[SOW RAG check]=@RagValue          
and t1.[GVBDUH Code] =@sapid and t3.[AM Sales Name] !='-'          
group by t3.[L3 Sales Name], t3.[AM Sales Name], t1.[DU Desc (L4)], t1.[SDU Name (L3)], t1.[VBDU Desc (L2)], t3.[Customer Name],          
t1.[Project code], t3.[L2 sales Name], Convert(nvarchar(255),ISNULL(t1.[MSA], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[MSA KIFNO], '')),    
 CONVERT(varchar(10), t1.[MSA End Date], 126), Convert(nvarchar(255),ISNULL(t1.[Customer SOW], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[SOW KIFNO], '')), CONVERT(varchar(10), t1.[SOW End Date], 126),       
t1.[Avg Rev based on AMJ '18 PP (in $K)], t1.[Other KIFID], CONVERT(varchar(10), t1.[Other KIFID End Date], 126)),      


cte4 as      
(select distinct       
[Project code],[L3 Sales Name],      
[DU Name],[SDU Name],[VBDU Name],[Customer Name],      
 STUFF(      
        (      
        select  ',' + PO_NUMBER FROM @temp d      
        where [Project code]=t.[Project code]              
  for xml path('')      
        ), 1, 1, '') as [PO Numbers],
STUFF(      
        (      
        select  distinct ',' + [PO KIF] FROM @temp d      
        where [Project code]=t.[Project code]              
  for xml path('')      
        ), 1, 1, '') as [PO KIFID]      
 from @temp t),

 cte5 as
(select distinct 
[Project code],[L3 Sales Name],
[DU Name],[SDU Name], [VBDU Name],[Customer Name],
 STUFF(
        (
        select  ',' + POValidTo FROM @temp d
        where [Project code]=t.[Project code]        
        for xml path('')
        ), 1, 1, '') as [PO End Date]   
 from @temp t)   


 SELECT * 
 FROM ( 
 SELECT  A.[Customer Group], A.[Customer Name], A.[L4], A.[L3],UPPER(A.[L2]) L2, A.AM,
A.[Sales L3 Name],           
A.[Sales L2 Name],
A.[MSA / KIFNo],     
A.[MSA End Date], A.[SOW / KIFNo],    
 A.[SOW End Date], A.[PO Numbers],     
 CASE A.[PO Numbers] 
       WHEN NULL THEN '' 
       ELSE  A.[PO KIFID]
END as [PO KIFID] 
 , A.[PO End Date],    
 A.[Other KIFID],     
A.[Other KIFID End Date] , 
sum(A.[$ K  / Month Impact]) AS [$ K  / Month Impact]
  FROM (

select distinct cte1.[Customer Group], cte1.[Customer Name], cte1.[L4], cte1.[L3], cte1.[L2], cte1.AM,           
--cte1.Project, cte1.[Project code],         
cte1.[Sales L3 Name],           
cte1.[Sales L2 Name],        
-- cte2.[MSA No], cte3.[SOW No],         
 cte2.[MSA / KIFNo],     
cte2.[MSA End Date], cte3.[SOW / KIFNo],    
 cte3.[SOW End Date], cte4.[PO Numbers], cte4.[PO KIFID], cte5.[PO End Date],          
cte1.[$ K  / Month Impact], cte1.[Other KIFID],     
cte1.[Other KIFID End Date]        
--cte4.PONumbers         
from cte1           
left join cte2          
on cte1.[Project code]=cte2.[Project code]          
left join cte3          
on cte1.[Project code]=cte3.[Project code]          
left join cte4          
on cte1.[Project code]=cte4.[Project code] 
left join cte5          
on cte1.[Project code]=cte5.[Project code]     
 -- order by cte1.[$ K  / Month Impact] desc           
) A
GROUP BY 
A.[Customer Group], A.[Customer Name], A.[L4], A.[L3], UPPER(A.[L2]), A.AM,
A.[Sales L3 Name],           
A.[Sales L2 Name],
A.[MSA / KIFNo],     
A.[MSA End Date], A.[SOW / KIFNo],    
 A.[SOW End Date], A.[PO Numbers],     
 A.[PO KIFID], A.[PO End Date],      
 A.[Other KIFID],     
A.[Other KIFID End Date] 
         ) B
         ORDER BY B.[$ K  / Month Impact] DESC
0
santosh kumar 20 नवम्बर 2019, 09:15

1 उत्तर

सबसे बढ़िया उत्तर

संपादित

    SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetL1AgeingBucket] 
    (@sapid VARCHAR(10),         
     @RagValue VARCHAR(50))          
AS
    DECLARE @temp TABLE
                  (          
                      [Project code] VARCHAR(100),
                      [L3 Sales Name] VARCHAR(100),          
                      PO_NUMBER NVARCHAR(MAX),
                      POValidTo NVARCHAR(MAX),
                      [DU Name] VARCHAR(100),
                      [SDU Name] VARCHAR(100),
                      [VBDU Name] VARCHAR(100),         
                      [Customer Name] VARCHAR(100),
                      [PO KIF] VARCHAR(MAX)          
                  )           

insert into @temp          
select distinct t1.[Project code], t3.[L3 Sales Name], t2.PONumber, CONVERT(varchar(10), t2.[POValidTo], 126),        
t1.[DU Desc (L4)] [DU Name], t1.[SDU Name (L3)] [SDU Name], t1.[VBDU Desc (L2)] [VBDU Name],t1.[Customer Name], t1.[PO KIFID]          
from TBL_MSA_SOW t1           
inner join [tbl_PO] as t2           
on t1.[Project code]=t2.[Project Code]          
inner join [tbl_Sales_mapping] as t3          
on t1.[Project Code]=t3.[Project Code]           
where t2.[PO RAG check]=@RagValue          
and t1.[GVBDUH Code] = CASE WHEN EXISTS(Select * from TBL_MSA_SOW Where [GVBDUH Code] = @sapid) THEN @sapid ELSE [GVBDUH Code] END and t3.[AM Sales Name] !='-';          

with cte1 as          
(select distinct t1.[DU Desc (L4)] as [L4], t1.[SDU Name (L3)] as [L3], t1.[VBDU Desc (L2)] as [L2], t3.[Customer Name], t3.[AM Sales Name] as AM,           
 t1.[Project code], t3.[L3 Sales Name] as [Sales L3 Name],           
t3.[L2 sales Name] as [Sales L2 Name], Convert(nvarchar(255),ISNULL(t1.[MSA], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[MSA KIFNO], '')) as [MSA / KIFNO],     
CONVERT(varchar(10), t1.[MSA End Date], 126) as [MSA End Date], Convert(nvarchar(255),ISNULL(t1.[Customer SOW], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[SOW KIFNO], '')) as [SOW / KIFNO],    
  CONVERT(varchar(10), t1.[SOW End Date], 126) as [SOW End Date],      
round(t1.[Avg Rev based on AMJ '18 PP (in $K)],0) as [$ K  / Month Impact],  
 t1.[Other KIFID], CONVERT(varchar(10),     
 t1.[Other KIFID End Date], 126) as [Other KIFID End Date],          
t3.[Customer Group]            
from [tbl_MSA_SOW] as t1 left join [tbl_PO] as t2           
on t1.[Project code] = t2.[Project Code]          
inner join [tbl_Sales_mapping] as t3           
on t1.[Project Code]=t3.[Project Code]           
where (t1.[MSA RAG check]=@RagValue or t1.[SOW RAG check]=@RagValue          
or t2.[PO RAG check]=@RagValue)           
and t1.[GVBDUH Code] =CASE WHEN EXISTS(Select * from TBL_MSA_SOW Where [GVBDUH Code] = @sapid) THEN @sapid ELSE [GVBDUH Code] END and t3.[AM Sales Name] !='-'          
group by t3.[L3 Sales Name], t3.[AM Sales Name], t1.[DU Desc (L4)] , t1.[SDU Name (L3)], t1.[VBDU Desc (L2)], t3.[Customer Name],           
t1.[Project code], t3.[L2 sales Name],  Convert(nvarchar(255),ISNULL(t1.[MSA], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[MSA KIFNO], '')),     
CONVERT(varchar(10), t1.[MSA End Date], 126), Convert(nvarchar(255),ISNULL(t1.[Customer SOW], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[SOW KIFNO], '')),    
CONVERT(varchar(10), t1.[SOW End Date], 126),      
t1.[Avg Rev based on AMJ '18 PP (in $K)], 
t1.[Other KIFID], CONVERT(varchar(10),     
t1.[Other KIFID End Date], 126), t3.[Customer Group]),         

cte2 as          
(select distinct t1.[DU Desc (L4)] as [L4], t1.[SDU Name (L3)] as [L3], t1.[VBDU Desc (L2)] as [L2], t3.[Customer Name], t3.[AM Sales Name] as AM,           
 t1.[Project code], t3.[L3 Sales Name] as [Sales L3 Name],           
t3.[L2 sales Name] as [Sales L2 Name],Convert(nvarchar(255),ISNULL(t1.[MSA], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[MSA KIFNO], '')) as [MSA / KIFNO],
 CONVERT(varchar(10), t1.[MSA End Date], 126) as [MSA End Date],    
  '' as [SOW / KIFNo], CONVERT(varchar(10), t1.[SOW End Date],    
 126) as [SOW End Date],      
round(t1.[Avg Rev based on AMJ '18 PP (in $K)],0) as [$ K  / Month Impact], t1.[Other KIFID],    
 CONVERT(varchar(10), t1.[Other KIFID End Date], 126) as [Other KIFID End Date]             
from [tbl_MSA_SOW] as t1 left join [tbl_PO] as t2           
on t1.[Project code] = t2.[Project Code]          
inner join [tbl_Sales_mapping] as t3           
on t1.[Project Code]=t3.[Project Code]           
where t1.[MSA RAG check]=@RagValue           
and t1.[GVBDUH Code] =CASE WHEN EXISTS(Select * from TBL_MSA_SOW Where [GVBDUH Code] = @sapid) THEN @sapid ELSE [GVBDUH Code] END and t3.[AM Sales Name] !='-'          
group by t3.[L3 Sales Name], t3.[AM Sales Name], t1.[DU Desc (L4)] , t1.[SDU Name (L3)], t1.[VBDU Desc (L2)], t3.[Customer Name],          
t1.[Project code], t3.[L2 sales Name], Convert(nvarchar(255),ISNULL(t1.[MSA], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[MSA KIFNO], '')),    
CONVERT(varchar(10), t1.[MSA End Date], 126), Convert(nvarchar(255),ISNULL(t1.[Customer SOW], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[SOW KIFNO], '')), CONVERT(varchar(10), t1.[SOW End Date], 126),       
t1.[Avg Rev based on AMJ '18 PP (in $K)], t1.[Other KIFID], CONVERT(varchar(10), t1.[Other KIFID End Date], 126)),         

cte3 as          
(select distinct t1.[DU Desc (L4)] as [L4], t1.[SDU Name (L3)] as [L3], t1.[VBDU Desc (L2)] as [L2], t3.[Customer Name], t3.[AM Sales Name] as AM,           
 t1.[Project code], t3.[L3 Sales Name] as [Sales L3 Name],           
t3.[L2 sales Name] as [Sales L2 Name],  '' as [MSA / KIFNo], CONVERT(varchar(10), t1.[MSA End Date], 126) as [MSA End Date], Convert(nvarchar(255),ISNULL(t1.[Customer SOW], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[SOW KIFNO], '')) as [SOW / KIFNO],    
 CONVERT(varchar(10), t1.[SOW End Date], 126) as [SOW End Date],      
round(t1.[Avg Rev based on AMJ '18 PP (in $K)],0) as [$ K  / Month Impact], t1.[Other KIFID],    
 CONVERT(varchar(10), t1.[Other KIFID End Date], 126) as [Other KIFID End Date]            
from [tbl_MSA_SOW] as t1 left join [tbl_PO] as t2           
on t1.[Project code] = t2.[Project Code]          
inner join [tbl_Sales_mapping] as t3           
on t1.[Project Code]=t3.[Project Code]           
where t1.[SOW RAG check]=@RagValue          
and t1.[GVBDUH Code] =CASE WHEN EXISTS(Select * from TBL_MSA_SOW Where [GVBDUH Code] = @sapid) THEN @sapid ELSE [GVBDUH Code] END and t3.[AM Sales Name] !='-'          
group by t3.[L3 Sales Name], t3.[AM Sales Name], t1.[DU Desc (L4)], t1.[SDU Name (L3)], t1.[VBDU Desc (L2)], t3.[Customer Name],          
t1.[Project code], t3.[L2 sales Name], Convert(nvarchar(255),ISNULL(t1.[MSA], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[MSA KIFNO], '')),    
 CONVERT(varchar(10), t1.[MSA End Date], 126), Convert(nvarchar(255),ISNULL(t1.[Customer SOW], ''))+'/'+Convert(nvarchar(255), ISNULL(t1.[SOW KIFNO], '')), CONVERT(varchar(10), t1.[SOW End Date], 126),       
t1.[Avg Rev based on AMJ '18 PP (in $K)], t1.[Other KIFID], CONVERT(varchar(10), t1.[Other KIFID End Date], 126)),      


cte4 as      
(select distinct       
[Project code],[L3 Sales Name],      
[DU Name],[SDU Name],[VBDU Name],[Customer Name],      
 STUFF(      
        (      
        select  ',' + PO_NUMBER FROM @temp d      
        where [Project code]=t.[Project code]              
  for xml path('')      
        ), 1, 1, '') as [PO Numbers],
STUFF(      
        (      
        select  distinct ',' + [PO KIF] FROM @temp d      
        where [Project code]=t.[Project code]              
  for xml path('')      
        ), 1, 1, '') as [PO KIFID]      
 from @temp t),

 cte5 as
(select distinct 
[Project code],[L3 Sales Name],
[DU Name],[SDU Name], [VBDU Name],[Customer Name],
 STUFF(
        (
        select  ',' + POValidTo FROM @temp d
        where [Project code]=t.[Project code]        
        for xml path('')
        ), 1, 1, '') as [PO End Date]   
 from @temp t)   


 SELECT * 
 FROM ( 
 SELECT  A.[Customer Group], A.[Customer Name], A.[L4], A.[L3],UPPER(A.[L2]) L2, A.AM,
A.[Sales L3 Name],           
A.[Sales L2 Name],
A.[MSA / KIFNo],     
A.[MSA End Date], A.[SOW / KIFNo],    
 A.[SOW End Date], A.[PO Numbers],     
 CASE A.[PO Numbers] 
       WHEN NULL THEN '' 
       ELSE  A.[PO KIFID]
END as [PO KIFID] 
 , A.[PO End Date],    
 A.[Other KIFID],     
A.[Other KIFID End Date] , 
sum(A.[$ K  / Month Impact]) AS [$ K  / Month Impact]
  FROM (

select distinct cte1.[Customer Group], cte1.[Customer Name], cte1.[L4], cte1.[L3], cte1.[L2], cte1.AM,           
--cte1.Project, cte1.[Project code],         
cte1.[Sales L3 Name],           
cte1.[Sales L2 Name],        
-- cte2.[MSA No], cte3.[SOW No],         
 cte2.[MSA / KIFNo],     
cte2.[MSA End Date], cte3.[SOW / KIFNo],    
 cte3.[SOW End Date], cte4.[PO Numbers], cte4.[PO KIFID], cte5.[PO End Date],          
cte1.[$ K  / Month Impact], cte1.[Other KIFID],     
cte1.[Other KIFID End Date]        
--cte4.PONumbers         
from cte1           
left join cte2          
on cte1.[Project code]=cte2.[Project code]          
left join cte3          
on cte1.[Project code]=cte3.[Project code]          
left join cte4          
on cte1.[Project code]=cte4.[Project code] 
left join cte5          
on cte1.[Project code]=cte5.[Project code]     
 -- order by cte1.[$ K  / Month Impact] desc           
) A
GROUP BY 
A.[Customer Group], A.[Customer Name], A.[L4], A.[L3], UPPER(A.[L2]), A.AM,
A.[Sales L3 Name],           
A.[Sales L2 Name],
A.[MSA / KIFNo],     
A.[MSA End Date], A.[SOW / KIFNo],    
 A.[SOW End Date], A.[PO Numbers],     
 A.[PO KIFID], A.[PO End Date],      
 A.[Other KIFID],     
A.[Other KIFID End Date] 
         ) B
         ORDER BY B.[$ K  / Month Impact] DESC
0
Syed Wahhab 20 नवम्बर 2019, 10:04