Cumulative sum in SQL Server Query results

Adicionally;
The final stock of every month is the initial stock of the following one. My initial stock for this product this with fechacdp=000000 that is to say, 5892.00, for example 4124 = 5892-1768

Adicionally, the operations that are realised are:
200806      5892-1768      4124
200807      4124-234      3890
200808      3890+2131      6021
200809      6021-5874      147
200810      147+1200      1347

If the data sets are not too big, and/or indexed nicely then probably the easiest way is with an in-line query...

Have a look below, and we can go from there...

select p.materialid, p.centroid, p.fechacdp,sum(p.cantidad) as total,
(select sum(cantidad) from PlanOperacion.prdztpp_md05_det2 pp where pp.materialid = p.materialid and pp.centroid = p.centroid and pp.fechacdp < p.fechacdp) as running_total
 
from PlanOperacion.prdztpp_md05_det2 P
where p.materialid = '200049839' and p.centroid = 'PE02'
group by p.materialid, p.centroid, p.fechacdp
order by p.fechacdp

Select allOpen in new window

it works! but not of the correct way, in your result fechacdp 200806 = 5892, when the correct value is: 200806 = 4124, these are the correct values:
200806      5892-1768   =   4124
200807      4124-234     =   3890
200808      3890+2131  =   6021
200809      6021-5874   =    147
200810      147+1200     = 1347

I AM attach file type IMAGE WITH THE CORRECT VALUES IN GREEN COLOR
queryStockFinalMensual03.JPG

Silly me, didn't include the "current" value, just the previous....

try this...

it works fine!!! but it is possible that the first registry corresponding to fechacdp = 000000 does not appear in the set of results
200049839      PE02      000000      5892      5892

well, that is what caught me out in the first posting... Would need to build a table and check to be sure, but try this...


select p.materialid, p.centroid, p.fechacdp,sum(p.cantidad) as total,
(select sum(cantidad) from PlanOperacion.prdztpp_md05_det2 pp where pp.materialid = p.materialid and pp.centroid = p.centroid and pp.fechacdp <= p.fechacdp having count(cantidad) > 1 ) as running_total
 
from PlanOperacion.prdztpp_md05_det2 P
where p.materialid = '200049839' and p.centroid = 'PE02'
group by p.materialid, p.centroid, p.fechacdp
order by p.fechacdp

In order to finish this question, I need that set of results (7 rows), becomes a single row (to see diagram) to be able to insert in another table
queryStockFinalMensual04.JPG

a secrete question - within a question, all for 250 points ! Bargain...

Where does date name come from ? Obviously, fechacdp but, is it just a 6 digit number, int, varchar ? We need to express as a datetime (so we can get month names)

e.g. if an integer, then:

select case when fechacdp > 200600 then datename(mm,convert(datetime,convert(varchar,fechacdp)+'01',112))+convert(varchar,left(fechacdp,4)) else 'Opening Balance' end

--and that piece of code is worth gold :)

ok mark_wills, I go to make a new question, thanks for your valuable aid in the first part

I go to make a new question, I hope that also you can help me

Link it to this one, or post the new link back here as well. Wasn't really complaining about points - just being a bit cheeky, more about the "hidden" second question... Still need you to answer, test, the example query above about fechacdp...

it's work fine!!!, the result is:
Opening Balance
August2008
October2008
June2008
August2008
July2008
June2008
August2008
September2008
October2008
September2008
October2008
November2008

OK, so all we need now is to get it horizontal (I am sure there is a joke in that comment somewhere)...

Ever used the PIVOT function in SQl Server 2005 ? Are the periods going to be fairly static ?

Friend is not dear joke, is very in serious. What difficult east subject is that a material can have up to 5 rows (1 per month) and another one can have up to 12, it is very variable

Not saying the work is a joke, saying that my comment to "get it horizontal" is the basis of some jokes...

Now, you can see below how vastly different your answer has now become - hence the comment about different question.


SELECT materialid, centroid,[OpeningBalance],[January2008],[February2008],[March2008],[April2008],[May2008],[June2008],[July2008],[August2008],[September2008],[October2008],[November2008],[December2008]
FROM
(SELECT materialid, centroid, cantidad, case when fechacdp > 200600 then datename(mm,convert(datetime,convert(varchar,fechacdp)+'01',112))+convert(varchar,left(fechacdp,4)) else 'OpeningBalance' end as Periods From PlanOperacion.prdztpp_md05_det2 where materialid = '200049839' and centroid = 'PE02'
PIVOT
(
SUM (cantidad)
FOR Periods IN ([OpeningBalance],[January2008],[February2008],[March2008],[April2008],[May2008],[June2008],[July2008],[August2008],[September2008],[October2008],[November2008],[December2008])
) AS pvt
ORDER BY materialid, centroid;

ok my friend. I have the error next: Incorrect syntax near the keyword 'PIVOT'.

<P>OK, missing a bracket and alias at end of first part of subquery...</P><P> </P><P> </P>

SELECT materialid, centroid,[OpeningBalance],[January2008],[February2008],[March2008],[April2008],[May2008],[June2008],[July2008],[August2008],[September2008],[October2008],[November2008],[December2008]
FROM 
(SELECT materialid, centroid, cantidad,case when fechacdp > 200600 then datename(mm,convert(datetime,convert(varchar,fechacdp)+'01',112))+convert(varchar,left(fechacdp,4)) else 'OpeningBalance' end as Periods From PlanOperacion.prdztpp_md05_det2 where materialid = '200049839' and centroid = 'PE02') P
PIVOT
(
SUM(cantidad)
FOR Periods IN ([OpeningBalance],[January2008],[February2008],[March2008],[April2008],[May2008],[June2008],[July2008],[August2008],[September2008],[October2008],[November2008],[December2008])
) AS pvt
ORDER BY materialid, centroid;

Select allOpen in new window

Hmmmm, tried a new spell check feature, and got the <P>  tags... Code looks OK though

the query it's work fine, but the data is not correct (view file)
queryStockFinalMensual06.JPG

No, it doesn't, forgot about the original query with the running total...

SELECT materialid, centroid,[OpeningBalance],[January2008],[February2008],[March2008],[April2008],[May2008],[June2008],[July2008],[August2008],[September2008],[October2008],[November2008],[December2008]
FROM 
(SELECT materialid, centroid,case when fechacdp > 200600 then datename(mm,convert(datetime,convert(varchar,fechacdp)+'01',112))+convert(varchar,left(fechacdp,4)) 
else 'OpeningBalance' end as Periods,
(select sum(cantidad) from PlanOperacion.prdztpp_md05_det2 pp where pp.materialid = p.materialid and pp.centroid = p.centroid and pp.fechacdp <= p.fechacdp having count(cantidad) > 1 ) as running_total
 From PlanOperacion.prdztpp_md05_det2 p where materialid = '200049839' and centroid = 'PE02' group by materialid,centroid,fechacdp) Data
PIVOT
(
SUM(running_total)
FOR Periods IN ([OpeningBalance],[January2008],[February2008],[March2008],[April2008],[May2008],[June2008],[July2008],[August2008],[September2008],[October2008],[November2008],[December2008])
) AS pvt
ORDER BY materialid, centroid;

Select allOpen in new window

it's work fine!!!. That but it is possible to be done with you query so that inserts those data in a new table

Is this another new requirement ?

either create a table, and do :

INSERT new_table (materialid, centroid,[OpeningBalance],[January2008],[February2008],[March2008],[April2008],[May2008],etc etc)

SELECT materialid, centroid,[OpeningBalance],[January2008],[February2008],[March2008],[April2008],[May2008],[June2008],[July2008],[August2008],[September2008],[October2008],[November2008],[December2008]
FROM

or, create a unique new table (ie cannot already exist) using the "select into" syntax...


SELECT materialid, centroid,[OpeningBalance],[January2008],[February2008],[March2008],[April2008],[May2008],[June2008],[July2008],[August2008],[September2008],[October2008],[November2008],[December2008]
INTO mytable
FROM


and my friend, that is all I can keep answering on this question - it was closed off and there has been three new requirements : 1) Date format, 2) Pivot, 3) output

I appreciate your valuable assistance, many thanks

Hi my friend, that I can do so that the months leave in Spanish?.
I posted a new question with Title: null columns in the result query, you can solve possibly it. Greetings

NULLS are a genuine possibility of a PIVOT function...

that will probably be dependant on your language setting in the database....

language ID 5 should give you:

Enero,Febrero,Marzo,Abril,Mayo,Junio,Julio,Agosto,Septiembre,Octubre,Noviembre,Diciembre

check by doing : select @@langid

then have a look at  select * from sys.syslanguages

If needed, thn could build a translation table, then you would need to translate the month names...