Sparate Column Data Floor wise?

asked 2021-04-16T04:10:02.163+00:00
akhter hussain 2,491 Reputation points

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,473 questions
{count} votes

Accepted answer
  1. answered 2021-04-16T06:35:43.453+00:00
    MelissaMa-MSFT 24,116 Reputation points

    Hi @akhter hussain ,

    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.

    No comments

0 additional answers

Sort by: Most helpful