Click here to Skip to main content
Rate this: bad
good
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 at 7-Dec-12 23:47pm
   
so what is the problem?? why u are not able to do this? any error?
Member-515487 at 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
0 OriginalGriff 277
1 Maciej Los 210
2 BillWoodruff 205
3 Jochen Arndt 180
4 DamithSL 165
0 OriginalGriff 5,130
1 DamithSL 4,157
2 Maciej Los 3,670
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,821


Advertise | Privacy | Mobile
Web02 | 2.8.141216.1 | Last Updated 13 Dec 2012
Copyright © CodeProject, 1999-2014
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