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

