Click here to Skip to main content
12,243,038 members (40,446 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL SQL-Server
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 7-Dec-12 18:32pm
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

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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 Smile | :)
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160426.1 | Last Updated 13 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100