Solved

Cumulative sum in SQL Server Query results

Posted on 2008-06-26
28
942 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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
 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query Works in SQL 2008 & 2012 But Not SQL 2016 15 98
EF5 How do I stop pre-compiled views? 8 50
Create a Calendar table 29 43
HIghlights of SSIS? 3 42
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

737 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