question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked MelissaMa-msft edited

Required Ouput Below

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


101738-image.png


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

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @akhterhussain-3167,

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.

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.