question

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

Sparate Column Data Floor wise?

I want to separate data of a column in floor wise from table #Bigbalprd FID column,mean i have two floor (First and second),so i want to separate their quantity and Weight ,sum ,if same item exit ,its qty and weight .
In Image ,F1_Qty and F1_Weight is belong to first floor and F2_Qty and F2_Weight is belong to Second floor,
Last column Total_Qty=(F1_Qty+F2_Qty) and Total_Weight=(F1_Weight+F2_Weight),also want to display all item weight their qty exit or not.
I am retrieving Data between Date.

 Create table #Category(CID int,CName varchar(50))
 Create table #Section (SecID int,SecName varchar(50))
   Create table #ItemMasterFile (Codeitem int,Descriptionitem varchar(50),Packsize varchar(50),CID int,SecId int)
     Create table #Bigbalprd (B_ID int,Codeitem int,Bpqty int,Bweight int,Entrydate date,CID int,Secid int,FID int)
         
   Create table #Floor (FID int,Fname Varchar(50))
   INSERT INTO #Floor VALUES
   (1,'First') 
   , (2,'Second');
        
     INSERT INTO #Category VALUES
      (11,'C1')
     , (22,'C2')
     , (33,'C3')
     , (44,'C4')
     , (55,'C5');
   INSERT INTO #Section VALUES
      (1,'S1')
     , (2,'S2')
     , (1,'S3')
     , (2,'S4')
     , (1,'S5');
            
  INSERT INTO #ItemMasterFile VALUES
       (1,'A','Bigbale',11,1)
     , (2,'B','Bigbale',22,2)
     , (3,'C','Bigbale',33,1)
     , (4,'D','Bigbale',33,1)
     , (5,'e','Bigbale',44,1)
     , (6,'f','Bigbale',55,4)
     , (7,'g','Bigbale',11,4)
     , (8,'h','Bigbale',55,2)
     , (9,'K','Bigbale',55,3)
     , (10,'L','Bigbale',44,3)
     , (11,'M','Bigbale',22,3);
                    
                    
     INSERT INTO #Bigbalprd VALUES
     (111,1,1,500,'04-01-2021',11,1,1)
     ,(112,2,1,200,'04-01-2021',22,2,2)
     ,(113,1,1,300,'04-01-2021',11,1,1)
     ,(114,6,1,100,'04-01-2021',55,4,1)
     ,(115,1,1,200,'04-01-2021',11,1,1)
     ,(116,1,1,300,'04-01-2021',11,1,1)
     ,(117,7,1,100,'04-01-2021',11,4,2)
     ,(118,5,1,200,'04-01-2021',44,1,2)
     ,(119,8,1,300,'04-01-2021',55,3,1)
      ,(120,2,1,300,'04-01-2021',22,2,2)
  , (121,1,1,800,'04-01-2021',11,1,2)
  , (122,1,1,400,'04-01-2021',11,1,2)
    
 Expected Output

88492-image.png



sql-server-generalsql-server-transact-sql
image.png (29.1 KiB)
· 1
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.

Hi akhterhussain-3167,

Could you please provide any update? Thanks.

Best regards
Melissa

0 Votes 0 ·

1 Answer

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

Hi @akhterhussain-3167,

After checking, the insert data of #Section should be as below:

    INSERT INTO #Section VALUES
       (1,'S1')
      , (2,'S2')
      , (3,'S3')
      , (4,'S4')
      , (5,'S5');

Please refer below:

 ;with cte as (
 select a.Codeitem,a.Descriptionitem,b.CName,c.SecName,sum(d.Bpqty) Bpqty,sum(d.Bweight) Bweight
 ,'F'+cast(d.FID as char(1))+'_QTY'  FID_QTY
 ,'F'+cast(d.FID as char(1))+'_Weight'  FID_Weight
 from #ItemMasterFile a
 left join #Category b on a.CID=b.CID
 left join #Section c on a.SecId=c.SecID
 left join #Bigbalprd d on a.Codeitem=d.Codeitem
 group by a.Codeitem,a.Descriptionitem,b.CName,c.SecName,d.FID )
 ,cte1 as (
 select Codeitem,upper(Descriptionitem) Descriptionitem,CName,SecName
 ,isnull(max([F1_QTY]),0) [F1_QTY]
 ,isnull(max([F1_Weight]),0) [F1_Weight]
 ,isnull(max([F2_QTY]),0)  [F2_QTY]
 ,isnull(max([F2_Weight]),0)  [F2_Weight]
 FROM cte AS R
     PIVOT(MAX(Bpqty) FOR FID_QTY IN ([F1_QTY], [F2_QTY])) AS QTY
     PIVOT(MAX(Bweight) FOR FID_Weight IN ([F1_Weight], [F2_Weight])) AS Weight
 group by Codeitem,Descriptionitem,CName,SecName)
 ,cte2 as (
 select *,[F1_QTY]+[F2_QTY] Total_Qty,[F1_Weight]+[F2_Weight] Total_Weight
  from cte1)
  select * from (
  select * from cte2
  union
  select NULL,NULL,NULL,NULL,sum([F1_QTY]),sum([F1_Weight]),sum([F2_QTY]),sum([F2_Weight]),sum(Total_Qty),sum(Total_Weight)
  from cte2 ) a
  order by IIF(Codeitem is null,99,Codeitem)

Output:

88517-out.png

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.


out.png (13.5 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.