Expected Output Require.

Analyst_SQL 3,531 Reputation points
2021-10-27T06:57:25.337+00:00

Below is data ,
i tried to achieved ,but having issue,if Data exit in FID=2 and does not exit in FID=1 then data does not get display you can see Section(Men) ,I inserted last record in #Probale ,which is not displaying. 

 CREATE TABLE #Category (CID INT,CName VARCHAR(50));  
 CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50));  
 CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,CID INT,weight int);  
 CREATE TABLE #Bigbalprd(BID INT,CodeItem INT,SecID INT,CID INT,Bpqty INT,Entrydate DATETIME , DelID int,Bweight int,FID int);  
 CREATE TABLE #Probale(prdno INT,CodeItem INT,prdQTY INT,secid int,weight int,Entrydate DATETIME,DelID int,FID int);  
 CREATE TABLE #Emp_Strength(EID INT,SecID INT,QTY int, Entrydate DATETIME);  
   
 INSERT INTO #Category VALUES(1,'INDIA'),(2,'INDIA(Cut)'),(3,'Pakistan'),(4,'Pakistan(Cut)')  
 INSERT INTO #Sections VALUES(1,'HR'),(2,'Baby'),(3,'Ladies'),(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),  
 (7,'Scrit'),(8,'Men'),(9,'HHR'),(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),  
 (15,'m-Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');  
       
 INSERT INTO #ItemMasterFile VALUES(1,'A',1,1,100)  
 , (2,'B',2,2,100)  
 , (3,'C',3,3,100)  
 , (4,'D',4,null,100)  
 , (5,'e',5,null,100)  
 , (6,'f',6,null,100)  
 , (7,'g',4,2,100)  
 , (8,'h',4,3,100)  
 , (9,'K',2,2,100)  
 , (10,'L',4,3,100)  
 , (11,'M',11,4,100)  
  , (12,'O',8,4,100);  
 INSERT INTO #Bigbalprd VALUES(1,1,1,1,1,'02-06-2021',null,100,1)  
 , (2,3,3,3,1,'02-06-2021',null,100,1)  
 , (3,4,null,4,1,'02-06-2021',null,100,1)  
 , (4,4,null,4,1,'02-06-2021',null,100,1)  
 , (5,5,null,4,1,'02-06-2021',null,100,1);  
       
 INSERT INTO #Probale VALUES(1,1,1,3,100,'02-06-2021',null,1)  
 , (2,3,1,1,200,'02-06-2021',null,1)  
 , (3,11,1,2,200,'02-06-2021',null,1)  
 , (4,10,1,4,200,'02-06-2021',null,1)  
 , (5,8,1,5,200,'02-06-2021',null,1)  
 , (6,9,1,6,200,'02-06-2021',null,1)  
 , (7,9,1,7,200,'02-06-2021',null,1)  
  , (8,11,1,11,200,'02-06-2021',null,2)  
    , (9,12,1,8,200,'02-06-2021',null,2);  
       
 INSERT INTO #Emp_Strength VALUES(1,1,4,'02-06-2021')  
 , (2,3,5,'02-06-2021')  
 , (3,3,3,'02-06-2021')  
 , (4,4,7,'02-06-2021');  
  
  
 DECLARE @StartDate DATETIME, @Enddate DATETIME  
 SET @StartDate = '02-06-2021'  
 SET @Enddate = '02-06-2021'  
  ;WITH emp  
           
  as  
  (  
  select Secnam,ISNULL(sum(e.qty),0) Employee_QTY from #Sections s  
  left join #Emp_Strength e on s.secid=e.secid  
  where (Entrydate BETWEEN @StartDate AND @Enddate or Entrydate is null)  
  group by Secnam  
  ),cte  
  AS  
  (  
  SELECT DISTINCT Sec.Secnam,  
  ISNULL(SUM(b1.Bpqty),0)Bigbale_QTY,ISNULL(sum(b1.Bweight),0)Bigbale_Weight,  
  ISNULL(SUM(b.prdQTY),0)Smallbale_QTY,ISNULL(SUM(case when b.prdQTY is null then 0 else  i.weight end ),0)Small_Bale_weight  
   
  FROM #ItemMasterFile i  
  LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem  
  LEFT OUTER JOIN #Bigbalprd b1 ON i.CodeItem = b1.CodeItem  
  Full Outer Join #Sections sec on i.SecID=sec.SecID  
  
  where (b.DelID is null and b.FID=1 and b.Entrydate BETWEEN @StartDate AND @Enddate or b.EntryDate is null )   
  and  
  (b1.DelID is null and  b1.FID=1 and b1.EntryDate BETWEEN @StartDate AND @Enddate or b1.EntryDate is Null )  
           
  GROUP BY sec.Secnam  
  ),  
  ctef  
  AS  
  (  
  SELECT DISTINCT Sec.Secnam,  
  ISNULL(SUM(b1.Bpqty),0)Bigbale_QTY2,ISNULL(sum(b1.Bweight),0)Bigbale_Weight2,  
  ISNULL(SUM(b.prdQTY),0)Smallbale_QTY2,ISNULL(SUM(case when b.prdQTY is null then 0 else  i.weight end ),0)Small_Bale_weight2  
  --ISNULL(SUM(emp.QTY),0)Employee_QTY  
  FROM #ItemMasterFile i  
  LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem  
  LEFT OUTER JOIN #Bigbalprd b1 ON i.CodeItem = b1.CodeItem  
  Full Outer Join #Sections sec on i.SecID=sec.SecID  
  
  where (b.DelID is null and b.FID=2 and b.Entrydate BETWEEN @StartDate AND @Enddate or b.EntryDate is null )   
  and  
  (b1.DelID is null and  b1.FID=2 and b1.EntryDate BETWEEN @StartDate AND @Enddate or b1.EntryDate is Null )  
           
  GROUP BY sec.Secnam  
  ),  
  cte1 as (  
  SELECT cte.secnam, Smallbale_QTY,Small_Bale_weight, Bigbale_QTY, Bigbale_Weight,  
  ( SELECT SUM(val)  
  FROM (VALUES (Bigbale_QTY)  
  , (Smallbale_QTY)  
           
  ) AS value(val)  
  ) AS Total_QTY,  
           
  ( SELECT SUM(val)  
  FROM (VALUES (Bigbale_Weight),  
  (Small_Bale_weight )  
  )AS value(val)  
  ) as Total_Weight,  
  coalesce(Employee_QTY,0) Employee_QTY  
  FROM cte left join emp on cte.secnam=emp.secnam  
  )  
  
,  
  ctef2 as (  
  SELECT ctef.secnam, Smallbale_QTY2,Small_Bale_weight2, Bigbale_QTY2, Bigbale_Weight2,  
  ( SELECT SUM(val)  
  FROM (VALUES (Bigbale_QTY2)  
  , (Smallbale_QTY2)  
           
  ) AS value(val)  
  ) AS Total_QTY,  
           
  ( SELECT SUM(val)  
  FROM (VALUES (Bigbale_Weight2),  
  (Small_Bale_weight2 )  
  )AS value(val)  
  ) as Total_Weight,  
  coalesce(Employee_QTY,0) Employee_QTY  
  FROM ctef left join emp on ctef.secnam=emp.secnam  
  )  
          
 ,MyCTE as (   
  select cte1.Secnam,cte1.Smallbale_QTY,ctef2.Smallbale_QTY2,cte1.Small_Bale_weight,ctef2.Small_Bale_weight2,cte1.Bigbale_QTY,cte1.Bigbale_Weight,  
  cte1.Total_QTY,cte1.Total_Weight,cte1.Employee_QTY,  
  
  --case when ((nullif(Total_Weight,0))/nullif(Employee_QTY,0)) is null then 0 else ((nullif(Total_Weight,0))/nullif(Employee_QTY,0)) end as peremp_QTY,  
  cast((cte1.Small_Bale_weight)*100.0/(sum(cte1.Total_Weight)over(PARTITION  BY (1)   
  ))AS numeric(10,2))as percentage ,  
  cast((cte1.Bigbale_Weight)*100.0/(sum(cte1.Total_Weight)over(PARTITION  BY (1)   
  ))AS numeric(10,2))as bpercentage ,  
  ISNULL(cast((cte1.Small_Bale_weight)*100.0/(sum(cte1.Total_Weight)over(PARTITION  BY (1)   
  ))AS numeric(10,2))+cast((cte1.Bigbale_Weight)*100.0/(sum(cte1.Total_Weight)over(PARTITION  BY (1)   
  ))AS numeric(10,2)),0) as P_Total  
  from cte1   
  Full Outer Join  ctef2 on cte1.Secnam=ctef2.Secnam  
  where  cte1.Smallbale_QTY+cte1.Small_Bale_weight+cte1.Bigbale_QTY+cte1.Bigbale_Weight+cte1.Total_QTY+cte1.Total_Weight+cte1.Employee_QTY<>0  
  group by cte1.Secnam,cte1.Small_Bale_weight,cte1.Smallbale_QTY,cte1.Bigbale_QTY,cte1.Bigbale_Weight,cte1.Total_QTY,  
  cte1.Employee_QTY,cte1.Total_Weight,ctef2.Smallbale_QTY2,ctef2.Small_Bale_weight2  
 )  
      
 SELECT   
      Secnam,Smallbale_QTY,smallbale_qty2,Small_Bale_weight,Small_Bale_weight2,Bigbale_QTY,Bigbale_Weight,Total_QTY,  
     Total_Weight,Employee_QTY, percentage, bpercentage,P_Total  
 FROM MyCTE  
      
  UNION ALL  
      
  -- adding missing Secnam  
 SELECT  Secnam , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,0  
 FROM #Sections   
 except  
 SELECT Secnam , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,0  
 FROM MyCTE

Output

144106-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,714 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
Accepted answer
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-11-02T02:20:05.057+00:00

    Please check: 

      DECLARE @StartDate DATETIME, @Enddate DATETIME  
  SET @StartDate = '02-06-2021'  
  SET @Enddate = '02-06-2021'  
   ;WITH emp  
               
   as  
   (  
   select Secnam,ISNULL(sum(e.qty),0) Employee_QTY from #Sections s  
   left join #Emp_Strength e on s.secid=e.secid  
   where (Entrydate BETWEEN @StartDate AND @Enddate or Entrydate is null)  
   group by Secnam  
   )  
   --SELECT * FROM emp  
   ,cte  
   AS  
   (  
   SELECT DISTINCT Sec.Secnam,  
   ISNULL(SUM(b1.Bpqty),0)Bigbale_QTY,ISNULL(sum(b1.Bweight),0)Bigbale_Weight,  
   ISNULL(SUM(b.prdQTY),0)Smallbale_QTY,ISNULL(SUM(case when b.prdQTY is null then 0 else  i.weight end ),0)Small_Bale_weight  
       
   FROM #ItemMasterFile i  
   LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem  
   LEFT OUTER JOIN #Bigbalprd b1 ON i.CodeItem = b1.CodeItem  
   Full Outer Join #Sections sec on i.SecID=sec.SecID  
      
   where (b.DelID is null and b.FID=1 and b.Entrydate BETWEEN @StartDate AND @Enddate or b.EntryDate is null )   
   and  
   (b1.DelID is null and  b1.FID=1 and b1.EntryDate BETWEEN @StartDate AND @Enddate or b1.EntryDate is Null )  
               
   GROUP BY sec.Secnam  
   )  
   --SELECT * FROM cte  
   ,  
   ctef  
   AS  
   (  
   SELECT DISTINCT Sec.Secnam,  
   ISNULL(SUM(b1.Bpqty),0)Bigbale_QTY2,ISNULL(sum(b1.Bweight),0)Bigbale_Weight2,  
   ISNULL(SUM(b.prdQTY),0)Smallbale_QTY2,ISNULL(SUM(case when b.prdQTY is null then 0 else  i.weight end ),0)Small_Bale_weight2  
   --ISNULL(SUM(emp.QTY),0)Employee_QTY  
   FROM #ItemMasterFile i  
   LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem  
   LEFT OUTER JOIN #Bigbalprd b1 ON i.CodeItem = b1.CodeItem  
   Full Outer Join #Sections sec on i.SecID=sec.SecID  
      
   where (b.DelID is null and b.FID=2 and b.Entrydate BETWEEN @StartDate AND @Enddate or b.EntryDate is null )   
   and  
   (b1.DelID is null and  b1.FID=2 and b1.EntryDate BETWEEN @StartDate AND @Enddate or b1.EntryDate is Null )  
               
   GROUP BY sec.Secnam  
   )  
   --SELECT * FROM   ctef  
     
   ,  
   cte1 as (  
   SELECT cte.secnam, Smallbale_QTY,Small_Bale_weight, Bigbale_QTY, Bigbale_Weight,  
   ( SELECT SUM(val)  
   FROM (VALUES (Bigbale_QTY)  
   , (Smallbale_QTY)  
               
   ) AS value(val)  
   ) AS Total_QTY,  
               
   ( SELECT SUM(val)  
   FROM (VALUES (Bigbale_Weight),  
   (Small_Bale_weight )  
   )AS value(val)  
   ) as Total_Weight,  
   coalesce(Employee_QTY,0) Employee_QTY  
   FROM cte left join emp on cte.secnam=emp.secnam  
   )  
      
 ,  
   ctef2 as (  
   SELECT ctef.secnam, Smallbale_QTY2,Small_Bale_weight2, Bigbale_QTY2, Bigbale_Weight2,  
   ( SELECT SUM(val)  
   FROM (VALUES (Bigbale_QTY2)  
   , (Smallbale_QTY2)  
               
   ) AS value(val)  
   ) AS Total_QTY,  
               
   ( SELECT SUM(val)  
   FROM (VALUES (Bigbale_Weight2),  
   (Small_Bale_weight2 )  
   )AS value(val)  
   ) as Total_Weight,  
   coalesce(Employee_QTY,0) Employee_QTY  
   FROM ctef left join emp on ctef.secnam=emp.secnam  
   )  
   --SELECT * FROM ctef2           
    
  ,MyCTE as (   
   select ISNULL(cte1.Secnam,ctef2.Secnam) Secnam,cte1.Smallbale_QTY,ctef2.Smallbale_QTY2,cte1.Small_Bale_weight,ctef2.Small_Bale_weight2,cte1.Bigbale_QTY,cte1.Bigbale_Weight,  
   cte1.Total_QTY,cte1.Total_Weight,cte1.Employee_QTY,  
      
   --case when ((nullif(Total_Weight,0))/nullif(Employee_QTY,0)) is null then 0 else ((nullif(Total_Weight,0))/nullif(Employee_QTY,0)) end as peremp_QTY,  
   cast((cte1.Small_Bale_weight)*100.0/(sum(cte1.Total_Weight)over(PARTITION  BY (1)   
   ))AS numeric(10,2))as percentage ,  
   cast((cte1.Bigbale_Weight)*100.0/(sum(cte1.Total_Weight)over(PARTITION  BY (1)   
   ))AS numeric(10,2))as bpercentage ,  
   ISNULL(cast((cte1.Small_Bale_weight)*100.0/(sum(cte1.Total_Weight)over(PARTITION  BY (1)   
   ))AS numeric(10,2))+cast((cte1.Bigbale_Weight)*100.0/(sum(cte1.Total_Weight)over(PARTITION  BY (1)   
   ))AS numeric(10,2)),0) as P_Total  
   from cte1   
   Full Outer Join  ctef2 on cte1.Secnam=ctef2.Secnam  
   --where  cte1.Smallbale_QTY+cte1.Small_Bale_weight+cte1.Bigbale_QTY+cte1.Bigbale_Weight+cte1.Total_QTY+cte1.Total_Weight+cte1.Employee_QTY<>0  
   group by cte1.Secnam,ctef2.Secnam,cte1.Small_Bale_weight,cte1.Smallbale_QTY,cte1.Bigbale_QTY,cte1.Bigbale_Weight,cte1.Total_QTY,  
   cte1.Employee_QTY,cte1.Total_Weight,ctef2.Smallbale_QTY2,ctef2.Small_Bale_weight2  
  )  
 SELECT * FROM mycte  
 order by Secnam

    Ouput:
    145662-image.png

    Regards
    Echo

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-10-28T02:55:47.913+00:00

    Hi @Analyst_SQL ，

    Your cte will filter out all data whose FID is not equal to 1. The FID of the last piece of data you inserted in the #Probale table is equal to 2, so there is no display: 

      DECLARE @StartDate DATETIME, @Enddate DATETIME  
  SET @StartDate = '02-06-2021'  
  SET @Enddate = '02-06-2021'  
;WITH emp as  
   (select Secnam,ISNULL(sum(e.qty),0) Employee_QTY from #Sections s  
   left join #Emp_Strength e on s.secid=e.secid  
   where (Entrydate BETWEEN @StartDate AND @Enddate or Entrydate is null)  
   group by Secnam)  
,cte AS  
(SELECT DISTINCT Sec.Secnam,  
ISNULL(SUM(b1.Bpqty),0)Bigbale_QTY,ISNULL(sum(b1.Bweight),0)Bigbale_Weight,  
ISNULL(SUM(b.prdQTY),0)Smallbale_QTY,ISNULL(SUM(case when b.prdQTY is null then 0 else  i.weight end ),0)Small_Bale_weight  
FROM #ItemMasterFile i  
LEFT OUTER JOIN #Probale b ON i.CodeItem = b.CodeItem  
LEFT OUTER JOIN #Bigbalprd b1 ON i.CodeItem = b1.CodeItem  
Full Outer Join #Sections sec on i.SecID=sec.SecID  
where (b.DelID is null and b.FID=1   
and b.Entrydate BETWEEN @StartDate AND @Enddate or b.EntryDate is null )   
and  
(b1.DelID is null and  b1.FID=1 and b1.EntryDate BETWEEN @StartDate AND @Enddate or b1.EntryDate is Null )  
 GROUP BY sec.Secnam)  
  
SELECT * FROM cte

    In the cte above, your WHERE clause filters out Men.

    Best regards
    Echo

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".