question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked pituach answered

Require Output to display date and all section.

I want to display all section whether their value exit or not and display date,


 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);
 CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,weight int,Entrydate DATETIME,DelID int);
 CREATE TABLE #Emp_Strength(EID INT,SecID INT,QTY int, Entrydate DATETIME);
 GO
 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',2,4,100);
 INSERT INTO #Bigbalprd VALUES(1,1,1,1,1,'02-06-2021',null,100)
 , (2,3,3,3,1,'02-06-2021',null,100)
 , (3,4,null,4,1,'02-06-2021',null,100)
 , (4,4,null,4,1,'02-06-2021',null,100)
 , (4,5,null,4,1,'02-06-2021',null,100);
     
 INSERT INTO #Probale VALUES(1,1,1,100,'02-06-2021',null)
 , (2,3,1,200,'02-06-2021',null)
 , (3,11,1,200,'02-06-2021',null)
 , (4,10,1,200,'02-06-2021',null)
 , (3,8,1,200,'02-06-2021',null)
 , (4,9,1,200,'02-06-2021',null)
 , (4,9,1,200,'02-06-2021',null);
     
 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
 --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
 --left join Emp_Strength emp on emp.SecID = sec.SecID
 --FULL OUTER JOIN Sections s ON i.SecID = s.SecID
 where (b.DelID is null and b.Entrydate BETWEEN @StartDate AND @Enddate or b.EntryDate is null ) 
 and
 (b1.DelID is null 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)
 --group by cte.secnam,cte.Smallbale_QTY,cte.Bigbale_Weight,cte.Small_Bale_weight,cte.Bigbale_QTY,emp.Employee_QTY)
     
     
     
 select Secnam,Smallbale_QTY,Small_Bale_weight,Bigbale_QTY,Bigbale_Weight,Total_QTY,Total_Weight,Employee_QTY,
 ISNULL(((nullif(Total_Weight,0))/nullif(Employee_QTY,0)),0) as peremp_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((Small_Bale_weight)*100.0/(sum(Total_Weight)over(PARTITION  BY (1) 
 ))AS numeric(10,2))as percentage ,
 cast((Bigbale_Weight)*100.0/(sum(Total_Weight)over(PARTITION  BY (1) 
 ))AS numeric(10,2))as bpercentage ,
 ISNULL(cast((Small_Bale_weight)*100.0/(sum(Total_Weight)over(PARTITION  BY (1) 
 ))AS numeric(10,2))+cast((Bigbale_Weight)*100.0/(sum(Total_Weight)over(PARTITION  BY (1) 
 ))AS numeric(10,2)),0) as P_Total
 from cte1
 where  Smallbale_QTY+Small_Bale_weight+Bigbale_QTY+Bigbale_Weight+Total_QTY+Total_Weight+Employee_QTY<>0
 group by cte1.Secnam,Small_Bale_weight,cte1.Smallbale_QTY,cte1.Bigbale_QTY,cte1.Bigbale_Weight,cte1.Total_QTY,cte1.Employee_QTY,cte1.Total_Weight


Require Output


139029-image.png


sql-server-generalsql-server-transact-sql
image.png (56.9 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.

1 Answer

pituach avatar image
1 Vote"
pituach answered

Good day and welcome to the QnA forums

I assume that best option is to go and write your query from scratch, but the simplest option for lazy is to wrape your query in CTE and then add the missing information using except. Since we already know that all the rest of the Secnam has the same values 0 in all columns, then this is really simple (but probably not the best way to go)

So... without re-write your query which I have no time now, here is a simple answer

  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
  --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
  --left join Emp_Strength emp on emp.SecID = sec.SecID
  --FULL OUTER JOIN Sections s ON i.SecID = s.SecID
  where (b.DelID is null and b.Entrydate BETWEEN @StartDate AND @Enddate or b.EntryDate is null ) 
  and
  (b1.DelID is null 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
  )
  --group by cte.secnam,cte.Smallbale_QTY,cte.Bigbale_Weight,cte.Small_Bale_weight,cte.Bigbale_QTY,emp.Employee_QTY)
        
 ,MyCTE as ( 
  select Secnam,Smallbale_QTY,Small_Bale_weight,Bigbale_QTY,Bigbale_Weight,Total_QTY,Total_Weight,Employee_QTY,
  ISNULL(((nullif(Total_Weight,0))/nullif(Employee_QTY,0)),0) as peremp_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((Small_Bale_weight)*100.0/(sum(Total_Weight)over(PARTITION  BY (1) 
  ))AS numeric(10,2))as percentage ,
  cast((Bigbale_Weight)*100.0/(sum(Total_Weight)over(PARTITION  BY (1) 
  ))AS numeric(10,2))as bpercentage ,
  ISNULL(cast((Small_Bale_weight)*100.0/(sum(Total_Weight)over(PARTITION  BY (1) 
  ))AS numeric(10,2))+cast((Bigbale_Weight)*100.0/(sum(Total_Weight)over(PARTITION  BY (1) 
  ))AS numeric(10,2)),0) as P_Total
  from cte1
  where  Smallbale_QTY+Small_Bale_weight+Bigbale_QTY+Bigbale_Weight+Total_QTY+Total_Weight+Employee_QTY<>0
  group by cte1.Secnam,Small_Bale_weight,cte1.Smallbale_QTY,cte1.Bigbale_QTY,cte1.Bigbale_Weight,cte1.Total_QTY,cte1.Employee_QTY,cte1.Total_Weight
 )
    
 SELECT 
     @StartDate as [Date], Secnam,Smallbale_QTY,Small_Bale_weight,Bigbale_QTY,Bigbale_Weight,Total_QTY,
     Total_Weight,Employee_QTY, peremp_QTY, percentage, bpercentage,P_Total
 FROM MyCTE
    
  UNION ALL
    
  -- adding missing Secnam
 SELECT @StartDate, Secnam , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
 FROM #Sections 
 except
 SELECT @StartDate, Secnam , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
 FROM MyCTE
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.