Solved

Cumulative sum in SQL Server Query results

Posted on 2008-06-26
28
936 Views
Last Modified: 2011-10-19
I have this query
1.
select materialid, centroid, fechacdp,sum(cantidad)
from PlanOperacion.prdztpp_md05_det2
where materialid = '200049839' and centroid = 'PE02'
group by materialid, centroid, fechacdp
order by fechacdp
2. note file queryStockFinalMensual01
3. I need to conduct one more an operation on previous the previous consultation so that I have left something thus:
4. note file queryStockFinalMensual02

queryStockFinalMensual01.JPG
queryStockFinalMensual02.JPG
0
Comment
Question by:enrique_aeo
  • 15
  • 13
28 Comments
 

Author Comment

by:enrique_aeo
ID: 21875562
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
0
 

Author Comment

by:enrique_aeo
ID: 21879295
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
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21881128


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

Open in new window

0
 

Author Comment

by:enrique_aeo
ID: 21883699
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
0
 

Author Comment

by:enrique_aeo
ID: 21883876
I AM attach file type IMAGE WITH THE CORRECT VALUES IN GREEN COLOR
queryStockFinalMensual03.JPG
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21883946
Silly me, didn't include the "current" value, just the previous....

try this...

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 250 total points
ID: 21883952
This time with some code...
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

Open in new window

0
 

Author Comment

by:enrique_aeo
ID: 21884120
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
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21884271
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

0
 

Author Comment

by:enrique_aeo
ID: 21884332
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
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21884589
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 :)
0
 

Author Comment

by:enrique_aeo
ID: 21884742
ok mark_wills, I go to make a new question, thanks for your valuable aid in the first part
0
 

Author Closing Comment

by:enrique_aeo
ID: 31471006
I go to make a new question, I hope that also you can help me
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21884799
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...
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:enrique_aeo
ID: 21886445
it's work fine!!!, the result is:
Opening Balance
August2008
October2008
June2008
August2008
July2008
June2008
August2008
September2008
October2008
September2008
October2008
November2008
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21886511
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 ?

0
 

Author Comment

by:enrique_aeo
ID: 21886571
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
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21886827
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;

0
 

Author Comment

by:enrique_aeo
ID: 21886885
ok my friend. I have the error next: Incorrect syntax near the keyword 'PIVOT'.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21887025
<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;

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21887043
Hmmmm, tried a new spell check feature, and got the <P>  tags... Code looks OK though
0
 

Author Comment

by:enrique_aeo
ID: 21887111
the query it's work fine, but the data is not correct (view file)
queryStockFinalMensual06.JPG
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21887183
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;

Open in new window

0
 

Author Comment

by:enrique_aeo
ID: 21887456
it's work fine!!!. That but it is possible to be done with you query so that inserts those data in a new table
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21890109
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
0
 

Author Comment

by:enrique_aeo
ID: 21896002
I appreciate your valuable assistance, many thanks
0
 

Author Comment

by:enrique_aeo
ID: 21909425
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
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21909700
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...
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now