SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SUM two fileds from different rows from the same t
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 3

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/07/2013 :  07:18:32  Show Profile  Reply with Quote
This is exactly what link i posted you does. DId you ever had a look at it?


SELECT t.*,
Total AS StockBEFORE,
t.QUANTITY + Total AS STockAFter
FROM Table t
OUTER APPLY (SELECT SUM(QUANTITY + STOCK_BEFORE) AS Total
             FROM table
             WHERE STOFCY_0 = t.STOFCY_0 
              AND ITMREF_0 = t.ITMREF_0 
              AND [DATE] = t.[DATE]
              AND LOT_NO = t.LOT_NO
              AND PK < t.PK
           )t1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

coolsanketsawant
Starting Member

10 Posts

Posted - 11/07/2013 :  07:35:36  Show Profile  Reply with Quote
Hi vishakh16,

in the query givn by you PK means Primary Key no?????
AND PK < t.PK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/07/2013 :  07:47:22  Show Profile  Reply with Quote
quote:
Originally posted by coolsanketsawant

Hi vishakh16,

in the query givn by you PK means Primary Key no?????
AND PK < t.PK


Primary Key or any column which uniquely identifies a record.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

coolsanketsawant
Starting Member

10 Posts

Posted - 11/07/2013 :  08:13:49  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by coolsanketsawant

Hi vishakh16,

in the query givn by you PK means Primary Key no?????
AND PK < t.PK


Primary Key or any column which uniquely identifies a record.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Hi vishakh16,

Sir, This is what I got when i tried you query....

STOFCY_0 ITMREF_0 IPTDAT_0 LOT QUANTITY QUANTITY_STK StockBEFORE STockAFter
WDBBL 10364111000001100 03/07/2013 00:00 LOT1306WDBBL001 -1 -1 -2 -3
WDBBL 10364111000001100 03/07/2013 00:00 LOT1306WDBBL010 -1 -1 -2 -3
WDBBL 10364111000001100 03/07/2013 00:00 LOT1307WDBBL058 -10 -10 0 -10
WDBBL 10364111000001100 03/07/2013 00:00 LOT1307WDBBL058 10 10 0 10
WDBBL 10364111000001100 03/07/2013 00:00 LOT1307WDBBL097 5 5 0 5
WDBBL 10364111000001100 03/07/2013 00:00 LOT1307WDBBL097 -5 -5 0 -5
WDBBL 10364111000001100 10/07/2013 00:00 LOT1306WDBBL010 1 1 2 3
WDBBL 10364111000001100 10/07/2013 00:00 LOT1307NEBBL010 -4 -4 0 -4

But according to my requirement STOCK_BEFORE Qty for LOT "LOT1306WDBBL010" of 7th row should be same as STOCK_AFTER Quantity for the same LOT No of 2nd row.




Go to Top of Page

coolsanketsawant
Starting Member

10 Posts

Posted - 11/08/2013 :  02:32:50  Show Profile  Reply with Quote
Any suggestion about this sir????
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000