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 | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/25/2008 :  08:49:06  Show Profile  Reply with Quote
I suspect you've made some changes to my query. can you post full query you used?
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts

Posted - 06/25/2008 :  09:06:40  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

I suspect you've made some changes to my query. can you post full query you used?



Here you go
UPDATE BillingTransactions
SET BillingTransactions.Balance=BillingTransactions.Total + ISNULL(BillingTransactions_1.PrevBalance,0)
FROM BillingTransactions
OUTER APPLY (SELECT SUM(Total) AS PrevBalance
             FROM BillingTransactions
             WHERE UserIndex =BillingTransactions.UserIndex
             AND BillingTransactionIndex<BillingTransactions.BillingTransactionIndex) BillingTransactions_1
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/25/2008 :  09:20:43  Show Profile  Reply with Quote
quote:
Originally posted by tkotey

quote:
Originally posted by visakh16

I suspect you've made some changes to my query. can you post full query you used?



Here you go
UPDATE BillingTransactions
SET BillingTransactions.Balance=BillingTransactions.Total + ISNULL(BillingTransactions_1.PrevBalance,0)
FROM BillingTransactions
OUTER APPLY (SELECT SUM(Total) AS PrevBalance
             FROM BillingTransactions
             WHERE UserIndex =BillingTransactions.UserIndex
             AND BillingTransactionIndex<BillingTransactions.BillingTransactionIndex) BillingTransactions_1



but ur sample data showed field name as Index not BillingTransactionIndex. also try using new alises rather than table name itself.

UPDATE b
SET b.Balance=b.Total + ISNULL(c.PrevBalance,0)
FROM BillingTransactions b
OUTER APPLY (SELECT SUM(Total) AS PrevBalance
             FROM BillingTransactions
             WHERE UserIndex =b.UserIndex
             AND BillingTransactionIndex<b.BillingTransactionIndex) c
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts

Posted - 06/25/2008 :  09:41:22  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by tkotey

quote:
Originally posted by visakh16

I suspect you've made some changes to my query. can you post full query you used?



Here you go
UPDATE BillingTransactions
SET BillingTransactions.Balance=BillingTransactions.Total + ISNULL(BillingTransactions_1.PrevBalance,0)
FROM BillingTransactions
OUTER APPLY (SELECT SUM(Total) AS PrevBalance
             FROM BillingTransactions
             WHERE UserIndex =BillingTransactions.UserIndex
             AND BillingTransactionIndex<BillingTransactions.BillingTransactionIndex) BillingTransactions_1



but ur sample data showed field name as Index not BillingTransactionIndex. also try using new alises rather than table name itself.

UPDATE b
SET b.Balance=b.Total + ISNULL(c.PrevBalance,0)
FROM BillingTransactions b
OUTER APPLY (SELECT SUM(Total) AS PrevBalance
             FROM BillingTransactions
             WHERE UserIndex =b.UserIndex
             AND BillingTransactionIndex<b.BillingTransactionIndex) c





YES!!!!!! IT IS WORKING!!!! DUDE YOU ARE A GENIUS AND A GURU. MUCH RESPECT TO YOU!!

I was leaving out the aliases but when I used the your code without changing it, it worked.

THANK YOU SO MUCH AND MAY GOD BLESS YOU visakh16. You rule dude!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/25/2008 :  12:55:07  Show Profile  Reply with Quote
quote:
Originally posted by tkotey

quote:
Originally posted by visakh16

quote:
Originally posted by tkotey

quote:
Originally posted by visakh16

I suspect you've made some changes to my query. can you post full query you used?



Here you go
UPDATE BillingTransactions
SET BillingTransactions.Balance=BillingTransactions.Total + ISNULL(BillingTransactions_1.PrevBalance,0)
FROM BillingTransactions
OUTER APPLY (SELECT SUM(Total) AS PrevBalance
             FROM BillingTransactions
             WHERE UserIndex =BillingTransactions.UserIndex
             AND BillingTransactionIndex<BillingTransactions.BillingTransactionIndex) BillingTransactions_1



but ur sample data showed field name as Index not BillingTransactionIndex. also try using new alises rather than table name itself.

UPDATE b
SET b.Balance=b.Total + ISNULL(c.PrevBalance,0)
FROM BillingTransactions b
OUTER APPLY (SELECT SUM(Total) AS PrevBalance
             FROM BillingTransactions
             WHERE UserIndex =b.UserIndex
             AND BillingTransactionIndex<b.BillingTransactionIndex) c





YES!!!!!! IT IS WORKING!!!! DUDE YOU ARE A GENIUS AND A GURU. MUCH RESPECT TO YOU!!

I was leaving out the aliases but when I used the your code without changing it, it worked.

THANK YOU SO MUCH AND MAY GOD BLESS YOU visakh16. You rule dude!



You're welcome
but in future please provide clear info of what you want with some sample data and also post your tried query if you're trying something different from what was given.
Go to Top of Page

akki0055
Starting Member

India
3 Posts

Posted - 05/12/2012 :  05:36:06  Show Profile  Reply with Quote

i am using bids 2005 for making reports my database is in sql server 2005
i am quite new in sql.
actually i have two tables first is badge in which i want to select only three rows they are badge_ident, first_name and last_name
another one is productivity bu shift in which more than five rows but i want to select only three rows they are shift_ident, shift_date
and loads.
i am facing a problem when i add rows from table badge and from productivity bu shift i cant filter them according to date and shift also i pass parameters date and shift but its not working.

here query

SELECT BADGE.LAST_NAME, BADGE.FIRST_NAME, BADGE.BADGE_IDENT, PRODUCTIVITY_BY_SHIFT.SHIFT_DATE, PRODUCTIVITY_BY_SHIFT.LOADS
FROM BADGE CROSS JOIN
PRODUCTIVITY_BY_SHIFT
WHERE (PRODUCTIVITY_BY_SHIFT.SHIFT_DATE = @DATE)

please provide me any solution

Thanks

akkitri
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/12/2012 :  20:04:58  Show Profile  Reply with Quote
quote:
Originally posted by akki0055


i am using bids 2005 for making reports my database is in sql server 2005
i am quite new in sql.
actually i have two tables first is badge in which i want to select only three rows they are badge_ident, first_name and last_name
another one is productivity bu shift in which more than five rows but i want to select only three rows they are shift_ident, shift_date
and loads.
i am facing a problem when i add rows from table badge and from productivity bu shift i cant filter them according to date and shift also i pass parameters date and shift but its not working.

here query

SELECT BADGE.LAST_NAME, BADGE.FIRST_NAME, BADGE.BADGE_IDENT, PRODUCTIVITY_BY_SHIFT.SHIFT_DATE, PRODUCTIVITY_BY_SHIFT.LOADS
FROM BADGE CROSS JOIN
PRODUCTIVITY_BY_SHIFT
WHERE (PRODUCTIVITY_BY_SHIFT.SHIFT_DATE = @DATE)

please provide me any solution

Thanks

akkitri


you dont have any relationship between tables? then whats the point of merging them?
Also have you declared @Date parameter
By not working do you mean its throwing an error? in that case, can you post error message?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

akki0055
Starting Member

India
3 Posts

Posted - 05/14/2012 :  01:02:17  Show Profile  Reply with Quote
actually i only want to filter the data according to date.
yes i declared the parameter date
but when i am trying to filter it shows all the data from start date to till date.
its a live system here the data comes according date wise.
no error shows.
if suppose i am using only one table PRODUCTIVITY_BY_SHIFT then i can able to filter it according to date but there is no
badge name exist in PRODUCTIVITY_BY_SHIFT table thats why i am using badge table but when i add badge table and execute sql
it shows all the data comes in the database.



akkitri
Go to Top of Page

akki0055
Starting Member

India
3 Posts

Posted - 05/15/2012 :  00:36:39  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by akki0055


i am using bids 2005 for making reports my database is in sql server 2005
i am quite new in sql.
actually i have two tables first is badge in which i want to select only three rows they are badge_ident, first_name and last_name
another one is productivity bu shift in which more than five rows but i want to select only three rows they are shift_ident, shift_date
and loads.
i am facing a problem when i add rows from table badge and from productivity bu shift i cant filter them according to date and shift also i pass parameters date and shift but its not working.

here query

SELECT BADGE.LAST_NAME, BADGE.FIRST_NAME, BADGE.BADGE_IDENT, PRODUCTIVITY_BY_SHIFT.SHIFT_DATE, PRODUCTIVITY_BY_SHIFT.LOADS
FROM BADGE CROSS JOIN
PRODUCTIVITY_BY_SHIFT
WHERE (PRODUCTIVITY_BY_SHIFT.SHIFT_DATE = @DATE)

please provide me any solution

Thanks

akkitri


you dont have any relationship between tables? then whats the point of merging them?
Also have you declared @Date parameter
By not working do you mean its throwing an error? in that case, can you post error message?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





actually i only want to filter the data according to date.
yes i declared the parameter date
but when i am trying to filter it shows all the data from start date to till date.
its a live system here the data comes according date wise.
no error shows.
if suppose i am using only one table PRODUCTIVITY_BY_SHIFT then i can able to filter it according to date but there is no
badge name exist in PRODUCTIVITY_BY_SHIFT table thats why i am using badge table but when i add badge table and execute sql
it shows all the data comes in the database.



akkitri


akkitri
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/15/2012 :  00:50:31  Show Profile  Reply with Quote
so are you telling you dont want all badges to be shown? then what determines which badges to be returned?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

coolsanketsawant
Starting Member

10 Posts

Posted - 11/06/2013 :  04:38:03  Show Profile  Reply with Quote
hi vishakh16,

I have a query. while working on a report I came across this scenario. what i want is like this....

Company Name ITEM NU. RECEIPT DATE QTY RECEIVD QTY IN STOCK
WDBLB ITM001 2013-06-29 00:00:00.000 1 1
WDBLB ITM002 2013-06-29 00:00:00.000 2 2
WDBLB ITM003 2013-06-29 00:00:00.000 4 4
AVBLB ITM004 2013-07-01 00:00:00.000 10 10
AVBLB ITM001 2013-07-01 00:00:00.000 25 26
AVBLB ITM002 2013-07-01 00:00:00.000 15 17


my problem are two column i.e. QTY RECEIVD & QTY IN STOCK.
I want to sum Quantities for same Item & show it in the QTY IN STOCK column. how can i achieve this??

Thanks in advance.....

Edited by - coolsanketsawant on 11/06/2013 04:41:07
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/06/2013 :  05:11:01  Show Profile  Reply with Quote
quote:
Originally posted by coolsanketsawant

hi vishakh16,

I have a query. while working on a report I came across this scenario. what i want is like this....

Company Name ITEM NU. RECEIPT DATE QTY RECEIVD QTY IN STOCK
WDBLB ITM001 2013-06-29 00:00:00.000 1 1
WDBLB ITM002 2013-06-29 00:00:00.000 2 2
WDBLB ITM003 2013-06-29 00:00:00.000 4 4
AVBLB ITM004 2013-07-01 00:00:00.000 10 10
AVBLB ITM001 2013-07-01 00:00:00.000 25 26
AVBLB ITM002 2013-07-01 00:00:00.000 15 17


my problem are two column i.e. QTY RECEIVD & QTY IN STOCK.
I want to sum Quantities for same Item & show it in the QTY IN STOCK column. how can i achieve this??

Thanks in advance.....


do you mean running or cumulative sum?

see scenario 1 here

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

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

coolsanketsawant
Starting Member

10 Posts

Posted - 11/06/2013 :  08:44:26  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by coolsanketsawant

hi vishakh16,

your suggestion didn't satisfy my requirement. what i want is like this....

Company_Name ITEM_NU. RECEIPT_DATE QTY_RECEIVD QTY_IN_STOCK TOTAL_QTY_AFTER_RECV
WDBLB ITM001 2013-06-29 00:00:00.000 1 1 2
WDBLB ITM002 2013-06-29 00:00:00.000 2 2 4
WDBLB ITM003 2013-06-29 00:00:00.000 4 4 8
AVBLB ITM004 2013-07-01 00:00:00.000 10 10 20
AVBLB ITM001 2013-07-01 00:00:00.000 25 2 7
AVBLB ITM002 2013-07-01 00:00:00.000 15 4 19

QTY_IN_STOCK column contains QTy of ITEm present in the system. When any new QTY for any ITEM is received in "QTY_RECEIVED" column
then I have to add "QTY_RECEIVD" & "QTY_IN_STOCK" & their total i have to reflect in "TOTAL_QTY_AFTER_RECV" column.

Hope i am not confusing you.....

Thanks in advance.....


do you mean running or cumulative sum?

see scenario 1 here

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

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

hi vishakh16,

your suggestion didn't satisfy my requirement. what i want is like this....

Company_Name ITEM_NU. RECEIPT_DATE QTY_RECEIVD QTY_IN_STOCK TOTAL_QTY_AFTER_RECV
WDBLB ITM001 2013-06-29 00:00:00.000 1 1 2
WDBLB ITM002 2013-06-29 00:00:00.000 2 2 4
WDBLB ITM003 2013-06-29 00:00:00.000 4 4 8
AVBLB ITM004 2013-07-01 00:00:00.000 10 10 20
AVBLB ITM001 2013-07-01 00:00:00.000 25 2 7
AVBLB ITM002 2013-07-01 00:00:00.000 15 4 19

QTY_IN_STOCK column contains QTy of ITEm present in the system. When any new QTY for any ITEM is received in "QTY_RECEIVED" column
then I have to add "QTY_RECEIVD" & "QTY_IN_STOCK" & their total i have to reflect in "TOTAL_QTY_AFTER_RECV" column.

Hope i am not confusing you.....


Edited by - coolsanketsawant on 11/06/2013 08:45:26
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/06/2013 :  08:57:31  Show Profile  Reply with Quote
Are QTY_RECEIVD and QTY_IN_STOCK present in same table?

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

coolsanketsawant
Starting Member

10 Posts

Posted - 11/06/2013 :  09:22:30  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

Are QTY_RECEIVD and QTY_IN_STOCK present in same table?

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

Yes Sir.....

Go to Top of Page

coolsanketsawant
Starting Member

10 Posts

Posted - 11/07/2013 :  00:01:23  Show Profile  Reply with Quote
quote:
Originally posted by coolsanketsawant

quote:
Originally posted by visakh16

Are QTY_RECEIVD and QTY_IN_STOCK present in same table?

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

Yes Sir.....





Hi Vishakh,

Any suggestion about this???
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/07/2013 :  03:23:20  Show Profile  Reply with Quote
then its just a matter of doing QTY_RECEIVD + QTY_IN_STOCK to get TOTAL_QTY_AFTER_RECV

------------------------------------------------------------------------------------------------------
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 :  03:27:10  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

then its just a matter of doing QTY_RECEIVD + QTY_IN_STOCK to get TOTAL_QTY_AFTER_RECV

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

But i want the total on next row, not on the same row. & the TOTAL_QTY_AFTER_RECV for one item of any given lot number should appear on the next row for same item & same lot number.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/07/2013 :  04:22:09  Show Profile  Reply with Quote
sorry thats not what your sample data shows. Also i dont see any item with more than one rows for a Company_Name in your example.
Unless you provide proper data i wont be able to give you accurate solution.

------------------------------------------------------------------------------------------------------
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 :  05:12:37  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

sorry thats not what your sample data shows. Also i dont see any item with more than one rows for a Company_Name in your example.
Unless you provide proper data i wont be able to give you accurate solution.

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

Sorry... its my mistake. actually I don't want it for Company_Name. I want it for same Item & Same Lot nu. & I didn't provide you the Lot_Nu Column.

STOFCY_0 ITMREF_0 DATE LOT_NO QUANTITY STOCK_BEFORE STOCK_AFTER
WDBBL 10364111000001101 2013-07-03 00:00:00.000 LOT1306WDBBL001 -1 3 2
WDBBL 10364111000001101 2013-07-03 00:00:00.000 LOT1306WDBBL010 -1 5 4
WDBBL 10364111000001101 2013-07-03 00:00:00.000 LOT1307WDBBL058 -10 27 17
WDBBL 10364111000001101 2013-07-03 00:00:00.000 LOT1307WDBBL058 10 17 27
WDBBL 10364111000001101 2013-07-03 00:00:00.000 LOT1307WDBBL097 5 10 15
WDBBL 10364111000001101 2013-07-03 00:00:00.000 LOT1307WDBBL097 -5 15 10
WDBBL 10364111000001101 2013-07-10 00:00:00.000 LOT1306WDBBL010 1 4 5
WDBBL 10364111000001101 2013-07-10 00:00:00.000 LOT1307NEBBL010 -4 25 21

I have marked items with same LOT_NO with same color. QUANTITY column is QUANTITY received(+ value) or sold(- value). STOCK_BEFORE column is Stock before Item was received or sold. & STOCK_AFTER column is stock after Item received or sold.

What i want is when Item of any particular LOT_NO is received or sold; QUANTITY should be added to or Deducted from STOCK_BEFORE & reflected in STOCK_AFTER,as well as next row of that ITEM with same LOT_NO shouls show the previous STOCK_AFTER Quantity.

Hope I am clear this time......


Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next 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.19 seconds. Powered By: Snitz Forums 2000