Running Inventories with date backtrack..? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Running Inventories with date backtrack..?

Author

Topic

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-01-03 : 19:36:42

Good Day!

I have this running /stored proc query that returns the list of all items.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Show_all_items]
	-- Add the parameters for the stored procedure here
@fromdate as Varchar(50), 
@todate as Varchar(50), 
@itemcode as bigint

As
Begin

	SELECT * FROM tbl_product_list p 
		LEFT JOIN  tbl_supplier s
		ON  s.suppliername=(SELECT s.suppliername
		WHERE s.suppliercode=p.supplierid)
			
		LEFT JOIN  tbl_prod_category c
		ON  c.categoryname=(SELECT c.categoryname
		WHERE c.catgoryid=p.category)
	
	ORDER BY p.itemname ASC
End

I want to add add colums to the result as follows which will display the quantity base on the date given by the user(fromdate & to date):


COLUMN NAME	DESCRIPTION		      TABLE SOURCE (NAME/FIELD)
BeginInventory	Holds the Beginning Inventory tbl_Inventory_Actual(RecID,InventoryDate,ProductCode,ItemName,Quantity,Total_Amount)
Purchases	Holds all the Purchased Items PO_detail(porefno,podate,supplierid,prodcode,itemname,qty,total)
TransferIn	All Items transferred IN      tbl_product_transfer_summary(transref,transdate,prodcode,proddesc,qty,transferMemo(IN/OUT),total	
TransferOut	All Items transferred OUT     tbl_product_transfer_summary(transref,transdate,prodcode,proddesc,qty,transferMemo(IN/OUT),total			
Sold		All Items Sold                tbl_pos_sales_detail(transdate,productcode,productdesc,qty,net)

The last column to be added is, RunningOnhand where

RunningOnhand = BeginInventory + Purchases + TransferIn - TransferOut - Sold

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-04 : 02:40:33

how are these tables related to tbl_prod_category and tbl_supplier?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-01-04 : 03:12:23

thank you visakh16!

tbl_prod_category and tbl_supplier is only related to tbl_product_list because it only supplies the supplier name base on supplierID and product category base on categoryID from tbl_product_list. The rest is related to tbl_product_list only which are identified via prodcode of each table from tbl_product_list!

The sample result must be:


prodcode            itemname   BeginInventory   Purchases  TransferIn  TransferOut  Sold  RunningOnhand
0114583500        dairy cream      200            200        50            20      20         410
0526878566        cream milk       100            500        600           200     300        700

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-04 : 03:25:19

sound like this then


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Show_all_items]
	-- Add the parameters for the stored procedure here
@fromdate as Varchar(50), 
@todate as Varchar(50), 
@itemcode as bigint

As
Begin

	SELECT p.productcode,
               itemname,
               BeginInventory,
               Purchases,
               TransfersIn,
               TransfersOut,
               Sold,
               BeginInventory +  Purchases + TransfersIn + TransfersOut + Sold AS RunningOnhand
        FROM tbl_product_list p 
         LEFT JOIN (SELECT ProductCode,SUM(Quantity) AS BeginInventory
                    FROM tbl_Inventory_Actual
                    GROUP BY ProductCode)i
          ON i.ProductCode = p.ProductCode
         LEFT JOIN (SELECT prodcode,SUM(qty) AS Purchases
                    FROM PO_detail
                    GROUP BY prodcode)po
          ON po.prodcode= p.ProductCode
         LEFT JOIN (SELECT prodcode,
                           SUM(CASE WHEN [transferMemo(IN/OUT)] = 'IN' THEN qty END) AS TransfersIn,
                           SUM(CASE WHEN [transferMemo(IN/OUT)] = 'OUT' THEN qty END) AS TransfersOut
                    FROM tbl_product_transfer_summary
                    GROUP BY prodcode)pts
          ON pts.prodcode= p.ProductCode
         LEFT JOIN (SELECT productcode,SUM(qty) AS Sold
                    FROM tbl_pos_sales_detail
                    GROUP BY productcode)psd
          ON psd.prodcode= p.ProductCode
	LEFT JOIN  tbl_supplier s
		ON  s.suppliername=(SELECT s.suppliername
		WHERE s.suppliercode=p.supplierid)
			
		LEFT JOIN  tbl_prod_category c
		ON  c.categoryname=(SELECT c.categoryname
		WHERE c.catgoryid=p.category)
	
	ORDER BY p.itemname ASC
End

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-01-04 : 19:30:41

thank you visakh16!

It is working however the RunningOnhand did not return any value? I have made a little modification on the code below. Changes/modifications are marked in red:

 
USE MFR_Merchandise
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Show_all_items_DateView]
	-- Add the parameters for the stored procedure here
	
@fromdate as Varchar(50), 
@todate as Varchar(50)

As
Begin

    SELECT p.productcode,
           itemname,
           BeginInventory,
           Purchases,
           TransfersIn,
           TransfersOut,
           SoldItem,
           BeginInventory + Purchases + TransfersIn - TransfersOut - Sold AS RunningOnhand
    FROM tbl_product_list p 
         
         LEFT JOIN (SELECT ProductCode,SUM(qty) AS BeginInventory
                    FROM tbl_Inventory_Actual
                    WHERE (CONVERT(DATE,InventoryDate)) BETWEEN @fromdate AND @todate  
                    GROUP BY ProductCode)i
         ON i.ProductCode = p.ProductCode
         
         LEFT JOIN (SELECT prodcode,SUM(qty) AS Purchases
                    FROM PO_detail
                    WHERE (CONVERT(DATE,podate)) BETWEEN @fromdate AND @todate
                    GROUP BY prodcode)po
         ON po.prodcode= p.ProductCode
         
         LEFT JOIN (SELECT prodcode,
                           SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn,
                           SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut
                    FROM tbl_product_transfer_detail
                    WHERE (CONVERT(DATE,transdate)) BETWEEN @fromdate AND @todate  
                    GROUP BY prodcode)pts
         ON pts.prodcode= p.ProductCode
         
         LEFT JOIN (SELECT productcode,SUM(qty) AS SoldItem
                    FROM tbl_pos_sales_detail
                    WHERE (CONVERT(DATE,transdate)) BETWEEN @fromdate AND @todate
                    GROUP BY productcode)psd
         ON psd.productcode= p.ProductCode
         
	LEFT JOIN  tbl_supplier s
		ON  s.suppliername=(SELECT s.suppliername
		WHERE s.suppliercode=p.supplierid)
			
		LEFT JOIN  tbl_prod_category c
		ON  c.categoryname=(SELECT c.categoryname
		WHERE c.catgoryid=p.category)
	
	ORDER BY p.itemname ASC
End

Here is the result. The RunningOnhand on the first row must be -24 as there is no BeginInventory, Purchases, TransfersIn, TransfersOut value. The same with the second row, the value of RunningOnhand must be -67.


productcode	itemname	   BeginInventory	 Purchases	TransfersIn	TransfersOut	Sold    RunningOnhand	
4800047841743	zonrox plus 450ml	NULL		   NULL		    NULL	    NULL	 24          NULL
4800047843334	zonrox prmo pck 	NULL		   NULL		    NULL	    NULL	 67	     NULL

I suspect its the formula of RunningOnhand but I cant figure out which part of it is missing.

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-04 : 20:16:10

try

COALESCE(BeginInventory,0) + COALESCE(Purchases,0) + COALESCE(TransfersIn,0) - COALESCE(TransfersOut,0) - COALESCE(Sold,0) AS RunningOnhand

instead of

BeginInventory + Purchases + TransfersIn - TransfersOut - Sold AS RunningOnhand

to handle NULL values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-04 : 20:20:26

also couple of other observatios

make @fromdate,@todate etc as datetime or date datatype as they contain date values

otherwise issues can cause due to wrong formats being passed

also make filter like

WHERE InventoryDate >= @fromdate AND InventoryDate < @todate +1

to make predicate sargeable

see
http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-01-05 : 20:45:31

thank you visakh16!

It is working however some values in RunningOnhand are not accurate while some are also accurate? Pls. see some results below for not accurate RunningOnhand. Ive also marked red those changes Ive made.


USE MFR_Merchandise
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Show_all_items_DateView]
	-- Add the parameters for the stored procedure here
	
@fromdate as DATETIME, 
@todate as DATETIME

As
Begin

    SELECT p.productcode,
           itemname,
           BeginInventory,
           Purchases,
           TransfersIn,
           TransfersOut,
           SoldItem,
           COALESCE(BeginInventory,0) + COALESCE(Purchases,0) + COALESCE(TransfersIn,0) - COALESCE(TransfersOut,0) - COALESCE(Sold,0) AS RunningOnhand
    FROM tbl_product_list p 
         
         LEFT JOIN (SELECT ProductCode,SUM(qty) AS BeginInventory
                    FROM tbl_Inventory_Actual
                    WHERE InventoryDate >= @fromdate AND InventoryDate < @todate + 1
                    GROUP BY ProductCode)i
         ON i.ProductCode = p.ProductCode
         
         LEFT JOIN (SELECT prodcode,SUM(qty) AS Purchases
                    FROM PO_detail
                    WHERE podate >= @fromdate AND podate < @todate + 1
                    GROUP BY prodcode)po
         ON po.prodcode= p.ProductCode
         
         LEFT JOIN (SELECT prodcode,
                           SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn,
                           SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut
                    FROM tbl_product_transfer_detail
                    WHERE transdate >= @fromdate AND transdate < @todate + 1  
                    GROUP BY prodcode)pts
         ON pts.prodcode= p.ProductCode
         
         LEFT JOIN (SELECT productcode,SUM(qty) AS SoldItem
                    FROM tbl_pos_sales_detail
                    WHERE transdate >= @fromdate AND transdate < @todate + 1
                    GROUP BY productcode)psd
         ON psd.productcode= p.ProductCode
         
	LEFT JOIN  tbl_supplier s
		ON  s.suppliername=(SELECT s.suppliername
		WHERE s.suppliercode=p.supplierid)
			
		LEFT JOIN  tbl_prod_category c
		ON  c.categoryname=(SELECT c.categoryname
		WHERE c.catgoryid=p.category)
	
	ORDER BY p.itemname ASC
End

Query result:


productcode	itemname	   BeginInventory	 Purchases	TransfersIn	TransfersOut	Sold    RunningOnhand	
4800047841743	zonrox plus 450ml	NULL		   NULL		    NULL	    NULL	 3          -1
4800047843334	zonrox prmo pck 	NULL		   NULL		    NULL	    NULL	 2	    -1
4800047843335	zonrox prmo 50ml 	NULL		   NULL		    NULL	    NULL	 1	     0

First Row value for RunningOnhand should be -3, row 2 should be -2 and row 3 should be -1. Ive also made changes to data type for date fields to datetime.

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-06 : 09:53:27

the logic looks fine to me. The only question would be whether BeginInventory,Purchases etc have expected values in those rows?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-01-06 : 17:39:30

Thank you visakh16!

Yes, BeginInventory,Purchases will have values when the user inputs the values for it. But there are times also that the values for these fields will be posted lately. So in these scenario if you look at the Running Inventory, only the Sold items are expected to have values, and the RunningOnhand should adopt those values too which should be negative as there is no Inventory yet. I also believe that the logic for the query is correct. But why the running RunningOnhand is missing some values?

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-01-07 : 09:48:07

I tried this solution too but still no luck. Modifications in red.


USE [MFR_Merchandise]
GO
/****** Object:  StoredProcedure [dbo].[Show_all_items_DateView]    Script Date: 01/04/2013 22:58:26 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Show_all_items_DateView]
	-- Add the parameters for the stored procedure here
	
@fromdate as DATETIME,
@todate as DATETIME

As
Begin

	SELECT p.productcode,
               itemname,
               description,
               packtype,
               itemtype,
               c.categoryname,
               s.suppliername,
               supplierprice,
               srp,
               discount,
               service_other_charge_amount_percent,
               BeginInventory,
               Purchases,
               TransfersIn,
               TransfersOut,
               SoldItem,
               COALESCE(BeginInventory,0) + COALESCE(Purchases,0) + COALESCE(TransfersIn,0) - COALESCE(TransfersOut,0) - COALESCE(Sold,0) AS RunningOnhand
                  
    FROM tbl_product_list p 
         
         LEFT JOIN  (SELECT ProductCode,COALESCE(SUM(qty),0) AS BeginInventory
                    FROM tbl_Inventory_Actual
                    WHERE InventoryDate >= @fromdate AND InventoryDate < @todate + 1
                    GROUP BY ProductCode)i
         ON i.ProductCode = p.productcode
         
         LEFT JOIN (SELECT prodcode,COALESCE(SUM(qty),0) AS Purchases
                    FROM PO_detail
                    WHERE podate >= @fromdate AND podate < @todate + 1
                    GROUP BY prodcode)po
         ON po.prodcode= p.productcode
         
         LEFT JOIN (SELECT prodcode,
                           COALESCE(SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END),0) AS TransfersIn,
                           COALESCE(SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END),0) AS TransfersOut
                    FROM tbl_product_transfer_detail
                    WHERE transdate >= @fromdate AND transdate < @todate + 1
                    GROUP BY prodcode)pts
         ON pts.prodcode= p.productcode
         
         LEFT JOIN (SELECT productcode,COALESCE(SUM(qty),0) AS SoldItem
                    FROM tbl_pos_sales_detail
                    WHERE transdate >= @fromdate AND transdate < @todate + 1
                    GROUP BY productcode)psd
         ON psd.productcode= p.productcode
         
	LEFT JOIN  tbl_supplier s
		ON  s.suppliername=(SELECT s.suppliername
		WHERE s.suppliercode=p.supplierid)
			
		LEFT JOIN  tbl_prod_category c
		ON  c.categoryname=(SELECT c.categoryname
		WHERE c.catgoryid=p.category)
	
	ORDER BY p.itemname ASC
End

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-07 : 10:43:50

i cant guess whats the issue as I dont know what those subqueries are doing. suggest you check them first to make sure you're getting values for other fields correctly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-01-10 : 05:51:30

thank you visakh16!

Ive managed to run the accurate RunningOnhand value,but instead of creating the formulas in storedproc, I used to make it in my front end app

.

thanks again!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 05:54:01

no probs
Glad that you sorted it out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources