473,320 Members | 1,949 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Problem with calculate difference between rows

I have the following problem:

In a view I obtain three rows and I have to group them together making
some particular operation to add dinamic column.

The query without the group by give me these results :
CMP CNT CODE GROUP ENDQTY ROWPARTITIONED
001 PRD 100 AAA 20 1
001 PRD 100 BBB 50 2
001 PRD 100 CCC 100 3

-------------------------------------------------
CMP CNT CODE GROUP ENDQTY ROWPARTITIONED
001 PRD 100 AAA 20 1
001 PRD 110 BBB 50 1
001 PRD 120 CCC 100 1

In the first case what I want is this one

CMP CNT CLM1 CLM2 CLM3
001 PRD 20 30 50

in the second case what I want is

CMP CNT CLM1 CLM2 CLM3
001 PRD 20 50 100
The columns(CLMx) must be calculated in the following way :

If the ROWPARTITIONED column is equal to 1 the the column is equal to
ENDQTY

If the ROWPARTITIONED column is greater then 1 the the column is equal
to ENDQTY -( ENDQTY of the previous group)
CLM2 = 30 because ENDQTY wit hrow partiioned = 2 --50 and ENDQTY of
the previous row = 20... so 50-20 = 30

What I have done is a the following query :

SELECT CMP, CNT, SUM(CASE WHEN GROUP = 'AAA' THEN ENDQTY ELSE 0 END)
CLM1,
SUM(CASE WHEN GROUP = 'BBB' THEN ENDQTY ELSE 0 END) - SUM(CASE WHEN
GROUP = 'AAA' THEN ENDQTY ELSE 0 END) CLM2,
SUM(CASE WHEN GROUP = 'CCC' THEN ENDQTY ELSE 0 END) - SUM(CASE WHEN
GROUP = 'BBB' THEN ENDQTY ELSE 0 END) CLM3

GROUP BY CMP, CNT

This query gives me the possibility to obtain a single row but I'm not
able to write something like this

CASE WHEN ROWPARTITIONED 1 THEN SUM(CASE WHEN GROUP = 'BBB' THEN
ENDQTY ELSE 0 END) - SUM(CASE WHEN GROUP = 'AAA' THEN ENDQTY ELSE 0
END) ELSE SUM(CASE WHEN GROUP = 'BBB' THEN ENDQTY ELSE 0 END) END

because I obtain an sql error (SQLCODE -119) due to the fact that the
field ROWPARTITIONED is in select and not in group by.
I don't want to use (if it is possible) an "inner/sub" select in each
CASE statement, becuase this is only a little part of the real very
complex statement.

Can someone help me pls?

Thank You very much (in advance) to all for the support!

bye!

Oct 30 '08 #1
2 2701
Thank you very much to all for the reply.
Tonkuma: no, I haven't alwyas 3 rows

but fortunatelly both Tonkuma and Lennart give me same very good
suggestion. and I solved (with your help) my problem.

Thanks again!
Roberto
Oct 31 '08 #2
but fortunatelly both Tonkuma and Lennart give me same very good
suggestion. and I solved (with your help) my problem.

Thanks again!
Roberto
I feel very glad, if I could give you some help to solve your problem.
Oct 31 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Julia Briggs | last post by:
Hello, I am trying to find a workaround for the Unix date issue to calculate ages based on a persons birthdate and the current date. Here is the code I am working with now, <?php $dob = "10...
4
by: Bill Dika | last post by:
Hi I am trying to calculate a running total of a calculated textbox (tbAtStandard) in GroupFooter1 for placement in a textbox (tbTotalAtStandard) on my report in Groupfooter0. The problem...
5
by: Mr | last post by:
Hi We have a application with Framework 1.1 and Vs .NET 2003. the application has a service with pooling; like a File system Watcher, but works. the system works fine in the development...
4
by: dmisen | last post by:
I have data on air pollution coming from sources across the country, and control options for reducing pollution from each source. The example below shows data for: - 2 sources (plant_ID "001" and...
4
by: Rich_C | last post by:
I'm sure this is very simple, but I have very little experience with javascript -- and what I do know isn't helping me here. I have a simple form where users can enter a quantity (qty) and cost...
5
by: john | last post by:
I have a transaction table which partly looks like this: InOut Amount In 10 In 10 Out 14 I would like to make a query that calculates the difference between incoming and...
2
by: Martin | last post by:
Hi all, This is the situation... DatagridView Control with datasource set to datatable. I want the user to be able to delete x number of rows by selecting a row with mouse then clicking a...
9
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just...
5
by: adarshyam | last post by:
Hi friends, I have an interesting problem in vb.net. And I am struggling to get a solution for this..m trying for the past 3days.. It’s to calculate moving average for the inputs given by the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.