Require Output to display date and all section.

Analyst_SQL 3,551 Reputation points
2021-10-09T09:12:36.993+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,331 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ronen Ariely 15,106 Reputation points
    2021-10-09T14:04:22.327+00:00

    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
    
    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful