Required Ouput Below

Question

I need out ,below is data

Create table #floor (FID int,FName varchar(50))  
Create table #ItemMasterFile (Codeitem int,Descriptionitem varchar(50),Weight varchar(50))  
Create table #Bigbalprd (B_ID int,Codeitem int,Bpqty int,Bweight int,Entrydate date,FID int)   
   
 Insert into #floor values (1 ,'First')  
,(2,'Second')  
  
 INSERT INTO #ItemMasterFile VALUES  
       (1,'A',111)  
     , (2,'B',222)  
     , (3,'C',331)  
     , (4,'D',331)  
     , (5,'e',441)  
     , (6,'f',554)  
     , (7,'g',114)  
     , (8,'h',552)  
     , (9,'K',553)  
     , (10,'L',443)  
     , (11,'M',223);  
  
  
  
  
	 INSERT INTO #Bigbalprd VALUES  
     (111,1,1,500,'04-01-2021',1)  
     ,(112,1,1,200,'04-01-2021',1)  
     ,(113,6,1,300,'04-01-2021',2)  
     ,(114,6,1,100,'04-01-2021',2)  
   ,(115,1,1,300,'04-01-2021',2)  
  
  
   CREATE TABLE #ConIssuance (CID INT,iWeight int,QTY int,EntryDate  date,FID int)    
  
    INSERT INTO #ConIssuance VALUES(1111,1000,1,'04-01-2021',1)    
 INSERT INTO #ConIssuance VALUES(1112,2000,1,'04-01-2021',2)   
 INSERT INTO #ConIssuance VALUES(1113,800,1,'04-01-2021',2)    
 INSERT INTO #ConIssuance VALUES(1114,600,1,'04-01-2021',1)    

Column Calculation.

Bweight =item wise Bweight sum form #Bigbalprd table date wise and Floor wise.
IWeight = sum from of #ConIssuance table date wise and floor wise
Percentage = Bweight divide by Iweight and multiply by 100 (Bweight/Iweight)*100

Answer 1

Hi @Analyst_SQL ,

Please refer below:

;with B as (  
select a.Descriptionitem,sum(b.Bweight) Bweight,b.Entrydate, FName ,c.FID  
from #ItemMasterFile a  
inner join #Bigbalprd b on a.Codeitem=b.Codeitem  
inner join #floor c on c.FID=b.FID  
group by a.Descriptionitem,b.Entrydate,c.FName,c.FID)  
,C as (  
select a.FID,sum(a.iWeight) iWeight,EntryDate  
from  #ConIssuance a   
inner join #floor b on b.FID=a.FID  
group by a.FID,EntryDate)  
select b.Entrydate [Date],upper(b.Descriptionitem) Description,B.Bweight,C.iWeight  
, cast(B.Bweight as float)/cast(C.iWeight as float)*100 [Percentage], B.FName [Floor]  
from B  
inner join C on B.FID=C.FID  

Output:

Date	Description	Bweight	iWeight	Percentage	Floor  
2021-04-01	A	700	1600	43.75	First  
2021-04-01	A	300	2800	10.7142857142857	Second  
2021-04-01	F	400	2800	14.2857142857143	Second  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.