Stock Movement query required.

Question

Stock Movement query required.

Analyst_SQL 3,576

Below is data

CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50))    
         
 CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Entrydate DATETIME,PID int)    
   
 CREATE TABLE #tbl_BalPacM(PID INT, Orderno int,Entrydate DATETIME,ID int)    
 CREATE TABLE #tbl_PckDetail(DID INT,PID int,BID int,Codeitem int,Pack_Qty int)    
  
 Create TABLE #tbl_SBDispatchM(ID int,Entydate DATETIME)  
 Create TABLE #tbl_SBDispatachD (DID int,ID int ,PID int)  
                
 INSERT INTO #ItemMasterFile VALUES(1,'A')    
 INSERT INTO #ItemMasterFile VALUES(2,'B')    
 INSERT INTO #ItemMasterFile VALUES(3,'C')    
 INSERT INTO #ItemMasterFile VALUES(4,'D')    
 INSERT INTO #ItemMasterFile VALUES(5,'e')    
 INSERT INTO #ItemMasterFile VALUES(6,'f')    
 INSERT INTO #ItemMasterFile VALUES(7,'g')    
 INSERT INTO #ItemMasterFile VALUES(8,'h')    
 INSERT INTO #ItemMasterFile VALUES(9,'K')    
 INSERT INTO #ItemMasterFile VALUES(10,'L')    
 INSERT INTO #ItemMasterFile VALUES(11,'M')    
   
   
          
 INSERT INTO #Probale VALUES(10007,1,1,'02-07-2022',null)    
 INSERT INTO #Probale VALUES(10008,3,1,'02-07-2022',null)         
 INSERT INTO #Probale VALUES(10009,1,1,'05-07-2022',null)    
 INSERT INTO #Probale VALUES(10010,3,1,'05-07-2022',null)         
 INSERT INTO #Probale VALUES(10011,1,1,'01-08-2022',1001)    
 INSERT INTO #Probale VALUES(10012,3,1,'01-08-2022',1001)    
 INSERT INTO #Probale VALUES(10013,11,1,'01-08-2022',1001)    
 INSERT INTO #Probale VALUES(10014,10,1,'01-08-2022',1001)    
 INSERT INTO #Probale VALUES(10015,8,1,'01-08-2022',null)    
 INSERT INTO #Probale VALUES(10016,9,1,'01-08-2022',null)    
 INSERT INTO #Probale VALUES(10017,9,1,'01-08-2022',null)    
 INSERT INTO #Probale VALUES(10018,1,1,'02-08-2022',1002)    
 INSERT INTO #Probale VALUES(10019,3,1,'02-08-2022',1002)    
 INSERT INTO #Probale VALUES(10020,11,1,'02-08-2022',1002)    
 INSERT INTO #Probale VALUES(10021,10,1,'02-08-2022',1002)    
      
 INSERT INTO #tbl_BalPacM VALUES(1001,111,'01-08-2022',1)    
            
 INSERT INTO #tbl_PckDetail VALUES(1,1001,10011,1,1)    
 INSERT INTO #tbl_PckDetail VALUES(2,1001,10012,3,1)    
 INSERT INTO #tbl_PckDetail VALUES(3,1001,10013,11,1)    
 INSERT INTO #tbl_PckDetail VALUES(4,1001,10014,10,1)    
  
 INSERT INTO #tbl_BalPacM VALUES(1002,111,'02-08-2022',null)    
            
 INSERT INTO #tbl_PckDetail VALUES(1,1002,10018,1,1)    
 INSERT INTO #tbl_PckDetail VALUES(2,1002,10019,3,1)    
 INSERT INTO #tbl_PckDetail VALUES(3,1002,10020,11,1)    
 INSERT INTO #tbl_PckDetail VALUES(4,1002,10021,10,1)    
  
 INSERT INTO #tbl_SBDispatchM VALUES(1 ,'01-08-2022')  
   
 INSERT INTO #tbl_SBDispatachD VALUES (11 ,1,1001)

1)All qty is getting sum from #Probale table.
2)Making Bundle(Pack) in #tbl_BalPacM(Master table) and #tbl_PckDetail(Detail table) table.
when i am inserting 10011 record id into #tbl_PckDetail then relevent Column PID getting inserted into #Probale table.
3)Dispatching/Stock outing from #tbl_SBDispatchM (Master table) and #tbl_SBDispatachD (Detail table)

Report Detail
i am giving date parameter from 01-08-2022 to 02-08-2022.
Opening QTY = From #Probale before starting date (01-08-2022).
IN QTY = From #Probale date between 01-08-2022 to 02-08-2022.
OUT QTY= From #tbl_SBDispatchM Entydate between 01-08-2022 to 02-08-2022 and Col ID exists in #tbl_BalPacM table(as you can see 1001)
Closing qty = (Opening + IN) - Out

Tom Phillips 17,771 Reputation points

2022-08-11T16:47:26.517+00:00

Your sample data does not match your output.

There are no records in #Probale before starting date (01-08-2022), so there cannot be any values in Opening Qty.

Analyst_SQL 3,576

check below line before 01-08-2022

 INSERT INTO #Probale VALUES(10007,1,1,'02-07-2022',null)    
  INSERT INTO #Probale VALUES(10008,3,1,'02-07-2022',null)         
  INSERT INTO #Probale VALUES(10009,1,1,'05-07-2022',null)    
  INSERT INTO #Probale VALUES(10010,3,1,'05-07-2022',null)

Tom Phillips 17,771 Reputation points

2022-08-11T18:51:41.62+00:00

Your code is ambiguous.

Apparently you are expecting "02-07-2022" to represent July 2 2022. Most people on this forum are in US and will not interpret your dates that way. You should always put date strings as "YYYY-MM-DD" to remove the ambiguity.
Analyst_SQL 3,576 Reputation points

2022-08-12T06:10:56.943+00:00

ok ,next time will care

3 answers

Your answer

Tom Phillips 17,771 Reputation points

2022-08-11T16:47:26.517+00:00

Your sample data does not match your output.

There are no records in #Probale before starting date (01-08-2022), so there cannot be any values in Opening Qty.
Analyst_SQL 3,576 Reputation points

2022-08-11T18:07:51.377+00:00

check below line before 01-08-2022

INSERT INTO #Probale VALUES(10007,1,1,'02-07-2022',null) INSERT INTO #Probale VALUES(10008,3,1,'02-07-2022',null) INSERT INTO #Probale VALUES(10009,1,1,'05-07-2022',null) INSERT INTO #Probale VALUES(10010,3,1,'05-07-2022',null)
Tom Phillips 17,771 Reputation points

2022-08-11T18:51:41.62+00:00

Your code is ambiguous.

Apparently you are expecting "02-07-2022" to represent July 2 2022. Most people on this forum are in US and will not interpret your dates that way. You should always put date strings as "YYYY-MM-DD" to remove the ambiguity.
Analyst_SQL 3,576 Reputation points

2022-08-12T06:10:56.943+00:00

ok ,next time will care

Answer 1

This should work on your side.

select im.Descriptionitem,
isnull(OAOpening.OpenQty,0) as OpeningQty,
isnull(prdqty ,0) as InQty ,
isnull(OA.Pack_Qty,0) as OutQty ,
(isnull(OAOpening.OpenQty,0) + isnull(prdqty ,0) ) -isnull(OA.Pack_Qty,0) as ClosingQty
from #ItemMasterFile IM
left outer join #Probale PB on im.CodeItem= pb.codeitem and pb.entrydate = '2022-01-08'
outer apply ( select sum(isnull(PBIN.prdQty,0)) as OPENQty
from #Probale PBIN
where PBIN.CodeItem = IM.CodeItem
and PBIN.EntryDate < PB.EntryDate
) OAOpening
outer apply ( select BPMD.Pack_qty
from #tbl_SBDispatchM SBD
inner join #tbl_BalPacM BPM on sbd.ID = BPM.ID
left outer join #tbl_PckDetail BPMD on BPM.PID = BPMD.PID
where BPMD.CodeItem = IM.CodeItem
and SBD.EntyDate between pb.entrydate and dateadd(day,1,pb.entrydate) -- adding 1 day
) OA

Analyst_SQL 3,576 Reputation points

2022-08-12T06:05:33.657+00:00

it is not giving me, my expected output

Answer 2

It is easiest if you break down your items into sections.

Try this:

DROP TABLE IF EXISTS #ItemMasterFile;  
DROP TABLE IF EXISTS #Probale;  
DROP TABLE IF EXISTS #tbl_BalPacM;  
DROP TABLE IF EXISTS #tbl_PckDetail;  
DROP TABLE IF EXISTS #tbl_SBDispatchM;  
DROP TABLE IF EXISTS #tbl_SBDispatachD;  
GO  
  
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50))    
             
  CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Entrydate DATETIME,PID int)    
       
  CREATE TABLE #tbl_BalPacM(PID INT, Orderno int,Entrydate DATETIME,ID int)    
  CREATE TABLE #tbl_PckDetail(DID INT,PID int,BID int,Codeitem int,Pack_Qty int)    
      
  Create TABLE #tbl_SBDispatchM(ID int,Entrydate DATETIME)  
  Create TABLE #tbl_SBDispatachD (DID int,ID int ,PID int)  
                    
  INSERT INTO #ItemMasterFile VALUES(1,'A')    
  INSERT INTO #ItemMasterFile VALUES(2,'B')    
  INSERT INTO #ItemMasterFile VALUES(3,'C')    
  INSERT INTO #ItemMasterFile VALUES(4,'D')    
  INSERT INTO #ItemMasterFile VALUES(5,'e')    
  INSERT INTO #ItemMasterFile VALUES(6,'f')    
  INSERT INTO #ItemMasterFile VALUES(7,'g')    
  INSERT INTO #ItemMasterFile VALUES(8,'h')    
  INSERT INTO #ItemMasterFile VALUES(9,'K')    
  INSERT INTO #ItemMasterFile VALUES(10,'L')    
  INSERT INTO #ItemMasterFile VALUES(11,'M')    
       
       
              
  INSERT INTO #Probale VALUES(10007,1,1,'2022-07-02',null)    
  INSERT INTO #Probale VALUES(10008,3,1,'2022-07-02',null)         
  INSERT INTO #Probale VALUES(10009,1,1,'2022-07-05',null)    
  INSERT INTO #Probale VALUES(10010,3,1,'2022-07-05',null)         
  INSERT INTO #Probale VALUES(10011,1,1,'2022-08-01',1001)    
  INSERT INTO #Probale VALUES(10012,3,1,'2022-08-01',1001)    
  INSERT INTO #Probale VALUES(10013,11,1,'2022-08-01',1001)    
  INSERT INTO #Probale VALUES(10014,10,1,'2022-08-01',1001)    
  INSERT INTO #Probale VALUES(10015,8,1,'2022-08-01',null)    
  INSERT INTO #Probale VALUES(10016,9,1,'2022-08-01',null)    
  INSERT INTO #Probale VALUES(10017,9,1,'2022-08-01',null)    
  INSERT INTO #Probale VALUES(10018,1,1,'2022-08-02',1002)    
  INSERT INTO #Probale VALUES(10019,3,1,'2022-08-02',1002)    
  INSERT INTO #Probale VALUES(10020,11,1,'2022-08-02',1002)    
  INSERT INTO #Probale VALUES(10021,10,1,'2022-08-02',1002)    
          
  INSERT INTO #tbl_BalPacM VALUES(1001,111,'2022-08-01',1)    
                
  INSERT INTO #tbl_PckDetail VALUES(1,1001,10011,1,1)    
  INSERT INTO #tbl_PckDetail VALUES(2,1001,10012,3,1)    
  INSERT INTO #tbl_PckDetail VALUES(3,1001,10013,11,1)    
  INSERT INTO #tbl_PckDetail VALUES(4,1001,10014,10,1)    
      
  INSERT INTO #tbl_BalPacM VALUES(1002,111,'2022-08-02',null)    
                
  INSERT INTO #tbl_PckDetail VALUES(1,1002,10018,1,1)    
  INSERT INTO #tbl_PckDetail VALUES(2,1002,10019,3,1)    
  INSERT INTO #tbl_PckDetail VALUES(3,1002,10020,11,1)    
  INSERT INTO #tbl_PckDetail VALUES(4,1002,10021,10,1)    
      
  INSERT INTO #tbl_SBDispatchM VALUES(1 ,'2022-08-01')  
       
  INSERT INTO #tbl_SBDispatachD VALUES (11 ,1,1001)  
  
  
  DECLARE @startdate date, @enddate date;  
  SET @startdate = '2022-08-01'  
  SET @enddate = '2022-08-02'  
  
  
;with OpeningQty as (  
	select CodeItem,   
		SUM(prdQty) as OpeningQty  
	from #Probale  
	where EntryDate < @startdate  
	GROUP BY CodeItem  
),  
InQty as (  
	select CodeItem,   
		SUM(prdQty) as InQty  
	from #Probale  
	where EntryDate BETWEEN @startdate and @enddate  
	GROUP BY CodeItem  
),  
OutQty as (  
	SELECT pd.CodeItem,  
		SUM(Pack_Qty) as OutQty  
	FROM #tbl_SBDispatchM dm  
		INNER JOIN #tbl_SBDispatachD dd  
		ON dd.ID = dm.ID  
		INNER JOIN #tbl_BalPacM b  
		ON b.PID = dd.PID  
		INNER JOIN #tbl_PckDetail pd  
		ON pd.PID = dd.PID  
	WHERE dm.EntryDate BETWEEN @startdate and @enddate  
	GROUP BY pd.CodeItem  
),  
final as (  
SELECT m.Descriptionitem as [Item],  
	ISNULL(o.OpeningQty,0) as [Opening QTY],  
	ISNULL(i.InQty,0) AS [IN QTY],  
	ISNULL(ot.[OutQty],0) AS [OUT QTY]  
FROM #ItemMasterFile m  
	LEFT OUTER JOIN OpeningQty o  
		ON o.CodeItem = m.CodeItem  
	LEFT OUTER JOIN InQty i  
		ON i.CodeItem = m.CodeItem  
	LEFT OUTER JOIN OutQty ot  
		ON ot.CodeItem = m.CodeItem  
)  
SELECT *,  
	ISNULL(([Opening QTY]+[IN QTY])-[OUT QTY],0) as [Closing QTY]  
FROM final  
ORDER BY [Item]

Analyst_SQL 3,576 Reputation points

2022-08-13T06:15:52.437+00:00

your provided query is not subtracting/reducing qty in opening ,if any out_qty before date 2022-08-01.
i did not provide out_qty dispatch in temp data.

;with OpeningQty as ( select CodeItem, SUM(prdQty) as OpeningQty from #Probale where EntryDate < @startdate GROUP BY CodeItem ),

Answer 3

Isabellaz-1451 3,616

Hi @Analyst_SQL

From your description ,I think you need to calculate the Closing QTY before 2022-08-01 first, and it is the OpeningQty from 2022-08-01

Best Regards,
Isabella

Analyst_SQL 3,576 Reputation points

2022-08-18T07:25:34.513+00:00

can you provide me code? @Isabellaz-1451

Share via

Stock Movement query required.

3 answers

Your answer