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.
12,815 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments
Accepted answer
  1. Ronen Ariely 15,096 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

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest