Opening and closing query need contno wise

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Below is my data

SQL

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:

SQL

<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

Posted 18-Feb-22 23:24pm

akhter86

Updated 20-Feb-22 8:44am

Add a Solution

Comments

Herman<T>.Instance 20-Feb-22 5:22am

Order bY Contno, EntryDate ??

akhter86 21-Feb-22 4:00am

Order by Entrydate

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2022-02-20T08:44:00

Sorry, but your SQL code is very hard to understand...

I also do not understand why the desired result contains this record: ABC1222 1/30/2022
You have defined @StartDate date ='2022-02-01', which should eliminate this record from output.

If i understand you well, ty this:

SQL

Declare @StartDate date='2022-02-01'
Declare @Enddate date ='2022-02-15'

SELECT C.CID, C.ContNo, C.entrydate AS IN_Date, 
  -- CAST(C.ConWeight AS DECIMAL(10,4)) AS ConWeight,
  -- CAST(COALESCE(I1.IWeight, 0) AS DECIMAL(10,2)) AS IWeight, 
  CAST(C.ConWeight AS DECIMAL(10,4)) - 
    CAST(COALESCE(I1.IWeight, 0) AS DECIMAL(10,2)) AS Opening_Weight,
  CAST(COALESCE(I2.IsuWeight, 0) AS DECIMAL(10,2)) AS IsuWeight,
  CAST(C.ConWeight AS DECIMAL(10,4)) - 
    CAST(COALESCE(I1.IWeight, 0) AS DECIMAL(10,2)) -
    CAST(COALESCE(I2.IsuWeight, 0) AS DECIMAL(10,2)) AS Closing_Weight
FROM Containerno C 
LEFT JOIN
(
  SELECT CID, SUM(IWeight) AS IWeight
  FROM ConIssuance
  WHERE EntryDate<@StartDate and DelID is null
  GROUP BY CID
) AS I1 ON C.CID = I1.CID
LEFT JOIN 
(
  SELECT CID, SUM(IWeight) AS IsuWeight
  FROM ConIssuance
  WHERE EntryDate BETWEEN @StartDate and @EndDate and DelID is null
  GROUP BY CID
) AS I2 ON C.CID = I2.CID
WHERE C.DelID is null AND C.entrydate BETWEEN @StartDate AND @Enddate

Here is an online demo: SQL Server 2019 | db<>fiddle[^]

Good luck!

Opening and closing query need contno wise

1 solution

Solution 1

Add your solution here

Preview 0

Opening and closing query need contno wise

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us