Below is my data
CREATE TABLE #Containerno(CID INT,Contno VARCHAR(50),ConWeight nvarchar(50),Entrydate date ,DelID int,cat_ID int)
CREATE TABLE #ConIssuance (IID INT,CID INT,QTY INT,IWeight int,Entrydate DATETIME,DelID int)
INSERT INTO #Containerno VALUES(0,'ABC0000',2000,'2022-01-28',null,null)
INSERT INTO #Containerno VALUES(1,'ABC1111',2000,'2022-01-28',null,null)
INSERT INTO #Containerno VALUES(2,'ABC1222',1500,'2022-01-30',null,null)
INSERT INTO #Containerno VALUES(3,'ABC1333',7800,'2022-02-01',null,null)
INSERT INTO #Containerno VALUES(4,'ABC1444',4500,'2022-02-02',null,null)
INSERT INTO #Containerno VALUES(5,'ABC1555',4700,'2022-02-15',null,null)
INSERT INTO #Containerno VALUES(6,'ABC1666',5000,'2022-02-15',null,null)
INSERT INTO #Containerno VALUES(7,'ABC1777',6000,'2022-02-16',null,null)
INSERT INTO #ConIssuance VALUES(1001,1,1,1000,'2022-01-29',null)
INSERT INTO #ConIssuance VALUES(1002,2,1,500,'2022-01-30',null)
INSERT INTO #ConIssuance VALUES(1003,2,1,500,'2022-02-01',null)
INSERT INTO #ConIssuance VALUES(1004,3,1,2000,'2022-02-03',null)
INSERT INTO #ConIssuance VALUES(1005,4,1,1000,'2022-02-03',null)
INSERT INTO #ConIssuance VALUES(1006,4,1,1000,'2022-02-03',null)
INSERT INTO #ConIssuance VALUES(1007,3,1,1000,'2022-02-03',null)
What I have tried:
<pre>Declare @StartDate date='2022-02-01'
Declare @Enddate date ='2022-02-15'
;with cte1 as (
Select CID,ContNo, ConWeight,entrydate IN_Date from #Containerno
where DelID is null
group by CID,ContNo,ConWeight,entrydate
)
,cte2 as (select CID,Sum(IWeight)IWeight from #ConIssuance where
EntryDate<@StartDate and
DelID is null
group by CID
),
cte3 as (select i.CID,C.ContNo,C.entrydate IN_Date,Sum(I.IWeight)Isu_Weight from #ConIssuance I
right join #Containerno C on C.CID=I.CID
where I.EntryDate between @StartDate and @Enddate
and
I.DelID is null
group by I.CID,C.ContNo,c.entrydate
)
,cte4 as (
select isnull(cte3.Contno,cte1.Contno)Contno,Isnull(cte1.IN_Date,cte3.In_date)IN_Date ,
cte2.IWeight,
(isnull(cast(ConWeight as Decimal(10,4) ),0)-iSNULL((cast(IWeight as DECIMAL(10,2))),0)) as Opening_Weight
,cte3.Isu_Weight
from cte2
right join cte1 on cte1.CID=cte2.CID
right join cte3 on cte1.CID=cte3.CID
)
select Contno ,IN_Date,Opening_Weight,Isu_Weight,
isnull((Opening_Weight)-Isu_Weight,0) Closing_Weight from cte4
order by IN_Date asc
I want that when CID value is issued or not in #
ConIssuance
table ,it must be display in output ,as you can see in image 15/02/2022 is not display in current query output,but i want below output
Contno Entrydate Opening_Weight Isue_Weight Closing_Weight
ABC1222 1/30/2022 1000 500 500
ABC1333 2/1/2022 7800 3000 4800
ABC1444 2/2/2022 4500 2000 2500
ABC1555 2/15/2022 4700 0 4700
ABC1666 2/15/2022 5000 0 5000