How to compare & add previous rows data into next

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to compare & add previous rows data into next

Author

Topic

sanjay_jadam
Yak Posting Veteran

53 Posts

Posted - 2005-05-25 : 02:32:04

Hi to all,

Is anyone help me for solve my problem

How can i compare and add previous row values to the next row values.
Eg:-
I have a View which generate this result set
Material Quantity month year
A 100 4 2005
A 40 5 2005
A 100 6 2005
B 100 4 2005
B 120 5 2005

so i need to calculate the total quantity of material A and B ....

required result is
Material Quantity month year
A 100 4 2005
A 140 5 2005
A 240 6 2005
B 100 4 2005
B 220 5 2005

Means that i want to add quantity for a previous month to the next month. But Material name must be same.
How can i get this type of result
Please help me ASAP.

Thanks in advance

Regards
Sanjay Jadam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-25 : 02:56:55

Select Material,(select sum(Quantity) from yourtable where Material=T.Material and Month<=T.Month) as Quantity,Month,year from yourtable T

Madhivanan

Failing to plan is Planning to fail

sanjay_jadam
Yak Posting Veteran

53 Posts

Posted - 2005-05-25 : 05:32:24

Hi,
Thanks

But i got an error

Internal SQL Server error.

Actully i have a table name stock and Material table
Stock table having these fields
ID_Material----Transactiondate----QuantityReceived----QuantityIssued and so on
I get this result

Material Quantity month year
A 100 4 2005
A 40 5 2005
A 100 6 2005
B 100 4 2005
B 120 5 2005

using this query

SELECT M.Itemname,SUM(QuantityReceived-quantityIssued) as 'Balance'
,MONTH(transactionDate) AS 'StkMonth', YEAR(transactionDate) as 'StkYear'
FROM stocks S INNER JOIN Material M ON S.ID_Material=M.ID_Material
GROUP BY M.ITEMNAME,MONTH(transactiondate),YEAR(transactiondate)

By modifying this query i need these result

Material Quantity month year
A----------100---- 4--- 2005
A----------140 ----5--- 2005
A ---------240-----6--- 2005
B--------- 100-----4----2005
B--------- 220---- 5----2005

so please help me

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-25 : 05:54:48

Move the result data to temporary table and write the query based on that table

Madhivanan

Failing to plan is Planning to fail

sanjay_jadam
Yak Posting Veteran

53 Posts

Posted - 2005-05-25 : 08:45:46

thanks
I have done it.
Thanks once again

sanjay_jadam
Yak Posting Veteran

53 Posts

Posted - 2005-05-25 : 08:57:47

Can you tell me which book i preferred for a developer level knowledge in SQL Server.
I need a good book on T-SQL programming in which most of the method included and good information for the stroed procedure and other SQL server topic

Thanks
Sanjay Jadam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources