Click here to Skip to main content
15,881,852 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
ALTER PROCEDURE [dbo].[rptrm_pm_stck_summary]
@frmdate date,
@todate date
AS
BEGIN

select item_code,(select item_desc from tblItemMaster where cs.item_code=item_code)as item_desc,(select UOM from tblItemMaster where cs.item_code=item_code)as UOM,batch_no,
opening_qty
,(select SUM(receipt_qty) from tblGRNEntry where GRN_Date between @frmdate and @todate and cs.item_code=item_code and cs.batch_no=batch_no) as receipt_qty,(select SUM(issue_qty) from tblMINDateEntry where MIN_Date between @frmdate and @todate and cs.item_code=item_code and cs.batch_no=batch_no) as issue_qty,
(select SUM(Qty) from tblStockAdjustment where Ref_Date between @frmdate and @todate and cs.item_code=item_code and cs.batch_no=batch_no) as ads_qty

--,(opening_qty+(select SUM(receipt_qty) from tblGRNEntry where GRN_Date between @frmdate and @todate and cs.item_code=item_code and cs.batch_no=batch_no)-(select SUM(issue_qty) from tblMINDateEntry where MIN_Date between @frmdate and @todate and cs.item_code=item_code and cs.batch_no=batch_no)+(select SUM(Qty) from tblStockAdjustment where Ref_Date between @frmdate and @todate and cs.item_code=item_code and cs.batch_no=batch_no))  


 from tblClosingStock cs
where
adddate between @frmdate and @todate
--select * from tblClosingStock cs
-- [rptrm_pm_stck_summary] '2011-1-1','2012-12-1'
END


but i want to use new column alias column names (opening_qty+receipt_qty-issue_qty+ads_qty) as closing_qty

and how to add null value if exist
Posted
Comments
choudhary.sumit 7-Dec-12 23:47pm    
so what is the problem?? why u are not able to do this? any error?
Member-515487 8-Dec-12 22:16pm    
invalid column name

1 solution

Alias column names could not be used as normal column and computation could not be performed on that.

However you can use SQL Variables @receipt_qty etc to save your SUM and then use it for addition purpose.

By the way, your commented code logic is fine and good option to use. Just format for better understanding :)
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900