?
Solved

Cumulative sum in SQL Server Query results

Posted on 2008-06-26
28
Medium Priority
?
950 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 1000 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
 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

578 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