Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# How to get this writing a sql statement

Posted on 2013-12-04
Medium Priority
413 Views
Hi
I have a table with value
Beginning_dr Beginning_CR, status , Running total
I have attached the file i which the last column is ther running total
but based on the status flag
if it is B then subtract if it is A then add

can you please tell me how to write the SQL statements

6,000.00      0.00      B      6,000.00
0.00      200.00      A      5,800.00
4,200.00      0.00      B      10,000.00

Thanks,
Rahul.
0
Question by:pardeshirahul
[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
• 5
• 5

LVL 74

Expert Comment

ID: 39696695
what is the ordering criteria?

How do we know 6000 comes before 4200  ?

also, your description and the results don't seem to match.

It looks like your B is an add and A is subtract.

last thing.

Do B records always use the beginning_dr value  and A records always use the beginning_cr value for the math, or should we sum the two columns and use the results or use some other operation?

Assuming...
there is a sortable column like an ID
and you always ADD beginning_dr for B
and always SUBTRACT beginning_cr for A
then try this..

SELECT beginning_dr,
beginning_cr,
status,
SUM(CASE WHEN status = 'B' THEN beginning_dr ELSE -beginning_cr END) OVER (ORDER BY id)
FROM yourtable
ORDER BY id;

if any of those assumptions are not correct, then please elaborate.  Note a running total is ONLY possible if you have a sortable criteria of some kind.
0

Author Comment

ID: 39696702
is just told you the short part of the table

the primary key is

LEdger_id
legal_entity
Period_year
period_name
main_account

order by these 5 columns
0

LVL 74

Expert Comment

ID: 39696711
sorting by more than one column is ok.

SELECT beginning_dr,
beginning_cr,
status,
SUM(CASE WHEN status = 'B' THEN beginning_dr ELSE -beginning_cr END)
OVER (ORDER BY LEdger_id,legal_entity,Period_year,period_name,main_account)
FROM yourtable
ORDER BY LEdger_id,legal_entity,Period_year,period_name,main_account;
0

Author Comment

ID: 39696827
what if the status column is not there

only we assume

if beginning_dr then 'B'
and if Beginning_cr then 'A'
0

Author Comment

ID: 39696830
then how to write the query
0

LVL 74

Expert Comment

ID: 39697323
SELECT beginning_dr,
beginning_cr,
SUM(case when beginning_dr != 0 then beginning_dr ELSE -beginning_cr END)
OVER (ORDER BY LEdger_id,legal_entity,Period_year,period_name,main_account)
FROM yourtable
ORDER BY LEdger_id,legal_entity,Period_year,period_name,main_account;
0

LVL 2

Expert Comment

ID: 39697734
You can use case statement

i have created one example for you

declare @table table(pk bigint,amount1 float,amount2 float,cse varchar(max))

insert into @table values(1,6000.00,5,'B')
insert into @table values(2,5000.00,10,'A')
insert into @table values(3,6500.00,45,'B')
insert into @table values(4,5200,82,'A')

select * from @table
select pk,amount1,amount2,cse,

case when cse='A' then amount1+amount2
when cse='B' then amount1-amount2
else 0
end

from @table
0

LVL 74

Expert Comment

ID: 39698105
that's sql server syntax, this is an oracle question

the oracle syntax provided above should work in sql server 2008 and above (maybe 2005 too, not sure anymore)
0

Author Comment

ID: 39699100
sorry please see the attached data from the excel file i have attached in this case how can i get the running total

the primary key is

LEdger_id
legal_entity
Period_year
period_name
main_account
Copy-of-Running-Total-Example.xls
0

LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 39699139
you shouldn't keep changing the data and the requirements.
when you do that it should be a new question.

sorting by period_name doesn't make sense because JAN-13 will come AFTER FEB-13.
you need to convert that text into an actual date value.
The query below does that.

However, your results are still not reliable.
Lines 3 and 4
and
Lines 5 and 6

are each identical based on your sorting criteria, so there is no way to ensure one row will follow the other reliably, thus your running total might change from mine as you cross those days but the final value should be the same.

SELECT ledger_id,
legal_entity_id,
period_year,
period_name,
main_account,
account_description,
begin_debit,
begin_credit,
account,
sequence_number,
net,
CASE WHEN begin_debit > begin_credit THEN begin_debit - begin_credit ELSE 0 END debit,
CASE WHEN begin_debit > begin_credit THEN 0 ELSE begin_credit - begin_debit END credit,
SUM(net)
OVER(
ORDER BY
ledger_id,
legal_entity_id,
period_year,
TO_DATE(period_name, 'MON-RR'),
main_account
)
FROM yourtable
ORDER BY ledger_id,
legal_entity_id,
period_year,
TO_DATE(period_name, 'MON-RR'),
main_account;

If that doesn't work,  please close this question as it relates to what you ORIGINALLY asked, or continue pursuing the original question and data
to resolve that.

And then open a new question for the new data and new requirements, explaining what this query does or does not do correctly.
0

Author Comment

ID: 39699181
sorry for that i will open a new question if ihave any other issues

Thanks
0

## Featured Post

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theâ€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
###### Suggested Courses
Course of the Month9 days, 21 hours left to enroll