| Author |
 Topic  |
|
|
adbasanta
Posting Yak Master
108 Posts |
 Posted - 01/03/2013 : 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 |
Edited by - adbasanta on 01/03/2013 19:50:11
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/04/2013 : 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
108 Posts |
Posted - 01/04/2013 : 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 |
Edited by - adbasanta on 01/04/2013 03:13:21 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/04/2013 : 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
108 Posts |
Posted - 01/04/2013 : 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 |
Edited by - adbasanta on 01/04/2013 19:33:34 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/04/2013 : 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
India
47023 Posts |
Posted - 01/04/2013 : 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
108 Posts |
Posted - 01/05/2013 : 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 |
Edited by - adbasanta on 01/05/2013 20:53:01 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/06/2013 : 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
108 Posts |
Posted - 01/06/2013 : 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 |
Edited by - adbasanta on 01/07/2013 00:09:58 |
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 01/07/2013 : 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 |
Edited by - adbasanta on 01/07/2013 09:48:41 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/07/2013 : 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
108 Posts |
Posted - 01/10/2013 : 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
India
47023 Posts |
Posted - 01/10/2013 : 05:54:01
|
no probs
Glad that you sorted it out
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Running Inventories with date backtrack..?
