question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked EchoLiu-msft commented

Expected Output Require.

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-generalsql-server-transact-sql
image.png (52.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

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



image.png (46.1 KiB)
image.png (45.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft commented

Hi @akhterhussain-3167

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".

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I want to Display Men , and i also want record get filter and display where FID=2
I want to display FID=1 and FID=2 record.

0 Votes 0 ·

@EchoLiu-msft

 Any update regarding my queries? 

I want to display both FID 1 and 2 Data in both table and also display all section whether data exit or not .

0 Votes 0 ·

145339-image.png
Following up, sorry for the late reply.
145368-image.png
Like this?


0 Votes 0 ·
image.png (40.9 KiB)
image.png (5.4 KiB)
Show more comments