Opening and closing query

Question

I have data below

Create table #tbl_issuance  (Issue_ID int,Issue_Weight int,Issue_date date ,Issue_Del int)
Create table #tbl_Bale_Prd  (Bale_ID int,Bale_Weight int,Bale_date date ,bale_Del int)


Insert into #tbl_issuance values(1001,5000,'2023-06-15',null)
Insert into #tbl_issuance values(1002,15310,'2023-06-16',null)
Insert into #tbl_issuance values(1003,50,'2023-06-17',null)

Insert into #tbl_Bale_Prd values(1111,210,'2023-06-16',null)
Insert into #tbl_Bale_Prd values(1112,1710,'2023-06-17',null)
Insert into #tbl_Bale_Prd values(1113,5750,'2023-06-19',null)

For Column detail for below query

Bale_Weight = pWeight

Issue_Weight = Iweight

Opening = Carry Forward closing for next day

Floor =IWeight + Opening

Closing = Floor -pWeight

;with cte as (
 SELECT a.EntryDate, a.pWeight,  isnull(c.iWeight,0)iWeight,   (isnull(iWeight,0) - isnull(pWeight,0) ) Closing1
 ,ROW_NUMBER() OVER (ORDER BY a.EntryDate asc) RN 
   FROM ( SELECT SUM(isnull(P.Bale_Weight,0)) pWeight, P.Bale_Date  EntryDate
   FROM #tbl_Bale_Prd P 
   where 
   P.bale_del is null
    GROUP BY P.Bale_Date    
    )  a
 left JOIN ( SELECT SUM(isnull(I.Issue_Weight,0)) iWeight, I.Issue_Date as Entrydate 
  FROM #tbl_issuance  i
  where I.Issue_del is null   
  GROUP BY I.Issue_Date) c ON a.EntryDate = c.EntryDate)
  ,wte1 as (
   select * from cte a
     CROSS APPLY ( SELECT 0+sum( Closing1) Closing FROM cte b WHERE b.EntryDate <= a.EntryDate) x)
 ,wte2 AS
 (
 select a.EntryDate, a.pWeight, a.iWeight, a.Closing
 ,case when b.Closing is null then 0 else b.Closing end opening 
 from wte1 a
 left join wte1 b 
 on a.RN = b.RN + 1
 )
 ,
 wte3 AS
 (
 select EntryDate, pWeight, iWeight, Opening+iWeight [Floor] , Closing
 , Opening FROM wte2
 )
                    
 SELECT * FROM wte3
 order by EntryDate desc

Expected Output

Answer 1

Hi @akhter hussain

How about this:

Copy

DECLARE @Startdate date = CAST('20230615' as datetime)
DECLARE @Enddate date = CAST('20230619' as datetime)

;WITH DateTable AS
(
 SELECT @Startdate AS [date]
 UNION ALL
 SELECT DATEADD(DAY, 1, [date])
 FROM DateTable
 WHERE DATEADD(DAY, 1, [date]) <= @Enddate
),CTE1 AS
(
 SELECT date AS EntryDate,ISNULL(pWeight,0)AS pWeight,ISNULL(iWeight,0) AS iWeight
 FROM DateTable D 
 LEFT JOIN (SELECT Issue_date,SUM(ISNULL(Issue_Weight,0))AS iWeight FROM #tbl_issuance GROUP BY Issue_date)I ON D.date=I.Issue_date
 LEFT JOIN (SELECT Bale_date,SUM(ISNULL(Bale_Weight,0))AS pWeight FROM #tbl_Bale_Prd GROUP BY Bale_date)B ON D.date=B.Bale_date
),CTE2 AS
(
 SELECT EntryDate,pWeight,iWeight,iWeight + 0 AS [Floor],iWeight - pWeight AS Closing,0 AS Opening
 FROM CTE1 
 WHERE EntryDate = @Startdate
 UNION ALL
 SELECT C1.EntryDate,C1.pWeight,C1.iWeight,C1.iWeight + C2.Closing,C1.iWeight + C2.Closing - C1.pWeight,C2.Closing
 FROM CTE2 C2 JOIN CTE1 C1 ON DATEADD(DAY,1,C2.EntryDate) = C1.EntryDate
)
SELECT * FROM CTE2
ORDER BY EntryDate DESC

Answer 2

Hi @Analyst_SQL

You have order by EntryDate desc in your query, however your expected output is not that order. Please check that.

Try this query:

DECLARE @Startdate date = CAST('20230615' as datetime)
DECLARE @Enddate date = CAST('20230619' as datetime)

;WITH DateTable AS
(
 SELECT @Startdate AS [date]
 UNION ALL
 SELECT DATEADD(DAY, 1, [date])
 FROM DateTable
 WHERE DATEADD(DAY, 1, [date]) <= @Enddate
),CTE1 AS
(
 SELECT date AS EntryDate,ISNULL(Bale_Weight,0) AS pWeight,ISNULL(Issue_Weight,0) AS iWeight
 FROM DateTable D LEFT JOIN #tbl_issuance I ON D.date=I.Issue_date
                  LEFT JOIN #tbl_Bale_Prd B ON D.date=B.Bale_date
),CTE2 AS
(
 SELECT EntryDate,pWeight,iWeight,iWeight + 0 AS [Floor],iWeight - pWeight AS Closing,0 AS Opening
 FROM CTE1 
 WHERE EntryDate = @Startdate
 UNION ALL
 SELECT C1.EntryDate,C1.pWeight,C1.iWeight,C1.iWeight + C2.Closing,C1.iWeight + C2.Closing - C1.pWeight,C2.Closing
 FROM CTE2 C2 JOIN CTE1 C1 ON DATEADD(DAY,1,C2.EntryDate) = C1.EntryDate
)
SELECT * FROM CTE2
ORDER BY EntryDate DESC

Output:

Best regards,

Cosmog Hong